ayilmaz ayilmaz - 23 days ago 9
C# Question

How to OrderBy and SelectTop5 with EF before selecting all rows into memory

I am trying to select top 5 rows and ordered-descending by Id coloumn without getting all rows into memory. My code is like below...

IList<Article> articles = _articleRepository.GetList(d => d.CategoryId == cat.Id).OrderByDescending(d=>d.Id).ToList();


I know this first gets all rows into memory and apply OrderBy and Top5 rules in memory and I believe there should be another way to this? I would like to apply these 2 rules before getting them all into memory How can I do that?

Answer

You need access to an IQueryable and perform the following LINQ:

dbContext.OrderByDescending(d => d.Id).Take(5).ToList();

This will convert the LINQ expressions into SQL and only fetch the relevant rows from the database. The ToList() will execute the query and load the five elements into memory.