本文介绍了如何在EntityFramework中不加载内容的COUNT行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我试图确定如何使用EntityFramework 计数 表上的匹配行。I'm trying to determine how to count the matching rows on a table using the EntityFramework.问题是每行可能有多个兆字节的数据(在二进制字段中)。当然,SQL将是这样: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:SELECT COUNT(*) FROM [MyTable] WHERE [fkID] = '1';我可以加载所有行,然后I could load all of the rows and then find 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?编辑:谢谢,所有。我已经从一个私人的连接移动数据库,所以我可以运行性能分析; 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 Trucks carrying Pallets of Cases of Items -- and I don't want the Truck to leave unless there is at least one Item in it.我的尝试如下所示。我没有得到的部分是CASE_2从不访问数据库服务器(MSSQL)。My attempts are shown below. The part I don't get is that CASE_2 never access the DB server (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从CASE_1生成的SQL通过 sp_executesql ,但是:And the SQL resulting from CASE_1 is piped through sp_executesql, but: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 [我不是真的有卡车,司机,托盘,箱子或物品;正如你可以从SQL看到的Truck-Pallet和Pallet-Case关系是多对多 - 虽然我不认为重要。推荐答案查询语法:Query syntax:var count = (from o in context.MyContainer where o.ID == '1' from t in o.MyTable select t).Count();方法语法:var count = context.MyContainer .Where(o => o.ID == '1') .SelectMany(o => o.MyTable) .Count()两者都生成相同的SQL查询。Both generate the same SQL query. 这篇关于如何在EntityFramework中不加载内容的COUNT行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
09-02 15:12