FromSqlRaw throws Exception when querying all objects that contain a certain string property in a json array column #29355
Description
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