Linq group by with multiple where, sum clauses in select causes a timeout I have  linq query that extracts a summary of my sales quantities item group wise. This query causes an sql timeout. From the what i have found out, timeout indicates that my query is poorly designed and needs to be optimized.

Below is the linq query:

 (from inv in Dbcontext.tblSalesInvoices
    join det in Dbcontext.tblSalesInvoiceDetails on inv.SalesInvoiceId equals det.SalesInvoiceId
    join item in Dbcontext.tblItems on det.ItemId equals item.ItemId
    join itemGroup in Dbcontext.tblItemGroups on item.ItemGroupId equals itemGroup.ItemGroupId
    join loc in Dbcontext.tblLocations on inv.LocationId equals loc.LocationId
    where inv.InvoiceDate &gt;= FeedSearchCriteria.FromDate &amp;&amp; inv.InvoiceDate &lt;= FeedSearchCriteria.ToDate
    &amp;&amp; inv.InvoiceStatusId != Constants.REVERSED_INVOICE
    group new { det.Quantity, itemGroup.ItemGroupCode, item.Weight, item.ItemCode, det.LineTotal } by loc.LocationName into g
    select new LocationCount()
    {
    LocationName = g.Key,
    FeedBagCount = (int?)g.Where(x =&gt; x.ItemGroupCode == "FEED").Sum(x =&gt; x.Quantity),
    SupremeKg = (decimal?)g.Where(x =&gt; x.ItemGroupCode == "ABATTOIR").Sum(x =&gt; x.Weight * x.Quantity),
    SupremeValue = (decimal?)g.Where(x =&gt; x.ItemGroupCode == "ABATTOIR").Sum(x =&gt; x.LineTotal),
    DocCount = (int?)g.Where(x =&gt; x.ItemCode == "DOC").Sum(x =&gt; x.Quantity)
    }).OrderBy(x=&gt;x.LocationName).ToListAsync();




I think my issue arises because of the where/sum clause in select. How can i re-write below query in more optimized manner.

Edit

Time out occurs when a date range is more than or equal to 1 month of transactions.
You could try to set the joins on the already filtered table:

(from inv in Dbcontext.tblSalesInvoices.Where(inv =&gt; inv.InvoiceDate &gt;= FeedSearchCriteria.FromDate &amp;&amp; inv.InvoiceDate &lt;= FeedSearchCriteria.ToDate
    &amp;&amp; inv.InvoiceStatusId != Constants.REVERSED_INVOICE)
    join det in Dbcontext.tblSalesInvoiceDetails on inv.SalesInvoiceId equals det.SalesInvoiceId
    join item in Dbcontext.tblItems on det.ItemId equals item.ItemId
    join itemGroup in Dbcontext.tblItemGroups on item.ItemGroupId equals itemGroup.ItemGroupId
    join loc in Dbcontext.tblLocations on inv.LocationId equals loc.LocationId
    group new { det.Quantity, itemGroup.ItemGroupCode, item.Weight, item.ItemCode, det.LineTotal } by loc.LocationName into g
    select new LocationCount()
    {
    LocationName = g.Key,
    FeedBagCount = (int?)g.Where(x =&gt; x.ItemGroupCode == "FEED").Sum(x =&gt; x.Quantity),
    SupremeKg = (decimal?)g.Where(x =&gt; x.ItemGroupCode == "ABATTOIR").Sum(x =&gt; x.Weight * x.Quantity),
    SupremeValue = (decimal?)g.Where(x =&gt; x.ItemGroupCode == "ABATTOIR").Sum(x =&gt; x.LineTotal),
    DocCount = (int?)g.Where(x =&gt; x.ItemCode == "DOC").Sum(x =&gt; x.Quantity)
    }).OrderBy(x=&gt;x.LocationName).ToListAsync();