Global Query Filter with reference to same entity

[taisbak]

We have a corebanking system with a number (1200) of 'time series' tables with columns like this
Id, Timestamp, value1, value2 ...

The most recent row per Id represent the current state. Each time a row is logically updated, a new row is inserted.

We would like to add a filter, which retrieves the most recent rows:

        modelBuilder
            .Entity<MyEntity>()
            .HasQueryFilter(e1 =>
                e1.Timestamp == MyEntitySet
                        .Where(e2 => e2.Id == e1.Id)
                        .Max(e2 => e2.Timestamp)
            );

Unfortunately, it causes the following exception:

Unhandled exception. System.NotImplementedException: NonNavSource
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandNavigationsInExpression(NavigationExpansionExpression source, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandNavigationsInLambdaExpression(NavigationExpansionExpression source, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessWhere(NavigationExpansionExpression source, LambdaExpression predicate)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ApplyQueryFilter(NavigationExpansionExpression navigationExpansionExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitConstant(ConstantExpression constantExpression)
   at System.Linq.Expressions.ConstantExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.First[TSource](IQueryable`1 source)
   at Program.Main(String[] args) in C:\Users\u3308\source\repos\FilterWithSubselect\Program.cs:line 11

Steps to reproduce

Small program that illustrates the problem and throws exception above:

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

public class Program
{
    static void Main(string[] args)
    {
        using (var db = new Context())
        {
            var MostRecentRow = db.MyEntitySet
                .Where(e => e.Id == 1)
                .First();
        }
    }
}

public class MyEntity
{
    public int Id { get; set; }
    public DateTime Ts { get; set; }
    public string StringValue { get; set; }
}

public class Context : DbContext
{
    public DbSet<MyEntity> MyEntitySet { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("connection string not needed to replicate problem.")
            ;
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<MyEntity>()
            .HasQueryFilter(e1 =>
                e1.Ts == MyEntitySet
                        .Where(e2 => e2.Id == e1.Id)
                        .Max(e2 => e2.Ts)
            );
    }
}

Further technical details

EF Core version: 3.0.0-preview9.19418.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2019 16.3.0 preview 2.0

We really hope for a resolution, as it would simplify coding against the tables tremendously ;-)

Kind regards,
Simon

[smitpatel]

The exception message comes from #17111

Regardless, this filter may not be allowed due to infinite recursion. We apply query filters to the dbsets used inside query filter. So this filter would recursively load filter for MyEntity which would cause stackoverflow.

[taisbak]

Yes, EF should not load a filter when inside that same filter.

[maumar]

fixed in d0b21ed

[taisbak]

Great.
Now it works just beautifully.
You made my Wednesday a happy day :-)
Thank you.

[remeWdm]

I met the same problem as you mentioned above. How did you solve it? Can you tell me @taisbak