choose the top five in entity framework

c# entity-framework

Question

I have

[Person]
PersonID, EmailAddress, FirstName, LastName

[OnlineAccount]
OnlineAccountID, PersonID, Nickname

Each individual is permitted to have a 0* OnlineAccount.

How do I choose the top 5 Persons with the most accounts in entity framework using C#?

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

Accepted Answer

Do this:

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

This returnsIQueryable<Person> . Deferred execution is used, therefore it hasn't yet returned any results. When necessary, it will be converted to SQL and run:

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

or

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

The previous example will convert to SQL like this:

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

This SQL won't be the exact same. Although various EF versions could generate different SQLs, I developed this to demonstrate how it works.Take(5) is equivalent toTOP 5 . OrderByDescending(u => u.OnlineAccounts.Count) is equivalent toORDER BY (SELECT COUNT(*) FROM OnlineAccount oa WHERE p.PersonID = oa.PersonID) DESC . Entity Framework's strength is in this. It converts SQL to.NET expressions.

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