Alex Rivera | Logout

How to COUNT rows within EntityFramework without loading contents?

Asked 2009-05-20T21:35:16.990
143

I'm trying to determine how to count the matching rows on a table using the 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:

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?


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 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.

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);
Edit
Report

1 Answer

3

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();
answered 2009-05-20T21:43:34.697

Your Answer