Entity Framework Share Queries

Question

I don't quite know how to word this, so I'm just going to explain my scenario.

I have a scenario where I have a TerminationDate field on an EmploymentHistory table that can be null, or a date in the future. EmploymentHistory joins to an Employees table and it's a 1:M relationship, where a single Employee can have multiple EmploymentHistory records. That Employees table joins to a lot of different places such as a Users table that represents the front end portal the Employees can log in to.

I frequently need to grab only the active employees. The SQL logic for being active is WHERE TerminationDate IS NULL OR TerminationDate >= GETDATE(). So if TerminationDate is null or if it's set in the future.

So in EF I have these queries like this:

// Grab all Active Employees context.Employees.Where(e => e.EmploymentHistory.Any(eh => eh.TerminationDate == null || eh.TerminationDate >= DateTime.Today).ToList();

// Get all Users context.Users.Where(u => u.Employee.EmploymentHistory.Any(eh => eh.TerminationDate == null || eh.TerminationDate >= DateTime.Today).ToList();

etc

This logic shows up in about 5 different places. How do I share the EmploymentHistory.Any(eh => eh.TerminationDate == null || eh.TerminationDate >= DateTime.Today part of the logic when the base nav prop may be different? Tried playing with Expression<Func<T, bool>> but got no where other than I think I have a solution if I can make all the Entities that need this inherit from a Base and could always assume there's a nav prop called Employee sitting there and the Base is my T. If I try an extension method, EF Core can only evaluate it client side, not server side.

Answer 1

You could write an expression for the EmploymentHistory: Expression<Func<EmploymentHistory, bool>> activeEmployee = eh => eh.TerminationDate == null || eh.TerminationDate >= DateTime.Today;

And use it like so:
context.Employees.Where(e => e.EmploymentHistory.Any(activeEmployee)).ToList();
and
context.Users.Where(u => u.Employee.EmploymentHistory.Any(activeEmployee)).ToList();.

It's not a huge reduction in code, but a lot less typing, and even better if you need to change the logic for some reason and don't want to change it everywhere is shows up.