One useful tip of LINQ to Entities

I have a project with EF to access database and consume the entities by LINQ. There is a small bug been found after spending tons of time which caused very bad performance issue even there are only 5000 records in that table. Here is the original code:

var dataFiltered = dbContext.Datas.ToList().Where(a.status == 1);
var lastRec=dataFiltered.LastOrDefault();

It tried to retrieve the last record from a selected record set. LINQ To Entities does not recognize the method Last or LastOrDefault. So the code converted the record set to list then get last record which worked but I DO NOT RECOMMEND THIS! as this will cause huge performance issue. Above code took about 6 seconds to query in 5000 records table and got the record I wanted.

The fact of LINQ to Entity eventually has to translate the query to SQL and SQL has a SELECT TOP (in T-SQL) but not a SELECT BOTTOM (no such thing).  Calling .ToList() on data before .Last(), which will immediately execute the LINQ To Entities Expression that has been built up to that point, and then .Last() will work, because at that point the .Last() is effectively executed in the context of a LINQ to Objects Expression instead. But it could bring back thousands of records and waste loads of CPU materialising objects that will never get used.

There is an easy way around it though, just order descending and then do a First(). I changed the above code to following one line code and it took only 600 ms to achieve the same result as above code. It was 10 times faster.

var lastRec= dbContext.Datas.OrderByDescending(a => a.id).FirstOrDefault(a => a.status == 1);

So be careful to use .ToList() in LINQ to Entity.

This entry was posted in Entity Framework, Information Technology and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s