How to filter collection in linq based on some subobjects in that list-CodePudding

I want to get only those employees that have at least one service, and which that service is younger than current date (dt)

I tried with .Any() but it returns me all employees with all services (it doesnt check that date)

var employees =
  employeeService.GetAllActiveEmployeesForCompanyForLocation(companyId, location.Id)
    .Where(x => x.IsCounter && x.Services != null && x.Services.Count > 0 &&
                x.Services.Any(u => u.ActiveTo >= dt.Value));

I want to filter just those employees which have at least one service or more where ActiveTo is not in the past (dt is a current datetime.now)

CodePudding user response：

You can chain multiple .Where and .Select statements after one another. Your LINQ query is very hard to read without more specific information about your objects.

To make it more readable, I would suggest splitting your requirements into separate queries, like so:

var employeesWithActiveServices = 
    employeeService.GetAllActiveEmployeesForCompanyForLocation(companyId, location.Id)
        .Where(e => e.IsCounter && e.Services.Count >= 1)
        .Select(e => e.Services.Contains(s => s.ActiveTo >= DateTime.Now)).ToList();

Notice how I removed your e.Services != null check. It is redundant when you're already checking e.Services.Count.

This was made quickly of the top of my head, so you may need to tweak it to suit your needs.

It is still a hard LINQ query to read without seeing the objects it is querying, but this at least makes the query itself more readable.

CodePudding user response：

Try to remove any additional null, Count checks, otherwise SQL will be complex and slow:

var employees =
  employeeService.GetAllActiveEmployeesForCompanyForLocation(companyId, location.Id)
    .Where(x => x.IsCounter && 
                x.Services.Any(u => u.ActiveTo >= dt.Value));

EF Core translates your query into the SQL, which do not have NullReference exception.