Efficient count (using GROUP BY) for collection in Entity Framework

I'm using Entity Framework 4.1 with ASP.NET MVC 3. I have two tables with a one-to-many relationship, let's say Shop and ShopVisit, and I want to show the list of Shops with the count of Visits. Quite simple to do as an SQL query, but how do I get Entity Framework to do this efficiently?

This works:

_db.Shops.Include("Visits").ToList();


@foreach (var shop in ViewBag.Shops)
{
    Visit count: @shop.Visits.Count
}

However, looking at SQL Profiler, it seems to be loading all Visits using a LEFT OUTER JOIN, which is not what I want. I only want the count. Of course, I don't want a sub-query to be done for each Shop, either. What can I do to make it do a COUNT(*) ... GROUP BY query?

Even if I do a 开发者_如何学CSelect like this:

_db.Shops.Include("Visits").Select(s => ShopStats { Shop = s, Vists = s.Vists.Count}).ToList();

It still insists on doing a LEFT OUTER JOIN, which loads all Visits.

In such case you need to create custom type and use projection:

var data = from s in _db.Shops
           select new SomeCustomType 
               {
                   Shop = s,
                   Count = s.Visits.Count()
               };

You can also flatten your SomeCustomType so it will contain all properties of Shop instead of shop itself. It will probably do left outer join internally in the query but it will not load visits to the application.

Include or lazy loading works as you described - count will be computed in memory so all visits must be loaded.

Edit:

There is one more solution called extra-lazy loading. It is not supported out of the box but you can extend EF to support it. In case of extra-lazy loading you will not load Visits but Count will trigger query to database to get a count of visits.