Very slow query in EF Core that fetches multiple children entities with .Include()?

Question

I have this query below that fetches all these children entities (ex. PracticedStyles, ReceivedReviews, InstructedPoses, etc)

All I need is the .count() for these children collections.

Question - Is there a faster way to run this query to get the counts for these includes, then using this query below? The run time is ~4-7 seconds and this is with very little data in the DB.

var userWithChildren = await _dbContext.Users
        .Include(p => p.Yogabands.Where(p => p.IsActive == true))
        .Include(p => p.PracticedStyles)
        .Include(p => p.PracticedPoses)
        .Include(p => p.ReceivedReviews)
        .Include(p => p.InstructedStyles)
        .Include(p => p.InstructedPoses)
        .Include(p => p.InstructorPrograms)
        .FirstOrDefaultAsync(user => user.UserName == userName);

Answer 1

Absolutely there is. Include is a blunt instrument which returns every row on the join. Worse still, because of the way SQL operates you get the results back in a normalized form, meaning that the total number of rows returned is all of those counts you're after multiplied together. With as many Includes as you have this could be tens of thousands of rows.

What you want is a projection, something like this:

var userWithChildren = await _dbContext.Users
    .Select(p => new
    {
        user = p,
        YogaBandCount = p.Yogabands.Where(yb => yb.IsActive == true).Count(),
        PracticedStylesCount = p.PracticedStyles.Count(),
        PracticedPosesCount = p.PracticedPoses.Count()
        ReceivedReviewsCount = p.ReceivedReviews.Count(),
        InstructedStylesCount = p.InstructedStyles.Count(),
        InstructedPosesCount = p.InstructedPoses.Count()
        InstructorProgramsCount = p.InstructorPrograms.Count()
    })
    .FirstOrDefaultAsync(p => p.user.UserName == userName);

This will create an anonymous class which has the user on it as well as all the counts you need.