set top

I have

PersonID, EmailAddress, FirstName, LastName

OnlineAccountID, PersonID, Nickname

Each person is allowed vĩ đại have 0-* OnlineAccount.

Bạn đang xem: set top

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

asked Apr 16, 2010 at 23:17

fishiefishie's user avatar

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 vĩ đại SQL and executed when needed:

Xem thêm: phiên tòa tình yêu tập 8

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


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

Example above will translate vĩ đại SQL similar vĩ đại 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 vĩ đại ilustrate how it works. Take(5) is translated vĩ đại TOP 5. OrderByDescending(u => u.OnlineAccounts.Count) is translated vĩ đại ORDER BY (SELECT COUNT(*) FROM OnlineAccount oa WHERE p.PersonID = oa.PersonID) DESC. This is power of Entity Framework. It translates .NET expressions vĩ đại SQL.

answered Apr 16, 2010 at 23:32


Tác giả

Bình luận