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.

CodePudding user response：

A couple things to eliminate:

This expression:

return (entity, pointInTime) => entity.ValidTimeFrom <= pointInTime && (entity.ValidTimeTo > pointInTime || entity.ValidTimeTo == null);

should likely be:

return (entity, pointInTime) => entity.ValidTimeFrom <= pointInTime && (entity.ValidTimeTo == null || entity.ValidTimeTo > pointInTime);

Having it the other way around would have it assessing the nullable property in a comparison before the null check. EF may be able to generate that, but possibly not.

Where I've used AsExpandable it has always been on the DbSet, not IQueryable. Try moving the AsExpandable out of the extension method and calling:

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

If that works the extension method may need to be updated to work against DbSet<TEntity> rather than IQueryable<TEntity>. I doubt _dbContext.Companies.AsQueryable().AtPointInTime(...) would work, though it might be worth a shot? :)

Other that that it looks like it should work for wrapping a temporal check. If I had to hazard a guess either the AsExpandable() call within the extension rather than directly on the DbSet meant the AsExpandable didn't "take" in the expression builder, or the inverted condition might be getting passed as-is and bunging up the generation.

I'm assuming the Plain old manual linq expression works just fine?

var pointInTime = DateTimeOffset.UtcNow;

var companies = await _dbContext.Companies
    .Where(entity => entity.ValidTimeFrom <= pointInTime 
        && (entity.ValidTimeTo == null 
            || entity.ValidTimeTo > pointInTime)
    .ToListAsync();

CodePudding user response：

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