Expression in the SQL tree does not have a type mapping assigned

[edamascus]

The following query was working fine in EF Core 7:

string[] values = new[] {"one", "two", "three", "four"};

var query = (from item in _context.items
             let value = values[item.value]
             select new {item.id, value}).ToList();

However, after upgrading to EF Core 8, the following exception is thrown:

System.InvalidOperationException: Expression '' in the SQL tree does not have a type mapping assigned.

I checked the breaking changes documentation and I am running SQL Server 2022 with compatibility level set for my database to 160.

What am I missing here?

[edamascus]

Converting the value retrieved from the values array to a string solved the issue and the SQL was translated properly:

string[] values = new[] {"one", "two", "three", "four"};

var query = (from item in _context.items
             let value = values[item.value].ToString()
             select new {item.id, value}).ToList();

I am not sure, it seems that EF Core -at the moment- isn't able to figure out the type of the values array during translation?

[maumar]

I can't reproduce the problem on my end. I'm using the following code:

using var ctx = new MyContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();
await ctx.Set<Entity>().AddAsync(new Entity { Value = 0 });
await ctx.Set<Entity>().AddAsync(new Entity { Value = 1 });
await ctx.SaveChangesAsync();

string[] values = new[] { "one", "two", "three", "four" };

var query = (from item in ctx.Set<Entity>()
             let value = values[item.Value]
             select new { item.Id, value }).ToList();

Console.WriteLine(query);


public class Entity
{
    public int Id { get; set; }
    public int Value { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;MultipleActiveResultSets=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Entity>();
    }
}

I get expected results and the sql is as follows:

exec sp_executesql N'SELECT [e].[Id], JSON_VALUE(@__values_0, ''$['' + CAST([e].[Value] AS nvarchar(max)) + '']'') AS [value]
FROM [Entity] AS [e]',N'@__values_0 nvarchar(4000)',@__values_0=N'["one","two","three","four"]'

@edamascus can you modify the code I pasted above, so that it reproduces the problem you are seeing? Also, are you using the latest patch of EF 8?

[edamascus]

@maumar, the current version of EF Core I am using is 8.0.0. I haven't updated to 8.0.4, as I checked the latest release notes and did not find anything directly related to this matter.

[edamascus]

@maumar, I had to simplify the actual code which I am having trouble with in order to report the issue. When declaring the value variable I am doing a null check to provide a default value when the value is null. Can you please try the following to see if the issue is reproduced (the change is in the following line let value = item.Id != 0 ? values[item.Value] : "zero"):

using var ctx = new MyContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();
await ctx.Set<Entity>().AddAsync(new Entity { Value = 0 });
await ctx.Set<Entity>().AddAsync(new Entity { Value = 1 });
await ctx.SaveChangesAsync();

string[] values = new[] { "one", "two", "three", "four" };

var query = (from item in ctx.Set<Entity>()
             let value = item.Id != 0 ? values[item.Value] : "zero"
             select new { item.Id, value }).ToList();

Console.WriteLine(query);

public class Entity
{
    public int Id { get; set; }
    public int Value { get; set; }
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro;Trusted_Connection=True;MultipleActiveResultSets=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Entity>();
    }
}

[roji]

@edamascus there have been many fixes that could be related to this, please try with the latest patch version. In fact, that's a good idea to always do before filing an issue.

[edamascus]

@roji, my fault for missing out the important part which caused the issue. The problem exists in versions 8.0.0 and 8.0.4 with the following query (which includes the ternary operator):

string[] values = new[] {"one", "two", "three", "four"};

var query = (from item in _context.items
             let value = item.value != null ? values[item.value] : ""
             select new {item.id, value}).ToList();

The following query (with string conversion) works:

string[] values = new[] {"one", "two", "three", "four"};

var query = (from item in _context.items
             let value = item.value != null ? values[item.value].ToString() : ""
             select new {item.id, value}).ToList();

[edamascus]

Not sure if this is related. But the same code fails with the following exception when item.value is actually null (it works well when it is not null):

Microsoft.Data.SqlClient.SqlException (0x80131904): Argument data type NULL is invalid for argument 2 of JSON_VALUE function.

In order to fix the issue, I have to re-evaluate item.value when accessing the array's item by index:

string[] values = new[] {"one", "two", "three", "four"};

var query = (from item in _context.items
             let value = item.value != null ? values[item.value != null ? item.value : 0].ToString() : ""
             select new {item.id, value}).ToList();

[maumar]

I'm able to reproduce this on current bits. Problem is that SqlExpressionFactory.Case applies TypeMapping for elseResult based on type mappings before we run type inference code - we use provided type mapping and the type mappings from When clauses, but they are all null.