Subquery filtering through extension-method

Question

Background

I'm trying to clean up my code by extracting some code into some extension methods, as shown below. I stumbled upon LINQKit, which has the Expandable functionality. I created a simple extension method AtPointInTime, which takes a DateTimeOffset as input (See code below), but I keep getting an error. What am I doing wrong (Can't find a solution in the documentation)?

Implementation

[Expandable(nameof(AtPointInTimeImpl))]
public static IQueryable<TSource> AtPointInTime<TSource>(this IQueryable<TSource> query, DateTimeOffset pointInTime) where TSource : class, IBitemporal
{
    return query.AsExpandable().Where(entity => AtPointInTimeFilter<TSource>().Invoke(entity, pointInTime));
}

private static Expression<Func<IQueryable<TSource>, DateTimeOffset, IQueryable<TSource>>> AtPointInTimeImpl<TSource>() where TSource : class, IBitemporal
{
    return (query, pointInTime) => query.Where(entity => AtPointInTimeFilter<TSource>().Expand().Invoke(entity, pointInTime));
}

private static Expression<Func<TSource, DateTimeOffset, bool>> AtPointInTimeFilter<TSource>() where TSource : class, IBitemporal
{
    return (entity, pointInTime) => entity.ValidTimeFrom <= pointInTime && (entity.ValidTimeTo > pointInTime || entity.ValidTimeTo == null);
}

Usage

To get all companies at a specific point in time I utilize AtPointInTime (Note: Bitemporal storage solution).

The AtPointInTime can also be called in a subquery I therefore extended my AtPointInTime with Expandable.

Example #1

var companies = await _dbContext.Companies.AtPointInTime(DateTimeOffset.UtcNow).ToList()

Example #2

var query = from alarm in _dbContext.Alarms.AtPointInTime(request.PointInTime)
            select new GetAlarmQueryResult
            {
                Alarm = alarm,
                Company = _dbContext.Companies.AtPointInTime(alarm.Created).SingleOrDefault(),
            };

Error

The LINQ expression 'DbSet<Company>
    .Where(c => (entity, pointInTime) => (DateTimeOffset)entity.ValidTimeFrom <= pointInTime && (Nullable<DateTimeOffset>)entity.ValidTimeTo > (Nullable<DateTimeOffset>)pointInTime || entity.ValidTimeTo == null
        .Invoke(
            expr: c, 
            arg1: __pointInTime_0))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Answer 1

Try the following implementation:

[Expandable(nameof(AtPointInTimeImpl))]
public static IQueryable<TSource> AtPointInTime<TSource>(this IQueryable<TSource> query, DateTimeOffset pointInTime) 
    where TSource : class, IBitemporal
{
    return query.AsExpandable().Where(entity => entity.AtPointInTime(pointInTime));
}

private static Expression<Func<IQueryable<TSource>, DateTimeOffset, IQueryable<TSource>>> AtPointInTimeImpl<TSource>() 
    where TSource : class, IBitemporal
{
    return (query, pointInTime) => query.Where(entity => entity.AtPointInTime(entity, pointInTime));
}

[Expandable(nameof(AtPointInTimeEntityImpl))]
public static bool AtPointInTime<TSource>(this TSource entity, DateTimeOffset pointInTime) 
    where TSource : class, IBitemporal
{
    throw new NotImplementedException();
}

private static Expression<Func<TSource, DateTimeOffset, bool>> AtPointInTimeEntityImpl<TSource>() 
    where TSource : class, IBitemporal
{
    return (entity, pointInTime) => entity.ValidTimeFrom <= pointInTime && (entity.ValidTimeTo > pointInTime || entity.ValidTimeTo == null);
}

Also AsExpandable can be eliminated if you configure EF Core options:

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