.net-coreentity-framework-coreexpressionopen-jsonef-core-8.0

EF Core 8 - translate queries into embedded collections (OPENJSON) using expression


Here is an example of EF Core 8 translating LINQ to embedded collections:

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#translate-queries-into-embedded-collections

And it works just fine:

public class Person 
{
    public string Name { get; set; } = null!;
    public string Address { get; set; } = null!;
}
DbSet<Person> db;

var searchTerms = new[] { "Search #1", "Search #2", "Search #3"};

var linqQuery = db.Where(a => searchTerms.Contains(a.Address)).ToQueryString();

// DECLARE @__searchTerms_0 nvarchar(4000) = N'["Search #1","Search #2","Search #3"]';

// SELECT
//    [a].[Name], [a].[Address]
// FROM
//    [PERSON] AS[a]
// WHERE
//    [a].[Address] IN(
//    SELECT [s].[value]
//    FROM OPENJSON(@__searchTerms_0) WITH([value] varchar(8000) '$') AS[s]
// )

But I can't achieve the same result using an expression.

Here is what I try and expect to get OPENJSON:

DbSet<Person> db;

var searchTerms = new[] { "Search #1", "Search #2", "Search #3"};

var propInfo = typeof(Person).GetProperty(nameof(Person.Address))!;
var parameterExp = Expression.Parameter(typeof(Person), "a");

var method = typeof(Enumerable)
    .GetMethods()
    .First(n => n.Name == "Contains" && n.GetParameters().Length == 2);

var genericMethod = method.MakeGenericMethod(typeof(string));

var containsExp = Expression.Call(
   genericMethod, 
   Expression.Constant(searchTerms, typeof(IEnumerable<string>)), 
   Expression.Property(parameterExp, propInfo));

var predicate = Expression.Lambda<Func<Person, bool>>(containsExp, parameterExp);

var expressionQuery = db.Where(predicate).ToQueryString();

// SELECT
//    SELECT[a].[Name], [a].[Address]
// FROM
//    [PERSON] AS[a]
// WHERE
//    [a].[Address] IN(
//        'Search #1',
//        'Search #2',
//        'Search #3'
//    )

Solution

  • It is because in first variant you have used closure over searchTerms array - it is what C# compiler do, and in second variant you have used searchTerms array directly in expression tree as constant.

    For constants EF Core just decided to generate static query with IN, because LINQ Translator makes decision that such query will be never changed.

    We can mimic such case by defining fake holder class:

    class ClosureHolder
    {
        public IEnumerable<string> Value { get; set; }
    }
    
    DbSet<Person> db;
    
    var searchTerms = new[] { "Search #1", "Search #2", "Search #3"};
    
    var propInfo = typeof(Person).GetProperty(nameof(Person.Address))!;
    var parameterExp = Expression.Parameter(typeof(Person), "a");
    
    var method = typeof(Enumerable)
        .GetMethods()
        .First(n => n.Name == "Contains" && n.GetParameters().Length == 2);
    
    var genericMethod = method.MakeGenericMethod(typeof(string));
    
    // instead of generating just constant, we generate MemberExpression to constant
    var holderExpr = Expression.Constant(new ClosureHolder { Value = searchTerms });
    var searchTermsExpr = Expression.Property(holderExpr, nameof(ClosureHolder.Value));
    
    var containsExp = Expression.Call(
       genericMethod, 
       searchTermsExpr, 
       Expression.Property(parameterExp, propInfo));
    
    var predicate = Expression.Lambda<Func<Person, bool>>(containsExp, parameterExp);
    
    var expressionQuery = db.Where(predicate).ToQueryString();