FromSqlRaw throws Exception when querying all objects that contain a certain string property in a json array column

[cschulzsuper]

I've got the following records in an SQL Server database table.

The following query will return element 1 aka List1, when I execute it directly in SQL Server. It returns all records that contain an item in the json array that has the property Text with the value Test1.

SELECT * FROM [Todos] c WHERE 'Test1' IN (SELECT JSON_VALUE(j.value,'$.Text') FROM OPENJSON(c.[Items],'$') j)

When I execute the same query in EF Core via FromSqlRaw, I get an exceptions. The exception differs depending on the daily build. The stack traces for those are attached below.

I don't rule out, that I want something from the raw SQL query that is just not possible in the moment. Or it needs to be done in a different way.

C# Example

using Microsoft.EntityFrameworkCore;
using System.Security.Cryptography.X509Certificates;

var context = new TodoContext();

context.Database.EnsureCreated();

var todoList1 = new TodoList
{
    Title = "List1",
    Items = new List<TodoItem>()
    {
        new TodoItem { Text = "Test1"}
    }
};

context.Add(todoList1);

var todoList2 = new TodoList
{
    Title = "List2",
    Items = new List<TodoItem>()
    {
        new TodoItem { Text = "Test2"}
    }
};

context.Add(todoList2);

context.SaveChanges();

var filter = "Test1";

var result = context.Todos
    .FromSqlRaw($"SELECT * FROM [Todos] c WHERE {filter} IN (SELECT JSON_VALUE(j.value,'$.Text') FROM OPENJSON(c.[Items],'$') j)")
    .AsEnumerable();

foreach(var x in result)
{
    Console.WriteLine( x.Title);
}

context.Dispose();

public class TodoList
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public IList<TodoItem> Items { get; set; } = new List<TodoItem>();
}

[Owned]
public class TodoItem
{
    public required string Text { get; set; }
}

public class TodoContext : DbContext
{
    public DbSet<TodoList> Todos { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder
            .UseSqlServer("SUPER_SECRET_ONLY_BOURNE_KNOWS");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<TodoList>()
            .OwnsMany(c => c.Items)
            .ToJson();
    }
}

Stack traces (7.0.0-rtm.22512.3)

System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.SharedTypeEntityExpandingExpressionVisitor.TryExpand(Expression source, MemberIdentity member)

Stack traces (8.0.0-alpha.1.22416.5)

System.ArgumentException: Expression of type 'TodoItem' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable`1[TodoItem]' of method 'System.Linq.IQueryable`1[TodoItem] AsQueryable[TodoItem](System.Collections.Generic.IEnumerable`1[TodoItem])' (Parameter 'arg0')
   at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
   at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.SharedTypeEntityExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.MaterializeCollectionNavigationExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.SharedTypeEntityExpandingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.IncludeExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.SharedTypeEntityExpandingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.SharedTypeEntityExpandingExpressionVisitor.Expand(SelectExpression selectExpression, Expression lambdaBody)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.ExpandSharedTypeEntities(SelectExpression selectExpression, Expression lambdaBody)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RemapLambdaBody(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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.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 Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at Program.<Main>$(String[] args) in C:\Users\Christian\Desktop\OwnsManyJson\OwnsManyJson\Program.cs:line 39

Include provider and version information

EF Core version: 7.0.0-rtm.22512.3 and 8.0.0-alpha.1.22416.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0 RC2
IDE: Visual Studio 2022 17.4.0 Preview 3.0

[maumar]

we don't create navigation bindings for the json entities in the SelectExpression ctor which takes TableExpressionBase as argument. We should add the same logic that we use in the "main" ctor for the SelectExpression