Unable to query entity after adding Json Column (EF Core 8)

Question

We're extending out some of our database models by leveraging new json features released with entityframework core 7, however after adding the property, we're no longer able to retrieve data without it throwing the following:

    System.ArgumentException: Expression of type 'System.Object' cannot be used for assignment to type 'System.String'
   at System.Linq.Expressions.Expression.Assign(Expression left, Expression right)
   at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method, LambdaExpression conversion)
   at System.Linq.Expressions.BinaryExpression.Update(Expression left, LambdaExpression conversion, Expression right)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitSwitchCase(SwitchCase node)
   at System.Linq.Expressions.ExpressionVisitor.Visit[T](ReadOnlyCollection`1 nodes, Func`2 elementVisitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitSwitch(SwitchExpression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitLoop(LoopExpression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitConditional(ConditionalExpression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
   at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessShaper(Expression shaperExpression, RelationalCommandCache& relationalCommandCache, IReadOnlyList`1& readerColumns, LambdaExpression& relatedDataLoaders, Int32& collectionId)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQuery(ShapedQueryExpression shapedQueryExpression)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
   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.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)

Setup in dbContext:

builder.Entity<Order>(eb =>
 {
     eb.OwnsOne(
         o => o.MetricData,
         b =>
         {
             b.ToJson();

             b.OwnsMany(metric => metric.AdditionalData);
         });
 });

EF Migration:

migrationBuilder.AddColumn<string>(
      name: "MetricData",
      table: "Orders",
      type: "nvarchar(max)",
      nullable: true);

Query being formed:

var qs = context.Orders
.Where(x =>
   x.OrderId == request.OrderId &&
   x.MetricData == null
).ToQueryString();

Same result received if

context.Orders
.Where(x =>
   x.OrderId == request.OrderId //&&
   //x.MetricData == null
)

Cannot get to the bottom of what isn't working here....

May I ask why you chose to compromise your database design by adding a JSON column instead of properly normalizing your schema? — Dai, Commented Jul 8 at 14:20
b.ToJson(); <-- Please explain what's going on here. ToJson is not a member of OwnedNavigationBuilder - whereas it looks like it's a custom extension-method in yourt codebase? — Dai, Commented Jul 8 at 14:23
You are aware that having an nvarchar(max) column in your table will wreck your SQL execution plan's memory usage, right? — Dai, Commented Jul 8 at 14:24
@Dai there are alot of questions here outside of the question itself. My database is normalised, however there are benefits to my implementation of flattening overly complex nested structures which actually kill performance in data volumes I'm working with. In terms of what is going on here, ToJson is an extension method which is part of entityframeworkcore 7 onward and it's documented usage has been followed, hence this question. — Jon Selby, Commented Jul 8 at 15:26

Jon Selby · Accepted Answer · 2024-07-09 06:49:21Z

So the exception here was not at all helpful, and I've no idea as to the why behind it, but in my metric data class, I'd new'd up primitive collections of strings, which is generally a habit of mine on all collection properties, primitive or not. For some reason EF implementation did not like this.

public List<string> Collection1 {get;set;} = new ();
public List<string> Collection2 {get;set;} = new ();
public List<string> Collection3 {get;set;} = new ();

Removing the new instantiation resolved the issue immediately.

public List<string> Collection1 {get;set;}
public List<string> Collection2 {get;set;}
public List<string> Collection3 {get;set;}

I hope someone finds this useful, as the exception did not at all yield much.

Collectives™ on Stack Overflow

Unable to query entity after adding Json Column (EF Core 8)

1 Answer 1

Not the answer you're looking for? Browse other questions tagged
c#
entity-framework-core
.net-8.0
or ask your own question.

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Not the answer you're looking for? Browse other questions tagged c#entity-framework-core.net-8.0 or ask your own question.

Linked

Related

Not the answer you're looking for? Browse other questions tagged
c#
entity-framework-core
.net-8.0
or ask your own question.