如何在不加载内容的情况下对 EntityFramework 中的行进行计数?

我正在尝试使用 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 关系是多对多的——尽管我认为这并不重要.我的实物是无形的，难以描述，所以我改了名字.]