choose the top five in entity framework

c# 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?

1
66
4/16/2010 11:17:57 PM

Accepted Answer

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.

144
9/19/2017 8:47:29 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow