Filtering parent collection by grandchildren properties in EF Core

Question

Using EF core 5 and ASP.NET Core 3.1, I am trying to get a filtered collection based on a condition on its grandchildren collection. I have the following entities:

public class Organisation
{
        public int Id { get; set; }
        public int? OrganisationId { get; set; }
        public IEnumerable<Customer> Customers { get; set; }
}

public partial class Customer
{
        [Key]
        public uint Id { get; set; }
        public int? EmployerId { get; set; }
        public int? OrganisationId { get; set; }
        public List<TimecardProperties> TimecardsProperties { get; set; }
}

public partial class TimecardProperties
{     
        [Key]
        public int Id { get; set; }
        public int? EmployerId { get; set; }
        public int? Week { get; set; }
        public short? Year { get; set; }
}

The goal is to get all Organisations that have at least one customer and the customer has at least 1 timecard property that is in week=34 and year=2021.

So far I have tried the following:

////necessary join to get Organisations for user id
            IQueryable<Organisation> ouQuery = (from cou in _dbContext.Organisations
                           join uou in _dbContext.table2 on cou.OrganisationId equals uou.OrganisationId                           
                           where uou.UsersId == int.Parse(userId)                          
                           select cou)
                           .Where(cou => cou.Customers.Where(c => c.TimecardsProperties.Count > 0).Any())
                           .Include(cou => cou.Customers.Where(c => c.TimecardsProperties.Count > 0))
                        .ThenInclude(c => c.TimecardsProperties.Where(tc => tc.tWeek == 34 && tc.Year > 2020))
                             ;

This returns a organisation list that each have a customers list but some customers have a count of timecards 0. I don't want to have organisation in the returned list that does not have at least one item in the timecards collection.

Also, it is too slow, and if I try to filter the produced list its even slower (over 15 seconds)

I have also tried a raw sql query on the organisation db context but it is again very slow:

select distinct count(id) from organisation a where organisation_id  in (
select organisation_id from customers where employer_id in (select distinct employer_id from timecards a
inner join timecard_components b on a.id=b.timecards_id
where week IN(
34) and year in (2021,2021) and invoice !=0 and type = 'time'
 group by employer_id, week)
);

In general, I want to know the the total count of the returned organisation collection for pagination (so I don't need to include all attributes of each entity) as well as return only a part of the correct results, which satisfy the conditions, an organisation list that has at least 1 timecards in their customers by executing the query in the end like so:

ouQuery.Skip((page - 1) * pageSize).Take(pageSize).ToListAsync();

I have also tried the EntityFramework.Plus and projection with no results. How could I write this to achieve getting the total count of the organisation list and a part of these results (first 10) to display to the user?

Answer 1

Use navigation properties. This is the query you want:

var orgsQuery = dbContext.Organizations
    .Where( o => o.Customers.Any( c =>
        c.TimecardProperties.Any( tp =>
            tp.Year = 2021
            && tp.Week = 34 ) ) );

Add includes and other predicates as needed