title: LINQ with joins and group generates enormous query and is slow
body: <p>My Entity structure is as follows:</p>
  <pre class="lang-cs prettyprint-override"><code>public class DisbursementItem
  {
      public int DisbursementNumber;
      public int IDDisbursementItem;
      public int IDReceiptItem;
      public decimal Amount;
      public decimal MeasureUnit;
      public decimal PricePerMU;
      public decimal PriceTotal;
      public Disbursement Disbursement_IDDisbursement;
      public int IDDisbursementNumber;
  }
  
  public class Disbursement
  {
      public int DisbursementNumber;
      DateTime date;
      DisbursementType DType;
      string Note;
      string Subscriber;
      Subscriber SubscriberModel;
      string ItemType;
      int ProcessNumber;
  }
  
  public class Subscriber 
  {
      public string Name
      public string Address;
      public string City;
  }
  
  public class DisbursementDescription
  {
      public int IDDisbursementItem;
      public string Description;
  }
  
  public class Receipt
  {
      public int IDReceiptItem;
      public int ItemNumber;
  }
  
  public class StorageCard 
  {
      public int ItemNumber;
      public string StorageCardGroup;
      public string StorageCardName;
  }
  </code></pre>
  <p>And my EF6 LINQ query is:</p>
  <pre class="lang-cs prettyprint-override"><code>DateTime from;
  DateTime to;
  var result = context.DisbursementItem
      .Where(x =&gt; x.Disbursement_IDDisbursement.Date &lt;= to &amp;&amp; x.Disbursement_IDDisbursement.Date &gt;= from)
      .Join(context.DisbursementDescription, di =&gt; di.IDDisbursementItem, dd =&gt; dd.IDDisbursementItem, (di, dd) =&gt; new {di = di, desc = dd.Description})
      .Join(context.Receipt, x =&gt; x.di.IDReceiptItem, r =&gt; r.IDReceiptItem, (x, r) =&gt; new { di = x.di, desc = x.desc, r = r })
      .Join(context.StorageCard, x =&gt; x.r.ItemNumber, sc =&gt; sc.ItemNumber, (x, sc) =&gt; new { di = x.di, desc = x.desc, r = x.r, sc = sc})
      .GroupBy(g =&gt; new {g.di.DisbursementNumber, g.sc.ItemNumber, g.di.MeasureUnit})
      .Select(x =&gt; new
      {
          Date = x.FirstOrDefault().di.Disbursement_IDDisbursement.Date,
          DisbursementNumber = x.Key.DisbursementNumber,
          DType = x.FirstOrDefault().di.Disbursement_IDDisbursement.DType,
          Note = x.FirstOrDefault().di.Disbursement_IDDisbursement.Note,
          Subscriber = x.FirstOrDefault().di.Disbursement_IDDisbursement.Subscriber,
          SubscriberName = x.FirstOrDefault().di.Disbursement_IDDisbursement.SubscriberModel.Name,
          SubscriberAddress = x.FirstOrDefault().di.Disbursement_IDDisbursement.SubscriberModel.Address,
          SubscriberCity = x.FirstOrDefault().di.Disbursement_IDDisbursement.SubscriberModel.City,
          ItemNumber = x.FirstOrDefault().sc.ItemNumber,
          StorageCardGroup = x.FirstOrDefault().sc.StorageCardGroup,
          StorageCardName = x.FirstOrDefault().sc.StorageCardName,
          Amount = x.Sum(y =&gt; y.di.Amount),
          PricePerMU = x.FirstOrDefault().di.PricePerMU,
          PriceTotal = x.Sum(y =&gt; y.di.PriceTotal),
          MeasureUnit = x.Key.MeasureUnit
          Desc = x.FirstOrDefault().desc,
      })
  
  </code></pre>
  <pre class="lang-sql prettyprint-override"><code>SELECT
      di.Date,
      di.DisbursementNumber,
      d.DType,
      d.Note,
      d.Subscriber,
      subs.Name,
      subs.Address,
      subs.City,
      sc.ItemNumber,
      sc.StorageCardGroup,
      sc.StorageCardName,
      Sum(di.Amount) as Amount,
      di.PricePerMU,
      Sum(di.PriceTotal) as PriceTotal,
      di.MeasureUnit,
      dd.Description
  
  FROM
      DisbursementItem as di
  
  INNER JOIN Disbursement as d 
  ON di.IDDisbursementNumber = d.DisbursementNumber
  
  INNER JOIN Receipt as r 
  ON di.IDReceiptItem = r.IDReceiptItem
  
  INNER JOIN StorageCard as sc
  ON r.ItemNumber = sc.ItemNumber
  
  INNER JOIN DisbursementDescription dd 
  ON di.IDDisbuzrsementItem = dd.IDDisbursementItem
  
  WHERE 
      di.Date &lt;= ... and di.Date &gt;= ...
  
  GROUP BY
  di.DisbursementNumber, sc.ItemNumber, di.MeasureUnit
  </code></pre>
  <p>That is the query in SQL that I want to achieve in EF</p>
  <p>This query can take over a minute for a few hundred rows. How can I optimize it? I suspect the multiple joins is a problem and maybe also the <code>Sum</code> of some fields.</p>
  <p>Also the database schema cannot be modified.</p>
  <p>The query it generate is enormous. It's like a SELECT in SELECT in SELECT for like 40 times.</p>