如何在不加載內容的情況下COUNT在EntityFramework中的行?

c# database entity-framework

我正在嘗試確定如何使用EntityFramework 計算表上的匹配行。

問題是每行可能有許多兆字節的數據(在二進製字段中)。當然SQL會是這樣的:

SELECT COUNT(*) FROM [MyTable] WHERE [fkID] = '1';

我可以加載所有行, 然後找到Count:

var owner = context.MyContainer.Where(t => t.ID == '1');
owner.MyTable.Load();
var count = owner.MyTable.Count();

但這非常低效。有更簡單的方法嗎?


編輯:謝謝,所有。我已將數據庫從私有附件移動,因此我可以運行分析;這有助於但卻引起我沒想到的混亂。

而我的真實數據是深一點,我將使用攜帶物品 案件托盤 卡車 -我不想讓卡車離開除非有至少有一個項目

我的嘗試如下所示。我沒有得到的部分是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

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

[ 我真的沒有卡車,司機,托盤,箱子或物品;正如您從SQL中看到的那樣,Truck-Pallet和Pallet-Case關係是多對多的 - 儘管我認為這並不重要。我的真實對像是無形的,難以描述,所以我更改了名稱。 ]

一般承認的答案

查詢語法:

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查詢。


熱門答案

我想你想要的東西

var count = context.MyTable.Count(t => t.MyContainer.ID == '1');

(編輯以反映評論)



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因