How To Count Associated Entities Without Fetching Them In Entity Framework

Question

I've been wondering about this one for a while now, so I thought it would be worth using my first Stack Overflow post to ask about it.

Imagine I have a discussion with an associated list of messages:

DiscussionCategory discussionCategory = _repository.GetDiscussionCategory(id);

discussionCategory.Discussions is a list of Discussion entities which is not currently loaded.

What I want is to be able to iterate through the discussions in a discussionCategory and say how many messages are in each discussion without fetching the message data.

When I have tried this before I have had to load the Discussions and the Messages so that I could do something like this:

discussionCategory.Discussions.Attach(Model.Discussions.CreateSourceQuery().Include("Messages").AsEnumerable());

foreach(Discussion discussion in discussionCategory.Discussions)
{

int messageCount = discussion.Messages.Count;

Console.WriteLine(messageCount);

}

This seems rather inefficient to me as I am fetching potentially hundreds of message bodies from the database and holding them in memory when all I wish to do is count their number for presentational purposes.

I have seen some questions which touch on this subject but they did not seem to address it directly.

Thanks in advance for any thoughts you may have on this subject.

Update - Some more code as requested:

public ActionResult Details(int id)
    {  
        Project project = _repository.GetProject(id);
        return View(project);
    }

Then in the view (just to test it out):

Model.Discussions.Load();
var items = from d in Model.Discussions select new { Id = d.Id, Name = d.Name, MessageCount = d.Messages.Count() };

foreach (var item in items) {
//etc

I hope that makes my problem a bit clearer. Let me know if you need any more code details.

Solution 1

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 you look at the generated SQL, you'll see that the Count() is done by the DB server.

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

Solution 2

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

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

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

1. Add a placeholder property to your class model using the [NotMapped] data annotation from the System.ComponentModel.DataAnnotations.Schema namespace. This gives you a place to store the calculated data without actually adding a column to your database or projecting to unnecessary 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 list of parent objects.

   var discussions = db.Discussions.ToList();
   

3. Capture the counts in a Dictionary. This generates a single SQL GROUP BY query with all parent IDs and child object counts. (Presuming DiscussionID is the 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 up the count from the dictionary, and store in the placeholder property.

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

5. Return your discussion list.

   return View(discussions);
   

6. Reference the MessageCount property in the View.

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

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

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

Hope this helps.

Solution 3

If you are using Entity Framework 4.1 or 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

Solution 4

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

1. Create your database view.

   Assuming you want all of the original entity properties plus the 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 Entity Framework Code First Migrations, see this SO answer on how to create a view.)

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

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

View more solutions

Comments

[Oligarchia]

Hi there, thanks for the answer. Unfortunately I have tried this before and found that the count property of the anonymous type returned zero for all discussions. I gave this method another try after seeing your answer, but with the same result. Perhaps I am misunderstanding something about the framework as far as keeping the entities 'connected' to the data store is concered. Can anyone else confirm that the above method should work?

[Brian Hasden]

That's fine. Like I said, I don't have a direct answer to his question but there is a lot of stuff out on the web saying that counts are either hard or impossible. I don't tend to believe someone when they say something is impossible but I wanted to present it to the asker and kick off the discussion.

[Kevin Gauthier]

Show your code; you're doing something wrong. We use this feature extensively in our shipping applications. I suspect you're trying to do this on an EF association property (like in your question) instead of in an L2E query (like in my answer). Those are completely different; the former is LINQ to Objects; the latter is LINQ to Entities.

[Oligarchia]

The code I used was identical in form to that you provided. Perhaps there is something further up the chain going wrong. I'll see if I can edit my original post to provide additional code if you think that will help. Thanks again for your assistance. I am taking a look at the blog post you linked to right now.

[Oligarchia]

After reading the blog post you provided it seems that my problem may lie in understanding how to use a repository to supply the same functionality you demonstrate by using the context directly.

[Kevin Gauthier]

That would explain it. Note that as long as your repository is returning IQueryable<> then you can do this kind of projection even outside of the repository, even if it's IQueryable<SomePoco> instead of IQueryable<SomeEntity>. As long as you stay in the IQueryable domain, the EF will always take the Count() back to SQL.

[Oligarchia]

Thanks for the tip, I'll go and give it a try.

[Bruno Santos]

Just tried to use this example but realized that d.Messages.Count() is executed for each element in Model.Discussions.

[aterbo]

I know this is an old answer, but is it possible to do this same idea with a conditional on the count? This works perfectly for me as-written (thanks!!) to get a raw count of associated objects. However, my associated objects have a bool marking if they are open or closed. Is there a way to modify the GroupBy to have a Where or Select to get the count of only the true objects with a single SQL query like this?

[Neil Laslett]

@aterbo Absolutely! Linq queries are very powerful. All you have to do is var _counts = db.Messages.Where(m => m.Status == "Open").GroupBy(m => m.DiscussionID).ToDictionary(d => d.Key, d => d.Count()); The Linq directives can be chained and process in order. No SQL is actually generated until the final ToDictionary call. If you look, it's an IQueriable up until that point.

[Neil Laslett]

Or if it's a bool value, it would be Where(m => m.Status == true)

[aterbo]

Great, thank you! EF Core wants me to add .AsEnumerable() to these .GroupBy() calls, which will pull all the entries into memory before doing the count (I think?), but I need to check if that's what's happening... It looks like .ToDictionary needs an IEnumerable

[Neil Laslett]

As long as the .AsEnumerable() falls after the .GroupBy(), that's ok. You're getting the enumeration of the grouped data, not of the individual records. This was done under .NET Framework, so the semantics might be a little different under Core/5/6, such as needing to add .AsEnumerable() into the call chain. Under Framework, I'm pretty sure you can call .ToDictionary() directly on an IQueriable.