Query: Null TypeMapping in Sql Tree

[kswoll]

When using .Select to project into custom types you can run into this error when using the conditional operator. I've included a SSCE below, but the gist is this code here:

db.FirstEntities
    .Select(x => new FirstEntityModel
    {
        Id = x.Id,
        ThirdEntityId = x.ThirdEntity == null ? null : (int?)x.ThirdEntity.Id,
        SecondEntity = x.SecondEntity == null ? null : new SecondEntityModel()
    })
    .ToArray();

Interestingly, if I remove either the ThirdEntityId assignment or the SecondEntity assignment, there are no problems.

The full exception is:

System.InvalidOperationException
  HResult=0x80131509
  Message=The binary operator NotEqual is not defined for the types 'Microsoft.EntityFrameworkCore.Storage.ValueBuffer' and 'Microsoft.EntityFrameworkCore.Storage.ValueBuffer'.
  Source=System.Linq.Expressions
  StackTrace:
   at System.Linq.Expressions.Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)
   at System.Linq.Expressions.Expression.NotEqual(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
   at System.Linq.Expressions.Expression.NotEqual(Expression left, Expression right)
   at Microsoft.EntityFrameworkCore.Query.Expressions.Internal.NullConditionalExpression.Reduce()
   at System.Linq.Expressions.Expression.ReduceAndCheck()
   at System.Linq.Expressions.Expression.ReduceExtensions()
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExtensionExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.MemberAssignmentRewriter..ctor(MemberAssignment binding, StackSpiller spiller, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.BindingRewriter.Create(MemberBinding binding, StackSpiller spiller, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMemberInitExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   at System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
   at System.Linq.Expressions.LambdaExpression.Compile()
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.Create(Shaper originalShaper, LambdaExpression materializer)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
   at Remotion.Linq.Clauses.SelectClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database, IDiagnosticsLogger`1 logger, Type contextType)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass13_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   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 Remotion.Linq.QueryableBase`1.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at ConsoleApp2.Program.Main(String[] args) in C:\Users\Kirk\Documents\Visual Studio 2017\Projects\ConsoleApp2\ConsoleApp2\Program.cs:line 20

The full repro is to create a new .NET Core Console program, add Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.Sqlite and make this your main Program.cs file:

using System;
using System.Linq;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
            var connection = new SqliteConnection("DataSource=:memory:");
            connection.Open();
            var options = new DbContextOptionsBuilder<TestDb>()
                .UseSqlite(connection)
                .Options;
            var db = new TestDb(options);
            db.Database.EnsureCreated();

            db.FirstEntities
                .Select(x => new FirstEntityModel
                {
                    Id = x.Id,
                    ThirdEntityId = x.ThirdEntity == null ? null : (int?)x.ThirdEntity.Id,
                    SecondEntity = x.SecondEntity == null ? null : new SecondEntityModel()
                })
                .ToArray();
        }
    }

    public class TestDb : DbContext
    {
        public DbSet<FirstEntity> FirstEntities { get; set; }
        public DbSet<SecondEntity> SecondEntities { get; set; }
        public DbSet<ThirdEntity> ThirdEntities { get; set; }

        public TestDb(DbContextOptions options) : base(options)
        {
        }
    }

    public class FirstEntity
    {
        public int Id { get; set; }
        public int? OtherId { get; set; }
        public int? SecondEntityId { get; set; }

        public ThirdEntity ThirdEntity { get; set; }
        public SecondEntity SecondEntity { get; set; }
    }

    public class SecondEntity
    {
        public int Id { get; set; }
        public int? OtherId { get; set; }
    }

    public class ThirdEntity
    {
        public int Id { get; set; }
        public int? FirstEntityId { get; set; }

        public FirstEntity FirstEntity { get; set; }
    }

    public class FirstEntityModel
    {
        public int Id { get; set; }
        public int? ThirdEntityId { get; set; }
        public SecondEntityModel SecondEntity { get; set; }
    }

    public class SecondEntityModel
    {
        public int Id { get; set; }
    }
}

Further technical details

EF Core version: 2.1.4
EF Core Sqlite version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10
IDE: Visual Studio 2017 15.8.8

[maumar]

dupe of #12788

[kswoll]

Sorry for the dup! :)

[kswoll]

For what it's worth, if I create a "model" type to wrap the id the problem goes away:

ThirdEntityId = x.ThirdEntity == null ? null : (int?)x.ThirdEntity.Id,

To:

ThirdEntity = x.ThirdEntity == null ? null : new ThirdEntityModel { Id = x.ThirdEntity.Id },

[divega]

Sorry for the dup! :)

No worries @kswoll. We decided to use your issue to track this. Thanks for posting the workarounds.

[optiks]

Hmm, the workaround is kinda messy. But it seems every other workaround gets optimised out. e.g.

• new { Year = b.Year as int? }.Year
• new Workaround13860<int?> { Value = b.Year as int? }.Value

This means we need to project into the wrapper Workaround13860<T>, then project out again.

private class Workaround13860<T>
{
   public T Value { get; set; }
}

[smitpatel]

Throws in 3.1

Unhandled exception. System.InvalidOperationException: Null TypeMapping in Sql Tree
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.SqlTypeMappingVerifyingExpressionVisitor.VisitExtension(Expression node)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   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 System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at EFSampleApp.Program.Main(String[] args) in D:\code\EFSampleApp\EFSampleApp\Program.cs:line 29
   at EFSampleApp.Program.<Main>(String[] args)

[robertsabo0]

My problem is this part of query:
query = query.Where(x => x.iwsVarId.ToString().Contains(variableFilterDto.likeParam));
Where iwsVarId is int.
This was not problem on EntityFrameworkCore 3.1.3.
By updateing to 3.1.4 it fails.

[CliffordSpainhour]

I'm having a similar problem, except the work around does not work for my case.
I am using EF Core 2.2.6 and cannot upgrade at the moment because of time constraints

If I reduce the size of my projection the query works, but does not work with the full projection.

Here is the smaller version of the projection:

var result = await context.SiteProducts.Where(x => x.Id == siteProductId).Select(p => new SiteProductModel
            {
                SourceUrls = p.SourceUrls.Select(e =>
                  new ProductSourceUrlModel
                  {
                      Id = e.Id,
                      Url = e.Value,
                      Price = e.Price != null ? new ProductPriceModel
                      {
                          Id = e.Price.SiteProductSourceUrlId,
                          AmountMax = e.Price.AmountMax,
                          AmountMin = e.Price.AmountMin,
                          Currency = e.Price.Currency,
                          IsSale = e.Price.IsSale,
                          LastSeen = e.Price.LastSeen,
                          Merchant = e.Price.Merchant,
                          SourceUrl = e.Value,
                          Availability = e.Price.Availability
                      } : null,

                  }),
            }).FirstOrDefaultAsync();

The issue arises because of the comparison Price = e.Price != null ? ...
Price is allowed to be null, so that is why I do the null check.
I've tried wrapping Price in a wrapper class, but it doesn't seem to work.

I define the relationship with SiteProductSourceUrl and SiteProductdPrice as such:

modelBuilder.Entity<SiteProductPriceEntity>(entity =>
            {                
                entity.Property(e => e.AmountMax).HasColumnType("decimal(19, 4)");
                entity.Property(e => e.AmountMin).HasColumnType("decimal(19, 4)");

                entity.HasOne(e => e.PrimarySourceUrl)
                .WithOne(e => e.Price)
                .HasForeignKey<SiteProductPriceEntity>(e => e.SiteProductSourceUrlId)
                .OnDelete(DeleteBehavior.Cascade);
            });

It's a one to one relationship with price being the dependent entity and sourceurl the principle.
I do not have a foreign key to price from the sourceUrl entity, otherwise I would use that in the comparison check instead of the Price reference itself.

Here are the relevant models:

public class SiteProductPriceEntity
    {
        public SiteProductPriceEntity()
        {
        }

        public int Id { get; set; }
        public decimal AmountMax { get; set; }
        public decimal AmountMin { get; set; }
        public string Currency { get; set; }
        public bool IsSale { get; set; }
        public DateTime LastSeen { get; set; }
        public string SourceUrls { get; set; }
        public string Merchant { get; set; }
        public string Availability { get; set; }
        public int SiteProductSourceUrlId { get; set; }
        
        public virtual SiteProductSourceUrlEntity PrimarySourceUrl { get; set; }
    }

public class SiteProductSourceUrlEntity
    {
        public int Id { get; set; }
        public string Value { get; set; }
        
        public virtual SiteProductPriceEntity Price { get; set; }
        public int? VariationId { get; set; }
        public virtual SiteProductVariationEntity Variation { get; set; }
        public int SiteProductId { get; set; }
        public virtual SiteProductEntity SiteProduct { get; set; }
    }

Below is the full stack trace.

Message: 
    System.InvalidOperationException : The binary operator NotEqual is not defined for the types 'Microsoft.EntityFrameworkCore.Storage.ValueBuffer' and 'Microsoft.EntityFrameworkCore.Storage.ValueBuffer'.
  Stack Trace: 
    Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)
    Expression.NotEqual(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
    NullConditionalExpression.Reduce()
    Expression.ReduceAndCheck()
    Expression.ReduceExtensions()
    StackSpiller.RewriteExtensionExpression(Expression expr, Stack stack)
    StackSpiller.RewriteExpression(Expression node, Stack stack)
    MemberAssignmentRewriter.ctor(MemberAssignment binding, StackSpiller spiller, Stack stack)
    BindingRewriter.Create(MemberBinding binding, StackSpiller spiller, Stack stack)
    StackSpiller.RewriteMemberInitExpression(Expression expr, Stack stack)
    StackSpiller.RewriteExpression(Expression node, Stack stack)
    ChildRewriter.Add(Expression expression)
    ChildRewriter.AddArguments(IArgumentProvider expressions)
    StackSpiller.RewriteNewExpression(Expression expr, Stack stack)
    StackSpiller.RewriteExpression(Expression node, Stack stack)
    StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
    StackSpiller.Rewrite[T](Expression`1 lambda)
    Expression`1.Accept(StackSpiller spiller)
    LambdaCompiler.Compile(LambdaExpression lambda)
    ProjectionShaper.Create(Shaper originalShaper, LambdaExpression materializer, Boolean storeMaterializerExpression)
    RelationalQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
    QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
    EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
    RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
    RelationalEntityQueryableExpressionVisitor.VisitSubQuery(SubQueryExpression expression)
    ExpressionVisitor.Visit(Expression node)
    ExpressionVisitorBase.VisitLambda[T](Expression`1 node)
    Expression`1.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    ExpressionVisitor.VisitUnary(UnaryExpression node)
    UnaryExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
    ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
    ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
    MethodCallExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    ExpressionVisitor.VisitMemberAssignment(MemberAssignment node)
    ExpressionVisitor.VisitMemberBinding(MemberBinding node)
    ExpressionVisitor.Visit[T](ReadOnlyCollection`1 nodes, Func`2 elementVisitor)
    ExpressionVisitor.VisitMemberInit(MemberInitExpression node)
    MemberInitExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    ExpressionVisitor.VisitMemberAssignment(MemberAssignment node)
    ExpressionVisitor.VisitMemberBinding(MemberBinding node)
    ExpressionVisitor.Visit[T](ReadOnlyCollection`1 nodes, Func`2 elementVisitor)
    ExpressionVisitor.VisitMemberInit(MemberInitExpression node)
    MemberInitExpression.Accept(ExpressionVisitor visitor)
    ExpressionVisitor.Visit(Expression node)
    EntityQueryModelVisitor.ReplaceClauseReferences(Expression expression, IQuerySource querySource, Boolean inProjection)
    EntityQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
    RelationalQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
    QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
    EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
    RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
    EntityQueryModelVisitor.CreateAsyncQueryExecutor[TResult](QueryModel queryModel)
    CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
    QueryCompiler.ExecuteAsync[TResult](Expression query)
    IAsyncEnumerable<TResult>.GetEnumerator()
    AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken) line 118
    SiteProductSourceUrlShould.GetFullProductShould()

[CliffordSpainhour]

I found a work around, but I don't necessary like the solution. I'm not sure how this will affect performance.

In the Source Url Entity Class, I now always initialize the price as an empty POCO

    public class SiteProductSourceUrlEntity
    {
        public SiteProductSourceUrlEntity()
        {
            Price = new SiteProductPriceEntity();
        }

        public int Id { get; set; }
        public string Value { get; set; }
        
        public virtual SiteProductPriceEntity Price { get; set; }
        public int? VariationId { get; set; }
        public virtual SiteProductVariationEntity Variation { get; set; }
        public int SiteProductId { get; set; }
        public virtual SiteProductEntity SiteProduct { get; set; }
    }

I then changed the query to check with the price id instead of the price reference

var result = await context.SiteProducts.Where(x => x.Id == siteProductId).Select(p => new SiteProductModel
            {
                SourceUrls = p.SourceUrls.Select(e =>
                  new ProductSourceUrlModel
                  {
                      Id = e.Id,
                      Url = e.Value,
                      Price = e.Price.Id > 0 ? new ProductPriceModel
                      {
                          Id = e.Price.SiteProductSourceUrlId,
                          AmountMax = e.Price.AmountMax,
                          AmountMin = e.Price.AmountMin,
                          Currency = e.Price.Currency,
                          IsSale = e.Price.IsSale,
                          LastSeen = e.Price.LastSeen,
                          Merchant = e.Price.Merchant,
                          SourceUrl = e.Value,
                          Availability = e.Price.Availability
                      } : null,

                  }),
            }).FirstOrDefaultAsync();

Although this works, doing it this way has other negative affects. For example, I have to set any empty prices back to null in other queries using eager loading, otherwise it will try to save an empty price when persisting.