选择实体框架中的前5名 [英] select top 5 in entity framework

问题描述

我有

[Person]
PersonID, EmailAddress, FirstName, LastName

[OnlineAccount]
OnlineAccountID, PersonID, Nickname

每个人都可以拥有0- * OnlineAccount。

Each person is allowed to have 0-* OnlineAccount.

在具有C＃的实体框架中，如何选择拥有最多帐户的前5名人员？

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

推荐答案

尝试一下：

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

这将返回 IQueryable< Person> 。它不会返回结果，因为它实现了延迟执行。它将转换为SQL并在需要时执行：

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

或

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

上面的示例将转换为与此类似的SQL：

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

这不是确切的SQL。不同的EF版本可能会产生不同的SQL，但是我写它来说明它是如何工作的。 Take（5）转换为 TOP 5 。 OrderByDescending（u => u.OnlineAccounts.Count）转换为 ORDER BY（从在线帐户中选择COUNT（*），在p.PersonID = oa.PersonID）DESC 。这是实体框架的力量。它将.NET表达式转换为SQL。

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.

这篇关于选择实体框架中的前5名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！