Function in LINQ Query: could not be translated

I have a Database Structure to handle people (students, staff,...) that can be assigned to different institutes for different dates. Now there is a query to get all staff with the info if single people are currently "active" or not (meaning they are part of the institute on a specific day and are not away on maternityleave). Because this "isActive" thing is rather complicated and I will need it not only for this query but in other places as well, I created this method:

public static Func<StaffInstitute, bool> StaffIsActive(DateTime referenceDate)
{
	return pe => (pe.EntryDate == null || pe.EntryDate <= referenceDate.Date) &&
		(pe.ExitDate == null || pe.ExitDate >= referenceDate.Date) &&
		(
			!pe.Staff.MaternityLeave ||
			(
				pe.Staff.MaternityLeave &&
				pe.Staff.MaternityLeaveFrom.HasValue &&
				pe.Staff.MaternityLeaveUntil.HasValue &&
				(pe.Staff.MaternityLeaveFrom.Value.Date > referenceDate.Date || pe.Staff.MaternityLeaveUntil.Value.Date < referenceDate.Date)
			) ||
			(
				pe.Staff.MaternityLeave &&
				(pe.Staff.MaternityLeaveFrom.HasValue || pe.Staff.MaternityLeaveUntil.HasValue) &&
				(!pe.Staff.MaternityLeaveFrom.HasValue || pe.Staff.MaternityLeaveFrom.Value.Date >= referenceDate.Date) &&
				(!pe.Staff.MaternityLeaveUntil.HasValue || pe.Staff.MaternityLeaveUntil.Value.Date < referenceDate.Date)
			)
		);
}		

The idea was to easily reuse this so that in any query in the future I will be able to find the IsActive value without having to copy these 17 lines. So my query to get a list of all the staff looks like this:

Func<StaffInstitute, bool> staffIsActiveFunction = StaffIsActive(referenceDate);
IQueryable<StaffListDTO> staff =
	from staffInstitute
	in _context.StaffInstitute.Include(s => s.Staff)
	select new StaffListDTO()
	{
		StaffId = staffInstitute.StaffId,
		Name = staffInstitute.Staff.Name,
		IsActive = staffIsActiveFunction.Invoke(staffInstitute),
	};

This actually works fine, which was a pleasant surprise. This list has a pagination, since it get get quite long, so at the end this will be added to the query:

var res = staff.Skip(0).Take(30).ToList();

Unfortunately, the list of staff has a filter for the IsActive value. On occasion, I want to see only employees that are currently active, so I added this before the pagination:

if (istAktiv.HasValue)
{
	staff = staff.Where(p => p.IsActive);
}	

And this is where it stops working and I get the following error:

The LINQ expression '...' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'

And I am at my wits end. I cannot do .ToList at this point, because then the query will fetch ALL the entries from the database instead of only 30. What can I do to make this work? If I write the contents of StaffIsActive directly into my query, it suddenly works and I just don't understand. WHY?!

Any ideas on how to make this work?

I am not sure about your EF version, but from my recent experience in EF6, even that staffIsActiveFunction.Invoke(staffInstitute) shouldn't work. I guess they fixed that in the newer version?

Regardless, in EF6 I use Expression<Func<T, bool>> to handle situations like that.

        public static readonly Expression<Func<TEntity, bool>> IsSomethingExpression =
            e => !e.DeleteFlag && ... ; // boolean conditions

Then, I can apply the same conditions in different places like this:

            IQueryable<TEntity> basicQuery = DbContext.TEntity
                .Where(IsSomethingExpression);

IQueryable<T> has a .Where(Expression<Func<T, bool>>) overload.

So I guess you can:

1. Refactor StaffIsActive to return Expression<Func<StaffInstitute, bool>>.
2. Use it for both IsActive and .Where().

When we write LINQ, EF needs to translates the statements into SQL query. If the statement is too complicated or simply not supported by EF, it will throw. Therefore, one should always be careful when calling custom functions in EF queries. One general solution to that is to use Expressions like mentioned above.

Your query needs expression expanding. It can be done by different libraries, almost full list you can find here. I choose LINQKit:

Define stub function with ExpandableAttribute:

[Expandable(nameof(StaffIsActiveImpl))]
public static bool StaffIsActive(StaffInstitute pe, DateTime referenceDate)
{
    throw new NotImplementedException();
} 

Define private realisation which returns Expression. Result Expression's parameters should mimic orginal function:

private static Expression<Func<StaffInstitute, DateTime, bool>> StaffIsActiveImpl()
{
    return (pe, referenceDate) => (pe.EntryDate == null || pe.EntryDate <= referenceDate.Date) &&
        (pe.ExitDate == null || pe.ExitDate >= referenceDate.Date) &&
        (
            !pe.Staff.MaternityLeave ||
            (
                pe.Staff.MaternityLeave &&
                pe.Staff.MaternityLeaveFrom.HasValue &&
                pe.Staff.MaternityLeaveUntil.HasValue &&
                (pe.Staff.MaternityLeaveFrom.Value.Date > referenceDate.Date || pe.Staff.MaternityLeaveUntil.Value.Date < referenceDate.Date)
            ) ||
            (
                pe.Staff.MaternityLeave &&
                (pe.Staff.MaternityLeaveFrom.HasValue || pe.Staff.MaternityLeaveUntil.HasValue) &&
                (!pe.Staff.MaternityLeaveFrom.HasValue || pe.Staff.MaternityLeaveFrom.Value.Date >= referenceDate.Date) &&
                (!pe.Staff.MaternityLeaveUntil.HasValue || pe.Staff.MaternityLeaveUntil.Value.Date < referenceDate.Date)
            )
        );
}

If you are using EF Core, activate LINQKit during DbContextOptions building:

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension

For EF6 use AsExpandable()

IQueryable<StaffListDTO> staff =
    from staffInstitute in _context.StaffInstitute.AsExpandable() // not needed for EF Core
    select new StaffListDTO()
    {
        StaffId = staffInstitute.StaffId,
        Name = staffInstitute.Staff.Name,
        IsActive = StaffIsActive(staffInstitute, referenceDate),
    };