Fetching the count of child entities with EF Core instead of the full entity using a generic repo with specification pattern?

Question

When I fetch a "Users" info for their profile page. I'm fetching multiple child entity collections from the "User" (parent) entity to either get info from it (ex. images) or I'm fetching them just to get a count (ex. Likes)

Problem - When fetching all the "Yogaband" children entities it's killing the time it takes to fetch (~12 secs) because, I assume, each "Yogaband" entity has many child entities and even the children have children, so there is a ton of data or the underlying query is complex (haven't looked) despite having roughly only ~15 "Yogaband" entities in the DB.

What I need - All I need is the count of "Yogabands" from the "User" and a few other counts of child collections but I'm not sure how to modify my specification pattern and generic repo to just fetch a count. The code now only fetches the entire entity (ex. AddInclude()) You can see in the automapper mapping code below I have YogabandCount, which is my attempt at keeping a numerical count of the "Yogabands" but that could end up getting disconnected from the actual count and probably isn't the best way to handle this problem. What I need is a better way to fetch the "Yogaband" count without getting the entire entity.

I'd like to have something like this below to the specification pattern

AddCount()

I'll start with my controller to fetch the "User"

[HttpGet("{username}", Name = "GetMember")]
public async Task<IActionResult> GetMember(string username)
{
    var spec = new MembersWithTypesSpecification(username);
    var user = await _unitOfWork.Repository<User>().GetEntityWithSpec(spec);
    if (user == null) return NotFound(new ApiResponse(404));
    var userToReturn = _mapper.Map<MemberForDetailDto>(user);
    return Ok(userToReturn);
}

Here is MembersWithTypesSpecification for creating everything I want

public class MembersWithTypesSpecification : BaseSpecification<User>
{
    public MembersWithTypesSpecification(string userName) 
        : base(x => x.UserName == userName) 
    {
        AddInclude(x => x.UserPhotos);
        AddInclude(x => x.PracticedStyles);
        AddInclude(x => x.PracticedPoses);
        AddInclude(x => x.InstructedStyles);
        AddInclude(x => x.InstructedPoses);
        AddInclude(x => x.InstructorPrograms);
        AddInclude(x => x.Yogabands);
        AddInclude(x => x.ReceivedReviews);
        AddInclude(x => x.Likers);     
    }
}

In the BaseSpecification file I have this below for AddInclude

public class BaseSpecification<T> : ISpecification<T>
{
    public BaseSpecification() {}
    public BaseSpecification(Expression<Func<T, bool>> criteria)
    {
        Criteria = criteria;
    }
    public Expression<Func<T, bool>> Criteria { get; }
    public List<Expression<Func<T, object>>> Includes { get; } = new List<Expression<Func<T, object>>>();
    public List<string> IncludeStrings { get; } = new List<string>();
    public Expression<Func<T, object>> OrderBy { get; private set; }
    public Expression<Func<T, object>> OrderByDescending { get; private set; }
    public Expression<Func<T, object>> GroupBy { get; private set; }
    public int Take { get; private set; }
    public int Skip { get; private set; }
    public bool IsPagingEnabled { get; private set; }
    protected void AddInclude(Expression<Func<T, object>> includeExpression)
    {
        Includes.Add(includeExpression);
    }
    protected void AddInclude(string includeString)
    {
        IncludeStrings.Add(includeString);
    }
    protected void AddOrderBy(Expression<Func<T, object>> orderByExpression)
    {
        OrderBy = orderByExpression;
    }
    protected void AddOrderByDescending(Expression<Func<T, object>> orderByDescExpression)
    {
        OrderByDescending = orderByDescExpression;
    }
    protected void AddGroupBy(Expression<Func<T, object>> groupByExpression)
    {
        GroupBy = groupByExpression;
    }
    protected void ApplyPaging(int skip, int take)
    {
        Skip = skip;
        Take = take;
        IsPagingEnabled = true;
    }
}

Here is GetEntityWithSpec() from my generic repo

public class GenericRepository<T> : IGenericRepository<T> where T : class
{
    private readonly DataContext _context;
    public GenericRepository(DataContext context)
    {
        _context = context;
    }
    public async Task<T> GetByIdAsync(int id)
    {
        return await _context.Set<T>().FindAsync(id);
    }
    public async Task<IReadOnlyList<T>> ListAllAsync()
    {
        return await _context.Set<T>().ToListAsync();
    }
    public async Task<T> GetEntityWithSpec(ISpecification<T> spec)
    {
        return await ApplySpecification(spec).FirstOrDefaultAsync();
    }
    public async Task<IReadOnlyList<T>> ListAsync(ISpecification<T> spec)
    {
        return await ApplySpecification(spec).ToListAsync();
    }
    public async Task<int> CountAsync(ISpecification<T> spec)
    {
        return await ApplySpecification(spec).CountAsync();
    }
    private IQueryable<T> ApplySpecification(ISpecification<T> spec)
    {
        return SpecificationEvaluator<T>.GetQuery(_context.Set<T>().AsQueryable(), spec);
    }
    public void Add(T entity)
    {
        _context.Set<T>().Add(entity);
    }
    public void Update(T entity)
    {
        _context.Set<T>().Attach(entity);
        _context.Entry(entity).State = EntityState.Modified;
    }
    public void Delete(T entity)
    {
        _context.Set<T>().Remove(entity);
    }
    public async Task<bool> SaveChangesAsync()
    {
        return await _context.SaveChangesAsync() > 0;
    }
}

And finally here is how I map the data with Automapper after it's been fetched.

CreateMap<User, MemberForDetailDto>()
            .ForMember(d => d.YearsPracticing, o => o.MapFrom(s => System.DateTime.Now.Year - s.YearStarted))
            .ForMember(d => d.Age, o => o.MapFrom(d => d.DateOfBirth.CalculateAge()))
            .ForMember(d => d.Gender, o => o.MapFrom(d => (int)d.Gender))
            .ForMember(d => d.Photos, opt => opt.MapFrom(src => src.UserPhotos.OrderByDescending(p => p.IsMain)))
            .ForMember(d => d.Yogabands, opt => opt.MapFrom(source => source.Yogabands.Where(p => p.IsActive).Count()))
            // .ForMember(d => d.Yogabands, opt => opt.MapFrom(source => source.YogabandsCount))
            // .ForMember(d => d.Likers, opt => opt.MapFrom(source => source.Likers.Count()))
            .ForMember(d => d.Likers, opt => opt.MapFrom(source => source.LikersCount))
            .ForMember(d => d.Reviews, opt => {
                opt.PreCondition(source => (source.IsInstructor == true));
                opt.MapFrom(source => (source.ReceivedReviews.Count()));
            })
            // .ForMember(d => d.Reviews, opt => opt.MapFrom(source => source.ReceivedReviews.Count()))
            .ForMember(d => d.ExperienceLevel, opt => opt.MapFrom(source => source.ExperienceLevel.GetEnumName()))
            .ForMember(d => d.PracticedStyles, opt => opt.MapFrom(source => source.PracticedStyles.Count())) 
            .ForMember(d => d.PracticedPoses, opt => opt.MapFrom(source => source.PracticedPoses.Count())) 
            .ForMember(d => d.InstructedStyles, opt => opt.MapFrom(source => source.InstructedStyles.Count())) 
            .ForMember(d => d.InstructedPoses, opt => opt.MapFrom(source => source.InstructedPoses.Count()))
            .ForMember(d => d.InstructorPrograms, opt => opt.MapFrom(source => source.InstructorPrograms.Where(p => p.IsActive == true)));

Answer 1

I assume that if you use Specification Pattern, what you do is trying to do things "DDD like".

First of all, you should re-think why do you use repository-pattern when your repository methods returns IQueryable. To use specifications with EF you dont need repositories at all - especially when you use such a implementation.

Second thing, what you trying to achieve is responsibility of repository. So you should move all Include / Pagination etc. to the specialized repository methods. Your specifications should contain predicates without additional logic (should contain only domain logic).

So create specialized repository like UserRepository then use it together with specification pattern eg.

class UserRepository : RepositoryBase {
    int CountUsers(UserSpecification spec){
        var query = ApplySpecification(spec);
        return query.Count();
    } 
}

So you'll be able to evaluate things on DB's side.

Also... you should abvoid using AutoMapper in such a ugly way. In case of that many custom mappings, it's easier & safer to map it manually. You can also consider to use projections instead of mapping AFTER fetch - check ProjectTo query extension.

Answer 2

I think it is an interesting problem, one whose origin is an intellectual hole i have found myself looking up from countless of times myself :) It's the strive to make generic solutions! Sometimes we get punished for that with Linq because when execution contains functions it often resolves the projected data before calling the given function. What i belive you need to do is to lower your generic ambition and inline your functions to allow EF/ Linq to work with the full queries and as you're reading also make sure to make it non-tracking.

Try benchmarking this one below against your generic-ness, for performance i propose you sacrifice generic structure for specific. So we're building our things into the repository when it need to be quick. I make a few assumtions about data for the example, please let me know if there are usefully corrections for clairity, as i do not see the dbcontext in your code.

public class UserDbContext : DbContext {
  DbSet<AppUser> Users { get; set; }
  DbSet<Yogabands> Yogabands { get; set; }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<AppUser>().HasKey(k => k.AppUserId);
    modelBuilder.Entity<Yogabands>().HasKey(k => k.YogabandsId);
    modelBuilder.Entity<AppUser>().HasMany(m => m.Yogabands).WithMany(n => n.Users);
  }

  public int CountYogaBands(Guid appUserId)
  {
    return Users.AsNoTracking()
            .Include(u => u.Yogabands).AsNoTracking()
            .Where(x => x.AppUserId == appUserId)
            .Select(y => y.Yogabands.Count())
            .FirstOrDefault();
  }

So since what you're asking is how do I do this fast, my suggestion is to push the query to the dbcontext and make it specific to avoid the functional feature of when using functions they get resolved after, will it need N accessor methods instead of 1, yes, but consider if that couldn't be ok, after all you could be returning multiple counts into a new object, like

...Select(y => new {
     Yogabands = y.Yogabands.Count(),
     Likers = y.Likers.Count()
   })
   .FirstOrDefault();

Though you'd propably prefer a defined type to a generic to more easily be able to work with the counts result.

My personal conclusion at least, and I stand to be corrected obviously, is that EF Core is not yet mature enough to incorporate dynamic functions into the Linq resolving the ultimate query, nor have I ever seen a version of EF which is fully able, you have to 'denormalize' and cannot fully pursue the DRY principle.

Still EF Core does a banger of a job when you give it the entire linq expression up front and we should favor that for performance IMHO.