How can I reuse the logic in a Where call while working with Entity Framework Core in .NET?

Question

I am writing a database query using LINQ in .NET and I want to be able to not duplicate the code I put in my Where method calls. All my entities inherit BaseEntity and have in common the properties used in the Where call.

public class BaseEntity
{
    public Guid Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
    public DateTime? DeletedAt { get; set; }
}

public class A : BaseEntity
{
    public List<B> Bs { get; set; }
}

public class B : BaseEntity
{
    public A A { get; set; }
}

At the moment I have written a query that works but I am not satisfied that I need to duplicate code. It looks like this:

var result = _context.Set<A>().Include(a => a.Bs.Where(b => b.DeletedAt == null && b.UpdatedAt > dateTime ||
                                                            b.DeletedAt != null && b.DeletedAt > dateTime))
                              .Where(a => a.DeletedAt == null && a.UpdatedAt > dateTime ||
                                          a.DeletedAt != null && a.DeletedAt > dateTime)
                              .ToList();

As you can see the code in Where calls is pretty much the same, yet I couldn't find a way to put it in a place and be able to reuse it. I did try to create an extension for IQueryable but I wouldn't be able to use it in the Where inside the Include becuause Bs is not IQueryable. Obviously, just putting the logic in a method doesn't work because it can't be translated.

Answer 1

A common expression should work, it possibly wasn't implemented correctly as they can be somewhat temperamental. For instance:

private Expression<Func<T, bool>> FilterByDate<T>(DateTime dateTime) where T : BaseEntity
{
    Expression<Func<T, bool>> expression = x => 
        (x.DeletedAt == null && x.UpdatedAt > dateTime) 
        || (x.DeletedAt != null && x.DeletedAt > dateTime)
    return expression;
}

Then in your query:

var result = _context.Set<A>().Include(a => a.Bs.Where(filterByDate<B>(dateTime))
    .Where(a => filterByDate<A>(dateTime))
    .ToList();

The tests I ran with a base class for dates looked to work for both the filtering of the items and the included children. It did require explicitly providing the type to the filter call.