LINQ Generated SQL code in .Net 4.0 slower than LinqPad Generated SQL code

I've got a pretty simple Linq query that when run in an ASP.Net 4.0 webpage it generates SQL Queries that are huge and take a ton of time to complete (30+ seconds). When I put the code in LinqPad and run there the SQL queries are small, neat and fast (< 1 second).

I've tried rewriting this query so many ways, and have even resorted to trying toEnumerable, toList, and toArray the thing to make Linq generate in 1 query instead of 6 separate queries. All that did was timeout my SQL query.

Anyway, just don't understand why it's being so difficult and what I can do to code in a way that ASP.Net 4.0 generates the kind of queries that LinqPad does. LinqPad is AWESOME and helps out a ton in so many ways, but when I optimize my queries using LinqPad, then just have to go around again and optimize in ASP.Net 4.0 because is doesn't act the same...just isn't efficient. I would REALLY appreciate any help anyone can give me on the matter...I still consider myself a Linq newbie.

Here is code in my ASP.Net 4.0 Web Form:
Takes over 30 seconds to run in 6 queries using SQL Profiler
clearEntities clear = new clearEntities(); using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, new System.Transactions.TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted })) { int ClubID = Convert.ToInt32(ddClub.SelectedValue); int CategoryID = Convert.ToInt32(ddCategory.SelectedValue); string Filter = tbFilter.Text; IQueryable data = clear.InventoryOrders.Where(i => (i.ClubID == ClubID || ClubID == -1) && (CategoryID == -1 || CategoryID == i.SKU.SKUParent.SKUCategoryID) && (Filter == null || i.SKU.SKUParent.Parent.Contains(Filter) || i.SKU.SKUParent.Description.Contains(Filter) || i.SKU.UPC == Filter) && (i.InventoryOrderStatusID == 1 || i.InventoryOrderStatusID == 2)); lbTotalOrderedOrders.Text = data.Where(i => i.InventoryOrderStatusID == 1).Count(); int TotalOrderedItems = data.Where(i => i.InventoryOrderStatusID == 1).Sum(i => i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0; decimal TotalOrderedCost = data.Where(i => i.InventoryOrderStatusID == 1).Sum(i => i.SKU.UnitCost * i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0.0m; int TotalShippedOrders = data.Where(i => i.InventoryOrderStatusID == 2).Count(); int TotalShippedItems = data.Where(i => i.InventoryOrderStatusID == 2).Sum(i => i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0; decimal TotalShippedCost = data.Where(i => i.InventoryOrderStatusID == 2).Sum(i => i.SKU.UnitCost * i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0.0m; }

Here is code that I put into LinqPad:
Takes < 1 second to run in 6 queries using SQL Profiler
int ClubID = -1; int CategoryID = -1; string Filter = ""; IQueryable data = InventoryOrders.Where(i => (i.ClubID == ClubID || ClubID == -1) && (CategoryID == -1 || CategoryID == i.SKU.SKUParent.SKUCategoryID) && (Filter == null || i.SKU.SKUParent.Parent.Contains(Filter) || i.SKU.SKUParent.Description.Contains(Filter) || i.SKU.UPC == Filter) && (i.InventoryOrderStatusID == 1 || i.InventoryOrderStatusID == 2)); int TotalOrderedOrders = data.Where(i => i.InventoryOrderStatusID == 1).Count(); int TotalOrderedItems = data.Where(i => i.InventoryOrderStatusID == 1).Sum(i => i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0; decimal TotalOrderedCost = data.Where(i => i.InventoryOrderStatusID == 1).Sum(i => i.SKU.UnitCost * i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0.0m; int TotalShippedOrders = data.Where(i => i.InventoryOrderStatusID == 2).Count(); int TotalShippedItems = data.Where(i => i.InventoryOrderStatusID == 2).Sum(i => i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0; decimal TotalShippedCost = data.Where(i => i.InventoryOrderStatusID == 2).Sum(i => i.SKU.UnitCost * i.InventoryOrderHistories.Where(ioh => ioh.StatusMovedToID == 1).FirstOrDefault().Quantity) ?? 0.0m; TotalOrderedItems.Dump(); TotalOrderedCost.Dump(); TotalShippedOrders.Dump(); TotalShippedItems.Dump(); TotalShippedCost.Dump();

Comments

Sign In or Register to comment.