DEV Community

Discussion on: LINQ: Beware of deferred execution

View post
Collapse
 
mattpogue profile image
Matt Pogue •

I just recently had this exact realization, which led to a WHOLE BUNCH of code rewriting. I started using SQL Profiler and looking at my database transactions while running and my app and I was appalled - no wonder some of my pages were taking so long to load! I was assuming something like the following:

var items = (from i in Data.Items where i.Cost > 20 select i);
foreach(var item in items)....
Enter fullscreen mode Exit fullscreen mode

Would make 1 trip to the DB and loop through the returned collection. Wrong! The initial bug was a query similar to the above where the page was taking 3:30 to load - as in 3 minutes, 30 seconds. There were 275 items being returned in my query and - you guessed it - 275 round trips to the database; one trip for each item. Something like this fixes it:

List<Data.Item> items = (from i in Data.Items where i.Cost > 20 select i).ToList();
foreach(Data.Item item in items)...
Enter fullscreen mode Exit fullscreen mode

It may seem like a trivial distinction, but I didn't understand the consequences when developing my code initially which is causing headaches now that our database has grown significantly. Important lesson for all C#/.Net devs!