Regarding using HasQueryFilter

[esersahin]

Different sql is generated from the direct use of the HasQueryFilter property in OnModelCreating. When it is used over a static extension within the OnModelCreating method, different sql is generated.
I could not understand the logic of the work.

How can I get sql to be by taking parameters within the static extension method?

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        modelBuilder.Entity<TblLabel>().HasQueryFilter(x => x.LanguageCode == _languageCode);

        base.OnModelCreating(modelBuilder);
}

result => WHERE [t].[LanguageCode] = @__ef_filter___languageCode_0

protected override void OnModelCreating(ModelBuilder modelBuilder)
{

    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        if (typeof(ILanguageCode).IsAssignableFrom(entityType.ClrType))
        {
            entityType.AddLanguageCodeQueryFilter(_languageCode);
        }
    }

    base.OnModelCreating(modelBuilder);
}

Extension Metod

public static class LanguageQueryExtension
{
    public static void AddLanguageCodeQueryFilter
    (
        this IMutableEntityType entityData, 
        LanguageCode languageCode
    )
    {
        var methodToCall = typeof(LanguageQueryExtension)
            .GetMethod(nameof(LanguageQueryFilter),
                BindingFlags.NonPublic | BindingFlags.Static)
            .MakeGenericMethod(entityData.ClrType);
        var filter = methodToCall.Invoke(null, new object[] { languageCode });
        entityData.SetQueryFilter((LambdaExpression)filter);
    }

    private static LambdaExpression LanguageQueryFilter<TEntity>(LanguageCode languageCode)
        where TEntity : class, ILanguageCode
    {
        Expression<Func<TEntity, bool>> filter = x => x.LanguageCode == languageCode;
        return filter;
    }
}

Result => WHERE [t].[LanguageCode] = CAST(0 AS tinyint)

public enum LanguageCode
{
    English = 0,
    French = 1
}

public interface ILanguageCode
{
    LanguageCode LanguageCode { get; set; }
}

public class TblLabel : ILanguageCode
{
    public int Id { get; set; }

    public string Name { get; set; }

    public LanguageCode LanguageCode { get; set; }
}

Include provider and version information

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer 7.0.0)
Target framework: (7.0.101 [/usr/local/share/dotnet/sdk])
Operating system: MacOS Monterey Version 12.6.2
IDE: (e.g. Visual Studio Code Version: 1.74.2)

[ajcvickers]

@esersahin Generally, you will need to make sure the expression tree passed to HasQueryFilter has exactly the same form as the one created by the compiler. This includes ensuring that parameters are correctly bound and that there are not any additional nodes in the tree. I suggest using the debugger to compare your expression to the compiler-generated expression.

[esersahin]

Let me explain the issue more clearly with another example.

The situation here is related to the creation of the same expression outside the static method and inside the static method.

When you create the expression outside of the static method and send it as a parameter, a different sql is generated, while when you create the same expression in a static method, a different sql is generated.

I want to understand the working logic here.

Expression is created outside of static method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
      Expression<Func<ILanguageCode, bool>> filterExpr = e => ((ILanguageCode)e).LanguageCode == _languageCode;
      modelBuilder.AddLanguageCode(filterExpr);
}

public static void AddLanguageCode(this ModelBuilder modelBuilder, Expression<Func<ILanguageCode, bool>> filterExpr)
{

    foreach (var mutableEntityType in modelBuilder.Model.GetEntityTypes())
    {
        if (mutableEntityType.ClrType.IsAssignableTo(typeof(ILanguageCode)))
        {
            var parameter = Expression.Parameter(mutableEntityType.ClrType);
            var body = ReplacingExpressionVisitor.Replace(filterExpr.Parameters.First(), parameter, filterExpr.Body);
            var lambdaExpression = Expression.Lambda(body, parameter);

            mutableEntityType.SetQueryFilter(lambdaExpression);
        }
    }
}

Generated sql => WHERE [t].[LanguageCode] = @__ef_filter___languageCode_0

Expression is created inside a static method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.AddLanguageCode(_languageCode);
}

public static void AddLanguageCode(this ModelBuilder modelBuilder, LanguageCode languageCode)
{
    Expression<Func<ILanguageCode, bool>> filterExpr = e => ((ILanguageCode)e).LanguageCode == languageCode;

    foreach (var mutableEntityType in modelBuilder.Model.GetEntityTypes())
    {
        if (mutableEntityType.ClrType.IsAssignableTo(typeof(ILanguageCode)))
        {
            var parameter = Expression.Parameter(mutableEntityType.ClrType);
            var body = ReplacingExpressionVisitor.Replace(filterExpr.Parameters.First(), parameter, filterExpr.Body);
            var lambdaExpression = Expression.Lambda(body, parameter);

            mutableEntityType.SetQueryFilter(lambdaExpression);
        }
    }
}

Generated sql => WHERE [t].[LanguageCode] = CAST(0 AS tinyint)

[roji]

@esersahin what's _languageCode in your second example? Is it a static private field?

In general, it's really hard to help you with only the bits and snippets you've provided - can you please submit a minimal, runnable code sample which shows the problematic behavior?

[ajcvickers]

@esersahin The expressions are not the same. Just looking at the DebugView of the expression (which is simplified and so can look the same for different expressions) shows they are different. In the HasQueryFilter case, the expression is:

.Lambda #Lambda1<System.Func`2[TblLabel1,System.Boolean]>(TblLabel1 $x) {
    (System.Int32)$x.LanguageCode == (System.Int32).Constant<SomeDbContext>(SomeDbContext)._languageCode
}

In the AddLanguageCodeQueryFilter case, the expression is:

.Lambda #Lambda1<System.Func`2[TblLabel2,System.Boolean]>(TblLabel2 $x) {
    (System.Int32)$x.LanguageCode == (System.Int32).Constant<LanguageQueryExtension+<>c__DisplayClass1_0`1[TblLabel2]>(LanguageQueryExtension+<>c__DisplayClass1_0`1[TblLabel2]).languageCode
}

The biggest difference here is that in the first case _languageCode is captured directly from the DbContext, whereas in the second case, _languageCode has been transformed into a constant expression, since its value was passed to the method. Query filters work by taking the current value for the filter from the current DbContext instance. This cannot work with the expressionm built in the method call case, since the context and field are no longer captured.

As I said before, if you want to manually build expression trees like this, then the best thing to do is look at the structure of the compiler-generated expression tree and make sure that the form duplicated.

In this particular case, you'll need to pass the DbContext instance to your method. I did a quick and dirty example below, but you'll need to figure out the factoring you want.

public class SomeDbContext : DbContext
{
    public LanguageCode _languageCode;

    public SomeDbContext(LanguageCode languageCode)
    {
        _languageCode = languageCode;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TblLabel1>().HasQueryFilter(x => x.LanguageCode == _languageCode);
        modelBuilder.Entity<TblLabel2>();

        var entityType = modelBuilder.Model.FindEntityType(typeof(TblLabel2))!;
        if (typeof(ILanguageCode).IsAssignableFrom(entityType.ClrType))
        {
            entityType.AddLanguageCodeQueryFilter(this);
        }
    }
}

public static class LanguageQueryExtension
{
    public static void AddLanguageCodeQueryFilter
    (
        this IMutableEntityType entityData,
        SomeDbContext context
    )
    {
        var methodToCall = typeof(LanguageQueryExtension)
            .GetMethod(nameof(LanguageQueryFilter),
                BindingFlags.NonPublic | BindingFlags.Static)
            .MakeGenericMethod(entityData.ClrType);

        var filter = methodToCall.Invoke(null, new object[] { context });
        entityData.SetQueryFilter((LambdaExpression)filter);
    }

    private static LambdaExpression LanguageQueryFilter<TEntity>(SomeDbContext context)
        where TEntity : class, ILanguageCode
    {
        Expression<Func<TEntity, bool>> filter = x => x.LanguageCode == context._languageCode;
        return filter;
    }
}

public enum LanguageCode
{
    English = 0,
    French = 1
}

public interface ILanguageCode
{
    LanguageCode LanguageCode { get; set; }
}

public class TblLabel1 : ILanguageCode
{
    public int Id { get; set; }

    public string Name { get; set; }

    public LanguageCode LanguageCode { get; set; }
}

public class TblLabel2 : ILanguageCode
{
    public int Id { get; set; }

    public string Name { get; set; }

    public LanguageCode LanguageCode { get; set; }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new SomeDbContext(LanguageCode.English))
        {
            await context.Database.EnsureDeletedAsync();
            await context.Database.EnsureCreatedAsync();

            context.AddRange(
                new TblLabel1 { Name = "1E", LanguageCode = LanguageCode.English },
                new TblLabel2 { Name = "2E", LanguageCode = LanguageCode.English },
                new TblLabel1 { Name = "1F", LanguageCode = LanguageCode.French },
                new TblLabel2 { Name = "2F", LanguageCode = LanguageCode.French });

            await context.SaveChangesAsync();
        }

        using (var context = new SomeDbContext(LanguageCode.English))
        {
            foreach (var entity in context.Set<TblLabel1>())
            {
                Console.WriteLine(entity.Name);
            }
            foreach (var entity in context.Set<TblLabel2>())
            {
                Console.WriteLine(entity.Name);
            }
        }

        using (var context = new SomeDbContext(LanguageCode.French))
        {
            foreach (var entity in context.Set<TblLabel1>())
            {
                Console.WriteLine(entity.Name);
            }
            foreach (var entity in context.Set<TblLabel2>())
            {
                Console.WriteLine(entity.Name);
            }
        }
    }
}

[esersahin]

@ajcvickers It may be a small thing for you, but I am grateful your effort in my understanding of the subject. Your explanations have enlightened me like the sun. Thank you very much for your effort :)

I wanted to share the corrected version of the other code example I shared before, so that it can be useful to people.

@roji thank you very much for your interest.

I will follow your youtube broadcasts more closely, glad to have you :)

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.Extensions.Logging;

public class SomeDbContext : DbContext
{
    public LanguageCode _languageCode;

    public SomeDbContext(LanguageCode languageCode)
    {
        _languageCode = languageCode;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TblLabel1>();
        modelBuilder.Entity<TblLabel2>();
        
        //1.Method
        //Expression<Func<ILanguageCode, bool>> filterExpr = e => e.LanguageCode == _languageCode;
        //modelBuilder.AddLanguageCode(filterExpr);
        
        //2.Method
        modelBuilder.AddLanguageCode(this);
        
    }
}

public static class LanguageQueryExtension
{
    
    public static void AddLanguageCode(this ModelBuilder modelBuilder, Expression<Func<ILanguageCode, bool>> filterExpr)
    {

        foreach (var mutableEntityType in modelBuilder.Model.GetEntityTypes())
        {
            if (mutableEntityType.ClrType.IsAssignableTo(typeof(ILanguageCode)))
            {
                var parameter = Expression.Parameter(mutableEntityType.ClrType);
                var body = ReplacingExpressionVisitor.Replace(filterExpr.Parameters.First(), parameter, filterExpr.Body);
                var lambdaExpression = Expression.Lambda(body, parameter);

                mutableEntityType.SetQueryFilter(lambdaExpression);
            }
        }
    }    

    public static void AddLanguageCode(this ModelBuilder modelBuilder, SomeDbContext context)
    {
        Expression<Func<ILanguageCode, bool>> filterExpr = e => e.LanguageCode == context._languageCode;

        foreach (var mutableEntityType in modelBuilder.Model.GetEntityTypes())
        {
            if (mutableEntityType.ClrType.IsAssignableTo(typeof(ILanguageCode)))
            {
                var parameter = Expression.Parameter(mutableEntityType.ClrType);
                var body = ReplacingExpressionVisitor.Replace(filterExpr.Parameters.First(), parameter, filterExpr.Body);
                var lambdaExpression = Expression.Lambda(body, parameter);

                mutableEntityType.SetQueryFilter(lambdaExpression);
            }
        }
    }
}

public enum LanguageCode
{
    English = 0,
    French = 1
}

public interface ILanguageCode
{
    LanguageCode LanguageCode { get; set; }
}

public class TblLabel1 : ILanguageCode
{
    public int Id { get; set; }

    public string Name { get; set; }

    public LanguageCode LanguageCode { get; set; }
}

public class TblLabel2 : ILanguageCode
{
    public int Id { get; set; }

    public string Name { get; set; }

    public LanguageCode LanguageCode { get; set; }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new SomeDbContext(LanguageCode.English))
        {
            await context.Database.EnsureDeletedAsync();
            await context.Database.EnsureCreatedAsync();
        
            context.AddRange(
                new TblLabel1 { Name = "1E", LanguageCode = LanguageCode.English },
                new TblLabel2 { Name = "2E", LanguageCode = LanguageCode.English },
                new TblLabel1 { Name = "1F", LanguageCode = LanguageCode.French },
                new TblLabel2 { Name = "2F", LanguageCode = LanguageCode.French });
        
            await context.SaveChangesAsync();
        }

        using (var context = new SomeDbContext(LanguageCode.English))
        {
            foreach (var entity in context.Set<TblLabel1>())
            {
                Console.WriteLine(entity.Name);
            }
            foreach (var entity in context.Set<TblLabel2>())
            {
                Console.WriteLine(entity.Name);
            }
        }

        using (var context = new SomeDbContext(LanguageCode.French))
        {
            foreach (var entity in context.Set<TblLabel1>())
            {
                Console.WriteLine(entity.Name);
            }
            foreach (var entity in context.Set<TblLabel2>())
            {
                Console.WriteLine(entity.Name);
            }
        }
    }
}