How can I get the count of a list in an Entity Framework model without including/loading the entire collection?

Question

I have a model in Entity Framework Core that goes something like this:

public class Anime
{
     public int EpisodeCount { get { return Episodes.Count() } }
     public virtual ICollection<Episode> Episodes { get; set; }
}

I'm having the issue of EpisodeCount being 0. The solution currently is to run a .Include(x => x.Episodes) within my EF query, but that loads the entire collection of episodes where it's not needed. This also increases my HTTP request time, from 100ms to 700ms which is just not good.

I'm not willing to sacrifice time for simple details, so is there a solution where I can have EF only query the COUNT of the episodes, without loading the entire collection in?

I was suggested to do this

var animeList = context.Anime.ToPagedList(1, 20);
animeList.ForEach(x => x.EpisodeCount = x.Episodes.Count());
return Json(animeList);

but this also returns 0 in EpisodeCount, so it's not a feasible solution.

Answer 1

You need to project the desired data into a special class (a.k.a. ViewModel, DTO etc.). Unfortunately (or not?), in order to avoid N + 1 queries the projection must not only include the count, but all other fields as well.

For instance:

Model:

public class Anime
{
    public int Id { get; set; }
    public string Name { get; set; }
    // other properties...
    public virtual ICollection<Episode> Episodes { get; set; }
}

ViewModel / DTO:

public class AnimeInfo
{
    public int Id { get; set; }
    public string Name { get; set; }
    // other properties...
    public int EpisodeCount { get; set; }
}

Then the following code:

var animeList = db.Anime.Select(a => new AnimeInfo
{
    Id = a.Id,
    Name = a.Name,
    EpisodeCount = a.Episodes.Count()
})
.ToList();

produces the following single SQL query:

SELECT [a].[Id], [a].[Name], (
     SELECT COUNT(*)
     FROM [Episode] AS [e]
     WHERE [a].[Id] = [e].[AnimeId]
) AS [EpisodeCount]
FROM [Anime] AS [a]