[ACCEPTED]-How To Count Associated Entities Without Fetching Them In Entity Framework-count

Easy; just project onto a POCO (or anonymous) type:

var q = from d in Model.Discussions
        select new DiscussionPresentation
        {
            Subject = d.Subject,
            MessageCount = d.Messages.Count(),
        };

When 3 you look at the generated SQL, you'll see 2 that the Count() is done by the DB server.

Note 1 that this works in both EF 1 and EF 4.

If you are using Entity Framework 4.1 or 1 later, you can use:

var discussion = _repository.GetDiscussionCategory(id);

// Count how many messages the discussion has 
var messageCount = context.Entry(discussion)
                      .Collection(d => d.Messages)
                      .Query()
                      .Count();

Source: http://msdn.microsoft.com/en-US/data/jj574232

I know this is an old question but it seems 44 to be an ongoing problem and none of the 43 answers above provide a good way to deal 42 with SQL aggregates in list views.

I am assuming 41 straight POCO models and Code First like 40 in the templates and examples. While the 39 SQL View solution is nice from a DBA point 38 of view, it re-introduces the challenge 37 of maintaining both code and database structures 36 in parallel. For simple SQL aggregate queries, you 35 won't see much speed gain from a View. What 34 you really need to avoid are multiple (n+1) database 33 queries, as in the examples above. If you 32 have 5000 parent entities and you are counting 31 child entities (e.g. messages per discussion), that's 30 5001 SQL queries.

You can return all those 29 counts in a single SQL query. Here's how.

1. Add 28 a placeholder property to your class model 27 using the [NotMapped] data annotation from the System.ComponentModel.DataAnnotations.Schema namespace. This 26 gives you a place to store the calculated 25 data without actually adding a column to 24 your database or projecting to unnecessary 23 View Models.

   ...
   using System.ComponentModel.DataAnnotations;
   using System.ComponentModel.DataAnnotations.Schema;
   
   namespace MyProject.Models
   {
       public class Discussion
       {
           [Key]
           public int ID { get; set; }
   
           ...
   
           [NotMapped]
           public int MessageCount { get; set; }
   
           public virtual ICollection<Message> Messages { get; set; }
       }
   }
   

2. In your Controller, get the 22 list of parent objects.

   var discussions = db.Discussions.ToList();
   

3. Capture the counts 21 in a Dictionary. This generates a single 20 SQL GROUP BY query with all parent IDs and 19 child object counts. (Presuming DiscussionID is the 18 FK in Messages.)

   var _counts = db.Messages.GroupBy(m => m.DiscussionID).ToDictionary(d => d.Key, d => d.Count());
   

4. Loop through the parent objects, look 17 up the count from the dictionary, and store 16 in the placeholder property.

   foreach (var d in discussions)
       {
           d.MessageCount = (_counts.ContainsKey(d.ID)) ? _counts[d.ID] : 0;
       }
   

5. Return your 15 discussion list.

   return View(discussions);
   

6. Reference the MessageCount property 14 in the View.

   @foreach (var item in Model) {
       ...
       @item.MessageCount
       ...
   }
   

Yes, you could just stuff that 13 Dictionary into the ViewBag and do the lookup 12 directly in the View, but that muddies your 11 view with code that doesn't need to be there.

In 10 the end, I wish EF had a way to do "lazy 9 counting". The problem with both lazy and 8 explicit loading is you're loading the objects. And 7 if you have to load to count, that's a potential 6 performance problem. Lazy counting wouldn't 5 solve the n+1 problem in list views but 4 it sure would be nice to be able to just 3 call @item.Messages.Count from the View without having to worry 2 about potentially loading tons of unwanted 1 object data.

Hope this helps.

If this isn't a one off, and you find yourself 9 needing to count a number of different associated 8 entities, a database view might be a simpler 7 (and potentially more appropriate) choice:

1. Create 6 your database view.

   Assuming you want all 5 of the original entity properties plus the 4 associated message count:

   CREATE VIEW DiscussionCategoryWithStats AS
   SELECT dc.*,
         (SELECT count(1) FROM Messages m WHERE m.DiscussionCategoryId = dc.Id)
             AS MessageCount
   FROM DiscussionCategory dc
   

   (If you're using 3 Entity Framework Code First Migrations, see 2 this SO answer on how to create a view.)

2. In EF, simply 1 use the view instead of the original entity:

   // You'll need to implement this!
   DiscussionCategoryWithStats dcs = _repository.GetDiscussionCategoryWithStats(id);
   
   int i = dcs.MessageCount;
   ...
   

I don't have a direct answer but can only 8 point you to the following comparison between 7 NHibernate and EF 4.0 which seems to suggest 6 that even in EF 4.0 there is no out of the 5 box support for getting counts of a related 4 entity collection without retrieving the 3 collection.

http://ayende.com/Blog/archive/2010/01/05/nhibernate-vs.-entity-framework-4.0.aspx

I've upvoted and starred your 2 question. Hopefully someone will chime in 1 with a workable solution.

I've come across the same issue when dealing 10 with multiple mappers including EF and DevExpress 9 XPO (which doesn't even allow a single entity 8 to map to multiple tables). What I found 7 to be the best solution is to basically 6 use the EDMX and T4 templates to generate 5 updatable views in SQL Server (with instead 4 of triggers) and that way you have low level 3 control over the sql so you can do sub-queries 2 in select clause, use all kinds of complex 1 joins to bring in data and so on.