.NET EF Core Automapper ProjectTo filtering is not working

Question

I have the following piece of code

var taskDTOs = await _repo.GetQueryable<TaskStatus>()
    .Where(s => s.Tasks.Any(t =>t.CompanyId == _userContext.CompanyId &&
       t.UserId == _userContext.UserId))
    .ProjectTo<GetTaskListDTO>(_mapper.ConfigurationProvider)
    .ToListAsync();

The issue is, all conditions in the "Where" clause are not getting applied at all, and the query is retrieving tasks from different companies and for different users. Why?

Answer 1

.Where(s => s.Tasks.Any(t =>t.CompanyId == _userContext.CompanyId &&
       t.UserId == _userContext.UserId))

Will not filter out the retrieved tasks, it will only filter out TaskStatus which does not have tasks for the user and company but will include all the tasks those statuses have (if projection includes them).

Actually it is a bit strange that you are querying TaskStatus and then map it to GetTaskListDTO, based on the names it sounds like you should query just Task's and then build the DTO. Something along these lines:

var tasks = await _repo.GetQueryable<Task>()
    .Where(t =>t.CompanyId == _userContext.CompanyId &&
       t.UserId == _userContext.UserId)
    //.ProjectTo<GetTaskDTO>(_mapper.ConfigurationProvider) // optional intermediate dto
    .ToListAsync();

var result = new GetTaskListDTO // or setup mapping
{ 

};

Potentially you can introduce some intermediate DTO to combine it with automapper.