C# 如何在不加载内容的情况下对 EntityFramework 中的行进行计数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/890381/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to COUNT rows within EntityFramework without loading contents?
提问by NVRAM
I'm trying to determine how to countthe matching rows on a table using the EntityFramework.
我正在尝试确定如何使用 EntityFramework计算表上匹配的行数。
The problem is that each row might have many megabytes of data (in a Binary field). Of course the SQL would be something like this:
问题是每一行可能有许多兆字节的数据（在二进制字段中）。当然，SQL 应该是这样的：
SELECT COUNT(*) FROM [MyTable] WHERE [fkID] = '1';
I could load all of the rows and thenfind the Count with:
我可以加载所有行，然后找到计数：
var owner = context.MyContainer.Where(t => t.ID == '1');
owner.MyTable.Load();
var count = owner.MyTable.Count();
But that is grossly inefficient. Is there a simpler way?
但这是非常低效的。有没有更简单的方法？
EDIT: Thanks, all. I've moved the DB from a private attached so I can run profiling; this helps but causes confusions I didn't expect.
编辑：谢谢，所有。我已将数据库从私有连接中移出，以便我可以运行分析；这有帮助，但会引起我没想到的混乱。
And my real data is a bit deeper, I'll use Truckscarrying Palletsof Casesof Items-- and I don't want the Truckto leave unless there is at least one Itemin it.
而我的真实数据是深一点，我会用卡车运送托盘的案件的资料-我不想让卡车离开除非有至少一个项目在里面。
My attempts are shown below. The part I don't get is that CASE_2 never access the DB server (MSSQL).
我的尝试如下所示。我不明白的部分是 CASE_2 从不访问数据库服务器 (MSSQL)。
var truck = context.Truck.FirstOrDefault(t => (t.ID == truckID));
if (truck == null)
return "Invalid Truck ID: " + truckID;
var dlist = from t in ve.Truck
where t.ID == truckID
select t.Driver;
if (dlist.Count() == 0)
return "No Driver for this Truck";
var plist = from t in ve.Truck where t.ID == truckID
from r in t.Pallet select r;
if (plist.Count() == 0)
return "No Pallets are in this Truck";
#if CASE_1
/// This works fine (using 'plist'):
var list1 = from r in plist
from c in r.Case
from i in c.Item
select i;
if (list1.Count() == 0)
return "No Items are in the Truck";
#endif
#if CASE_2
/// This never executes any SQL on the server.
var list2 = from r in truck.Pallet
from c in r.Case
from i in c.Item
select i;
bool ok = (list.Count() > 0);
if (!ok)
return "No Items are in the Truck";
#endif
#if CASE_3
/// Forced loading also works, as stated in the OP...
bool ok = false;
foreach (var pallet in truck.Pallet) {
pallet.Case.Load();
foreach (var kase in pallet.Case) {
kase.Item.Load();
var item = kase.Item.FirstOrDefault();
if (item != null) {
ok = true;
break;
}
}
if (ok) break;
}
if (!ok)
return "No Items are in the Truck";
#endif
And the SQL resulting from CASE_1 is piped through sp_executesql, but:
由 CASE_1 产生的 SQL 通过sp_executesql 进行管道传输，但是：
SELECT [Project1].[C1] AS [C1]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(cast(1 as bit)) AS [A1]
FROM [dbo].[PalletTruckMap] AS [Extent1]
INNER JOIN [dbo].[PalletCaseMap] AS [Extent2] ON [Extent1].[PalletID] = [Extent2].[PalletID]
INNER JOIN [dbo].[Item] AS [Extent3] ON [Extent2].[CaseID] = [Extent3].[CaseID]
WHERE [Extent1].[TruckID] = '....'
) AS [GroupBy1] ) AS [Project1] ON 1 = 1
[I don't really have Trucks, Drivers, Pallets, Cases or Items; as you can see from the SQL the Truck-Pallet and Pallet-Case relationships are many-to-many -- although I don't think that matters. My real objects are intangibles and harder to describe, so I changed the names.]
[我真的没有卡车、司机、托盘、箱子或物品；正如您从 SQL 中看到的那样，Truck-Pallet 和 Pallet-Case 关系是多对多的——尽管我认为这并不重要。我的实物是无形的，难以描述，所以我改了名字。]
采纳答案by Craig Stuntz
Query syntax:
查询语法：
var count = (from o in context.MyContainer
where o.ID == '1'
from t in o.MyTable
select t).Count();
Method syntax:
方法语法：
var count = context.MyContainer
.Where(o => o.ID == '1')
.SelectMany(o => o.MyTable)
.Count()
Both generate the same SQL query.
两者都生成相同的 SQL 查询。
回答by Kevin
I think you want something like
我想你想要类似的东西
var count = context.MyTable.Count(t => t.MyContainer.ID == '1');
(edited to reflect comments)
（编辑以反映评论）
回答by bytebender
I think this should work...
我认为这应该有效...
var query = from m in context.MyTable
where m.MyContainerId == '1' // or what ever the foreign key name is...
select m;
var count = query.Count();
回答by marc_s
Well, even the SELECT COUNT(*) FROM Tablewill be fairly inefficient, especially on large tables, since SQL Server really can't do anything but do a full table scan (clustered index scan).
好吧，即使这样SELECT COUNT(*) FROM Table也会相当低效，尤其是在大表上，因为 SQL Server 除了做全表扫描（聚集索引扫描）之外真的什么也做不了。
Sometimes, it's good enough to know an approximate number of rows from the database, and in such a case, a statement like this might suffice:
有时，知道数据库中的大致行数就足够了，在这种情况下，这样的语句可能就足够了：
SELECT
SUM(used_page_count) * 8 AS SizeKB,
SUM(row_count) AS [RowCount],
OBJECT_NAME(OBJECT_ID) AS TableName
FROM
sys.dm_db_partition_stats
WHERE
OBJECT_ID = OBJECT_ID('YourTableNameHere')
AND (index_id = 0 OR index_id = 1)
GROUP BY
OBJECT_ID
This will inspect the dynamic management view and extract the number of rows and the table size from it, given a specific table. It does so by summing up the entries for the heap (index_id = 0) or the clustered index (index_id = 1).
这将检查动态管理视图并从中提取行数和表大小，给定一个特定的表。它通过总结堆 (index_id = 0) 或聚集索引 (index_id = 1) 的条目来实现。
It's quick, it's easy to use, but it's not guaranteed to be 100% accurate or up to date. But in many cases, this is "good enough" (and put much less burden on the server).
它很快，易于使用，但不能保证 100% 准确或最新。但在很多情况下，这已经“足够好”了（并且给服务器带来的负担要小得多）。
Maybe that would work for you, too? Of course, to use it in EF, you'd have to wrap this up in a stored proc or use a straight "Execute SQL query" call.
也许这对你也有用？当然，要在 EF 中使用它，您必须将其包装在存储过程中或使用直接的“执行 SQL 查询”调用。
Marc
马克
回答by goosemanHyman
Use the ExecuteStoreQuerymethod of the entity context. This avoids downloading the entire result set and deserializing into objects to do a simple row count.
使用实体上下文的ExecuteStoreQuery方法。这避免了下载整个结果集并反序列化为对象以进行简单的行计数。
int count;
using (var db = new MyDatabase()){
string sql = "SELECT COUNT(*) FROM MyTable where FkId = {0}";
object[] myParams = {1};
var cntQuery = db.ExecuteStoreQuery<int>(sql, myParams);
count = cntQuery.First<int>();
}
回答by Quickhorn
As I understand it, the selected answer still loads all of the related tests. According to this msdn blog, there is a better way.
据我了解，所选答案仍会加载所有相关测试。根据this msdn blog，有更好的方法。
Specifically
具体来说
using (var context = new UnicornsContext())
var princess = context.Princesses.Find(1);
// Count how many unicorns the princess owns
var unicornHaul = context.Entry(princess)
.Collection(p => p.Unicorns)
.Query()
.Count();
}
回答by Yang Zhang
This is my code:
这是我的代码：
IQueryable<AuctionRecord> records = db.AuctionRecord;
var count = records.Count();
Make sure the variable is defined as IQueryable then when you use Count() method, EF will execute something like
确保变量定义为 IQueryable 然后当您使用 Count() 方法时，EF 将执行类似
select count(*) from ...
Otherwise, if the records is defined as IEnumerable, the sql generated will query the entire table and count rows returned.
否则，如果将记录定义为IEnumerable，则生成的sql 将查询整个表并计算返回的行数。
