select top 5 in entity framework

Question

I have

[Person]
PersonID, EmailAddress, FirstName, LastName

[OnlineAccount]
OnlineAccountID, PersonID, Nickname

Each person is allowed to have 0-* OnlineAccount.

In entity framework with C#, how do I select the top 5 Person that has the most accounts?

Answer 1

Try this:

var items = context.PersonSet.OrderByDescending(u => u.OnlineAccounts.Count).Take(5);

This returns IQueryable<Person>. It doesn't return results yet, because it implements deferred execution. It will be translated to SQL and executed when needed:

var metarializedItems = items.ToList(); // ToList forces execution

or

foreach(var item in items) // foreach forces execution

Example above will translate to SQL similar to this one:

SELECT TOP 5 p.PersonID, p.EmailAddress, p.FirstName, p.LastName 
FROM Person p
ORDER BY (SELECT COUNT(*) FROM OnlineAccount oa WHERE p.PersonID = oa.PersonID) DESC

It won't be this exact SQL. Different EF version may produce different SQLs, but I wrote it to ilustrate how it works. Take(5) is translated to TOP 5. OrderByDescending(u => u.OnlineAccounts.Count) is translated to ORDER BY (SELECT COUNT(*) FROM OnlineAccount oa WHERE p.PersonID = oa.PersonID) DESC. This is power of Entity Framework. It translates .NET expressions to SQL.