How to COUNT rows within EntityFramework without loading contents

Question

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   gt  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   gt   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    gt  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   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    I don t really have Trucks  Drivers  Pallets  Cases or Items  as you can see from the SQL the Truck-Pallet and Pallet-Case relationships are many-to-many -- although I don t think that matters   My real objects are intangibles and harder to describe  so I changed the names

User · Answer

I think you want something like  var count   context MyTable Count t   gt  t MyContainer ID     1       edited to reflect comments

User · Answer

As I understand it  the selected answer still loads all of the related tests  According to this msdn blog  there is a better way   http   blogs msdn com b adonet archive 2011 01 31 using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities aspx  Specifically  using  var context   new UnicornsContext         var princess   context Princesses Find 1           Count how many unicorns the princess owns      var unicornHaul   context Entry princess                         Collection p   gt  p Unicorns                         Query                          Count

User · Answer

Well  even the SELECT COUNT    FROM Table will be fairly inefficient  especially on large tables  since SQL Server really can t do anything but do a full table scan  clustered index scan     Sometimes  it s good enough to know an approximate number of rows from the database  and in such a case  a statement like this might suffice   SELECT      SUM used page count    8 AS SizeKB      SUM row count  AS  RowCount        OBJECT NAME OBJECT ID  AS TableName FROM      sys dm db partition stats WHERE      OBJECT ID   OBJECT ID  YourTableNameHere       AND  index id   0 OR index id   1  GROUP BY      OBJECT ID   This will inspect the dynamic management view and extract the number of rows and the table size from it  given a specific table  It does so by summing up the entries for the heap  index id   0  or the clustered index  index id   1    It s quick  it s easy to use  but it s not guaranteed to be 100  accurate or up to date  But in many cases  this is  good enough   and put much less burden on the server    Maybe that would work for you  too  Of course  to use it in EF  you d have to wrap this up in a stored proc or use a straight  Execute SQL query  call   Marc

User · Answer

Use the ExecuteStoreQuery method of the entity context   This avoids downloading the entire result set and deserializing into objects to do a simple row count      int count       using  var db   new MyDatabase           string sql    SELECT COUNT    FROM MyTable where FkId    0           object   myParams    1         var cntQuery   db ExecuteStoreQuery lt int gt  sql  myParams          count   cntQuery First lt int gt

User · Answer

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

User · Answer

This is my code   IQueryable lt AuctionRecord gt  records   db AuctionRecord  var count   records Count      Make sure the variable is defined as IQueryable then when you use Count   method  EF will execute something like   select count    from       Otherwise  if the records is defined as IEnumerable  the sql generated will query the entire table and count rows returned

User · Answer

Query syntax   var count    from o in context MyContainer              where o ID     1               from t in o MyTable              select t  Count      Method syntax   var count   context MyContainer              Where o   gt  o ID     1                SelectMany o   gt  o MyTable               Count     Both generate the same SQL query

[c#] How to COUNT rows within EntityFramework without loading contents?

Examples related to c#

Examples related to database

Examples related to entity-framework