Linq Query Throws Timeout in code but Works fine on LinqPad

Question

When I run the Linq query on LinqPad it takes only 4-5 seconds to return 5000 rows but when I run that same query in my code using Entity Framework it throws timeout.

What could be the possible issues?

Thanks in advance.

Below is the query:

var resultSale =           
    from product in Products
    join productInfo in ProductInfoSummaries on product.ID equals productInfo.ProductID
    join productDetail in ProductDetails on new { Id = product.ID, storeId = product.CreatedInStore } equals new { Id = productDetail.ProductID, storeId = productDetail.StoreID }
    
    join productInventoryOtherStore in InventoryOtherStores on product.ID equals productInventoryOtherStore.ProductID
    into productInventories
    from productInventoryOtherStore in productInventories.DefaultIfEmpty()
    
    join saleLine in SaleLines on productDetail.ID equals saleLine.ArtikkelNr
    join sales in Sales on saleLine.OrderID equals sales.ID
    
    where saleLine.ArtikkelNr != null
    && saleLine.DatoTid >= new DateTime(2018, 01, 01)
    && saleLine.DatoTid <= new DateTime(2019,11,21)
    && sales.StoreID == 14
    && (sales.OrderType == 1 || sales.OrderType == 2 || sales.OrderType == 4 || sales.OrderType == 6)
    && productDetail.SupplierProductNo != null
    && productDetail.Deleted == null
    && (productInfo.Inactive == null || productInfo.Inactive == false)
    && (product.CreatedInStore == 14 || product.CreatedInStore == 0 || product.CreatedInStore == null)
    
    group new { saleLine.AntallEnheter, sales.OrderType } by new { product.ID, productInventoryOtherStore.Amount } into g

    select new ProductSaleSummaryVM
    {
        ID = g.Key.ID,
        Inventory = (double)g.Key.Amount,
        TotalSold = g.Sum(x => x.OrderType !=4 ? x.AntallEnheter : 0) ?? 0,
        TotalWastage = g.Sum(x => x.OrderType ==4 ?  x.AntallEnheter : 0) ?? 0,
        TotalOrderedQty = 0
    };
    
var resultSupplierOrder = 
    from supplierOrderLine in SupplierOrderLines
    join supplierOrder in SupplierOrders on supplierOrderLine.SupplierOrderID equals supplierOrder.ID
    where supplierOrderLine.Deleted == null
    && supplierOrder.Status != 1
    && supplierOrder.StoreID == 14
    group supplierOrderLine by supplierOrderLine.ProductID into g
    select new ProductOrderDetailsVM
    {
        ID = g.Key,
        TotalOrderedQty = (double)g.Sum(x => x.ConsumerQuantity - x.QuantiyReceived)
    };
    
var r =   
    (from resSale in resultSale
    join resSupplierOrder in resultSupplierOrder on resSale.ID equals resSupplierOrder.ID
    into resSupplierOrders
    from resSupplierOrder in resSupplierOrders.DefaultIfEmpty()
    orderby resSale.ID
    select new ProductSaleSummaryVM
    {
        ID = resSale.ID,
        Inventory = resSale.Inventory,
        TotalSold = resSale.TotalSold,
        TotalWastage = resSale.TotalWastage,
        TotalOrderedQty = resSupplierOrder.TotalOrderedQty ?? 0
    })
    .Where(x => x.Inventory +  x.TotalOrderedQty < x.TotalSold);
            
r.Dump();

It could be connection issues. Check your connection string if its correct, and check if your database server is up and running. — rdr20, Feb 23 '21 at 09:32
@rdr20 Thanks. My database connection is working fine already. Only throws this timeout error when I try to get large records — Harshit, Feb 23 '21 at 09:45
Have you tried adjusting the command timeout to a longer one? — rdr20, Feb 23 '21 at 09:49

score 0 · Answer 1 · answered Feb 23 '21 at 09:22

0

Try using entity framework within linqpad see if it gives you any clues. see this link on how to use entity framework in linqpad

https://www.linqpad.net/EntityFramework.aspx

answered Feb 23 '21 at 09:22

mjb

126
3

Thanks, @mjb but I tried but not helpful in my case because my database project has some configurations which I can't supply in LinqPad. – Harshit Feb 23 '21 at 09:44
1

In LINQPad 6, you can choose between LINQ to SQL and Entity Framework without needing to create a data context. After clicking 'Add connection', choose the second option in the list. – Joe Albahari Feb 24 '21 at 04:12
JoeAlbahari, mjb Thanks. These suggestions helped me while resolving the issue – Harshit Feb 24 '21 at 04:31

score 0 · Answer 2 · answered Aug 24 '22 at 13:30

0

This behaviour could be related to parameter sniffing - check this article for details

answered Aug 24 '22 at 13:30

Bogdan Banut

101
1
6

Linq Query Throws Timeout in code but Works fine on LinqPad

2 Answers2