Efcore: Improve exception message for projection with JSON field conversion

[attiqeurrehman]

Hi,

I am facing this issue when migrating from .NET Core 2.2 to .NET Core 3.1:

Steps to reproduce

``` C#
public class Dashboard
{
public Dashboard()
{
Widgets = new HashSet();
Layouts = new List();
}
public string Name { get; set; }
public List Layouts { get; set; }
public virtual ICollection Widgets { get; set; }
}

public class Layout
{
public int Width { get; set; }
public int Height { get; set; }
// removed other feilds for clarity
}

//Entity Configuration
builder.Property(d => d.Name)
.IsRequired();
builder.Property(w => w.Layouts).HasConversion(
v => JsonConvert.SerializeObject(v,
new JsonSerializerSettings {NullValueHandling = NullValueHandling.Ignore}),
v => JsonConvert.DeserializeObject>(v,
new JsonSerializerSettings {NullValueHandling = NullValueHandling.Ignore}));

Exception is thrown when following query is executed: 

```C#
var result = await _context.Dashboards.AsNoTracking().Select(d => new DashboardModel
                {
                    Id = d.Id,
                    Name = d.Name,
                    Layouts = d.Layouts.Select(l => new LayoutDto {H = l.Height, W = l.Width}).ToList()
                }).ToListAsync(cancellationToken);

It works fine if don't use Select or Select without Layouts.

Got the following exception:

System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.AddCollectionProjection(ShapedQueryExpression shapedQueryExpression, INavigation navigation, Type elementType)
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.CreateQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryTResult

Further technical details

EF Core version: 3.1.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows
IDE: Visual Studio 2019 16.6.3

[ajcvickers]

Note from triage: this is a limitation in translating the Layouts projection lambda. You can revert to the 2.2 behavior by explicitly performing this on the client. For example:

C# var result = await _context.Dashboards.AsNoTracking().AsEnumerable().Select(d => new DashboardModel { Id = d.Id, Name = d.Name, Layouts = d.Layouts.Select(l => new LayoutDto {H = l.Height, W = l.Width}).ToList() }).ToListAsync(cancellationToken);

[attiqeurrehman]

@ajcvickers it works but can you point to some document about it, for my understanding regarding the issue.

Thanks,
Attiqe

[roji]

@attiqeurrehman the last Select in your query is being client-evaluated, and there are some limitations on what can be done in there (though we should definitely improve the exception). Placing AsEnumerable before that Select makes it get evaluated by LINQ to Objects, i.e. in memory and fully outside of EF Core.

As a small note, you may still want to instruct EF Core to project out only the subset of fields you're interested in:

c# var result = await _context.Dashboards.AsNoTracking() .Select(d => new { d.Id, d.Name, d.Layouts }) .AsEnumerable() .Select(d => new DashboardModel { Id = d.Id, Name = d.Name, Layouts = d.Layouts.Select(l => new LayoutDto {H = l.Height, W = l.Width}).ToList() }) .ToListAsync(cancellationToken);

The first Select is seen by EF Core and controls what is projected out of the database, while the second Select projects in memory.

[attiqeurrehman]

Nice thank you for the explanation.

[smitpatel]

Closely related with #18179

[smitpatel]

The task here to improve exception message.

[GordonBlahut]

Just ran into this with JSON converted fields after finally upgrading to 3.1.

The workarounds to pull either the entire entity or a specific subset of properties are incredibly cumbersome. What used to be a relatively clean mapping from Entity to DTO, especially when using something like AutoMapper projections, now requires specific case-by-case intermediary steps. It's especially messy when the mapping relies on navigation properties because Include()s have to be introduced before switching to client evaluation, bloating the response returned from the database and largely defeating the benefit of a projection in the first place.

Interestingly EFCore will allow a complex JSON field to be mapped/projected to other class(es) if there are no collections at any level. It's only when a collection is involved at any level does it collapse. Since the actual SQL query should be identical in either case, I don't understand why both cases couldn't be supported.