How can I COUNT rows in EntityFramework without having to load the contents?

c# database entity-framework


I'm trying to determine how to count the matching rows on a table using the EntityFramework.

The issue is that each row may contain many gigabytes of information (in a Binary field). The SQL would undoubtedly look something like this:

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

I could load every row and use this formula to obtain the count:

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

But that's really ineffective. Is there a simpler way?

Thank to everybody. I switched the database from a private connected to a standalone so that I could do profiling; although helpful, this has unexpected side effects.

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 since my real data is a little bit deeper.

My attempts are shown below. I don't understand why CASE 2 never accesses the database 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";

#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";

#if CASE_3
/// Forced loading also works, as stated in the OP...
bool ok = false;
foreach (var pallet in truck.Pallet) {
    foreach (var kase in pallet.Case) {
        var item = kase.Item.FirstOrDefault();
        if (item != null) {
            ok = true;
    if (ok) break;
if (!ok)
    return "No Items are in the Truck";

Additionally, CASE 1's SQL output is routed via sp_executesql, but

SELECT [Project1].[C1] AS [C1]
FROM   ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
    [GroupBy1].[A1] AS [C1]
        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

[Although you can see from the SQL that the Truck-Pallet and Pallet-Case connections are many-to-many, I don't really have any Trucks, Drivers, Pallets, Cases, or Items; nonetheless, I don't believe it matters. Since my actual items are immaterial and more difficult to explain, I modified the names.]

5/22/2009 5:27:05 PM

Accepted Answer

Question syntax

var count = (from o in context.MyContainer
             where o.ID == '1'
             from t in o.MyTable
             select t).Count();

Formula syntax:

var count = context.MyContainer
            .Where(o => o.ID == '1')
            .SelectMany(o => o.MyTable)

These two produce the same SQL query.

11/3/2014 4:09:58 PM

Popular Answer

I believe you're referring to

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

(Edited to include feedback)

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow