[SQL Server] Can't perform set operation over entity containing a complex JSON column

[ArnaudB88]

Bug description

Bug description

I am trying to union two entity lists of the same type and selecting some data (without consulting the complex entity type property), but it throws an exception:

The data type json cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

Why

As suggested in the EfCore 10 release notes, we are migrating from owned entities (saved as json) to complex entity types. This results in several problems, and the bug described here is one of them.
The described scenario worked fine with owned entities.

Scenario

We try to union two sets of entities of the same type, grouping and selecting a calculation. The complex entity type is not consulted in the query filter or projection.

Your code

//entities
public partial class Project
{
    public Guid Id { get; set; }
    public Guid? ParentProjectId { get; set; }

    [Ignore]
    public virtual Project ParentProject { get; set; }

    [Ignore]
    public virtual ICollection<Project> ParentProjects { get; set; }

    [Ignore]
    public virtual ICollection<ProjectOrganisatie> ProjectOrganisaties { get; set; }

    public IList<KolomMetadata> KolomMetadata { get; set; }
}
public partial class ProjectOrganisatie
{
    public Guid Id { get; set; }
    public Guid ProjectId { get; set; }
    public bool IsInactief { get; set; }
	
    [Ignore]
    public virtual Project Project { get; set; }
}

//Mappings
public partial class ProjectMap
    : IEntityTypeConfiguration<ClientName.ProjectName.Domain.Entities.Project>
{
    public void Configure(Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder<ClientName.ProjectName.Domain.Entities.Project> builder)
    {
        builder.ToTable("Project", "dbo");
        builder.HasKey(t => t.Id);
		
        builder.Property(t => t.ParentProjectId)
            .HasColumnName("ParentProjectId")
            .HasColumnType("uniqueidentifier");

        builder.HasOne(t => t.ParentProject)
            .WithMany(t => t.ParentProjects)
            .HasForeignKey(d => d.ParentProjectId)
            .HasConstraintName("FK_Project_Project");

        builder.ComplexCollection(t => t.KolomMetadata, builder => builder.ToJson().Configure());		
		//Note: the Configure() method sets the property names of the KolomMetadata to camelCase
	}
}
public partial class ProjectOrganisatieMap
    : IEntityTypeConfiguration<ClientName.ProjectName.Domain.Entities.ProjectOrganisatie>
{
    public void Configure(Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder<ClientName.ProjectName.Domain.Entities.ProjectOrganisatie> builder)
    {
        builder.ToTable("ProjectOrganisatie", "dbo");
        builder.HasKey(t => t.Id);
	
        builder.Property(t => t.ProjectId)
            .IsRequired()
            .HasColumnName("ProjectId")
            .HasColumnType("uniqueidentifier");

        builder.Property(t => t.IsInactief)
            .IsRequired()
            .HasColumnName("IsInactief")
            .HasColumnType("bit")
            .HasDefaultValue(false);

        builder.HasOne(t => t.Project)
            .WithMany(t => t.ProjectOrganisaties)
            .HasForeignKey(d => d.ProjectId)
            .HasConstraintName("FK_ProjectOrganisatie_Project");

        builder.ComplexCollection(t => t.KolomMetadata, builder => builder.ToJson().Configure());
	}
}

			
//Scenario
Guid projectId = Guid.NewGuid();
Expression<Func<T, bool>> filter = p => p.Id == projectId;
Expression<Func<T, TOfProperty>> projection = p => p.ProjectOrganisaties.Union(p.ParentProjects.SelectMany(p2 => p2.ProjectOrganisaties))
    .GroupBy(po => po.Project.ParentProjectId ?? po.ProjectId)
    .Select(g => new
    {
        AantalInactief = g.Count(po => po.IsInactief)
    })
    .FirstOrDefault();

var query = dbContext.Set<Project>.AsQueryable()
  .Where(filter)
  .AsNoTracking() //read only query
  .Select(projection);
  
var expression = query.Expression; //see below
var result = query.FirstOrDefaultAsync()(); //Throws exception


//Expression:
{[Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression].TagWith("Method: EntityFrameworkGenericRepositoryTests.FirstOrDefaultWithProjectionAsync_Should_Work_WithUnion() at line 61").Where(p => (p.Id == value(ClientName.ProjectName.Tests.RepositoryTests.EntityFrameworkGenericRepositoryTests+<>c__DisplayClass1_0).project.Id)).AsNoTracking().Select(p => p.ProjectOrganisaties.Union(p.ParentProjects.SelectMany(p2 => p2.ProjectOrganisaties)).GroupBy(po => (po.Project.ParentProjectId ?? po.ProjectId)).Select(g => new <>f__AnonymousType5`1(AantalInactief = g.Count(po => po.IsInactief))).FirstOrDefault())}
    Arguments: Count = 2
    CanReduce: false
    DebugView: ".Call System.Linq.Queryable.Select(\r\n    .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsNoTracking(.Call System.Linq.Queryable.Where(\r\n            .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.TagWith(\r\n                .Extension<Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression>,\r\n                \"Method: EntityFrameworkGenericRepositoryTests.FirstOrDefaultWithProjectionAsync_Should_Work_WithUnion() at line 61\")\r\n            ,\r\n            '(.Lambda #Lambda1<System.Func`2[ClientName.ProjectName.Domain.Entities.Project,System.Boolean]>))),\r\n    '(.Lambda #Lambda2<System.Func`2[ClientName.ProjectName.Domain.Entities.Project,<>f__AnonymousType5`1[System.Int32]]>))\r\n\r\n.Lambda #Lambda1<System.Func`2[ClientName.ProjectName.Domain.Entities.Project,System.Boolean]>(ClientName.ProjectName.Domain.Entities.Project $p)\r\n{\r\n    $p.Id == (.Constant<ClientName.ProjectName.Tests.RepositoryTests.EntityFrameworkGenericRepositoryTests+<>c__DisplayClass1_0>(Inte
rreg.ProjectName.Tests.RepositoryTests.Entit..."
    Method: {System.Linq.IQueryable`1[<>f__AnonymousType5`1[System.Int32]] Select[Project,<>f__AnonymousType5`1](System.Linq.IQueryable`1[ClientName.ProjectName.Domain.Entities.Project], System.Linq.Expressions.Expression`1[System.Func`2[ClientName.ProjectName.Domain.Entities.Project,<>f__AnonymousType5`1[System.Int32]]])}
    NodeType: Call
    Object: null
    Type: {Name = "IQueryable`1" FullName = "System.Linq.IQueryable`1[[<>f__AnonymousType5`1[[System.Int32, System.Private.CoreLib, Version=10.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], ClientName.ProjectName.Tests, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]"}

Stack traces

Microsoft.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=The data type json cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
  Source=Core Microsoft SqlClient Data Provider
  StackTrace:
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__195_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteReaderAsync>d__22.MoveNext()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteReaderAsync>d__22.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.<InitializeReaderAsync>d__21.MoveNext()
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.<MoveNextAsync>d__20.MoveNext()
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.<SingleOrDefaultAsync>d__16`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.<SingleOrDefaultAsync>d__16`1.MoveNext()
   at ClientName.ProjectName.Tests.RepositoryTests.EntityFrameworkGenericRepositoryTests.<FirstOrDefaultWithProjectionAsync_Should_Work_WithUnion>d__1.MoveNext() in C:\Projects\ClientName\ProjectName\ClientName.ProjectName.Tests\RepositoryTests\EntityFrameworkGenericRepositoryTests.cs:line 61
   at Microsoft.VisualStudio.TestPlatform.MSTest.TestAdapter.Execution.TestMethodInfo.<ExecuteInternalAsync>d__55.MoveNext()

Verbose output

EF Core version

10.0.3

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 10

Operating system

Windows 11

IDE

Visual Studio 2026 18.2.1

[ArnaudB88]

Link to #37290 since this was working with owned entity types, but not with complex entity types.

[roji]

@ArnaudB88 the problem here isn't with complex types (or complex vs. owned). SQL Server really does not support set operations over the new json type; it also does not support equality (but EF converts to text to allow this). I'm guessing that when switching to complex types you also changed from the older practice of storing JSON data in nvarchar(max) to using the new json type; while the latter is far better in many ways, it also (currently) doesn't support set operations.

So you have a choice: you can configure the column to be nvarchar(max) and set operations will work (but you'll miss out on the advantages of the new json type), or you can switch to the new json type and work around the lack of set operation support (e.g. by doing Union on the client). I'd also recommend reaching out to SQL Server to let them know that you need set operations over json. One last alternative that might work depending on your scenario, is to select out properties, excluding the JSON complex property.

I'm going to go ahead and close this as something that's unrelated to EF, but post back if you have any further questions/want to continue the conversation.

[ArnaudB88]

@roji Thanks for your fast answer.

I'm guessing that when switching to complex types you also changed from the older practice of storing JSON data in nvarchar(max) to using the new json type; while the latter is far better in many ways, it also (currently) doesn't support set operations.

Correct, we switched to the json type first. Then we run all unit tests while keeping or owned entity types (which succeeded).
Currently, we are trying to move to complex entity types.

the problem here isn't with complex types (or complex vs. owned)

If it's not complex type related, how come the above code succeeds with owned entity types (and SQL type JSON)?
(fyi the expression that I get from the above query is identical for both complex and owned entity types.)

I see that the original sql query generated with owned entity types does not contain the complex entity property KolomMetadata.
Is it possible that the query generated with complex entity types does contain the complex property?
And if so, could it be possible to exclude complex properties (of the JSON type) when using a union statement?

One last alternative that might work depending on your scenario, is to select out properties, excluding the JSON complex property.

This is indeed a workaround. So it confirms my thought above.
The downside with selecting properties before a union is that the generated sql is different and contains more joins (each part of the union statement contains all joins needed for the select statement). This results in a worse performant query.
(this worse performance is not applicable on the simplified example from above, but on a far more complex query in our code with more joined tables)

[roji]

I see that the original sql query generated with owned entity types does not contain the complex entity property KolomMetadata.

I need to see a bit more of both the code and the actual SQL getting generated - this seems to be going beyond what you posted above. Ideally just post a minimal runnable console progarm, rather than snippets.

But in any case, if you're doing a set operation (e.g. Union) over an entity type, and that entity type has an owned JSON column that isn't getting included in the SQL query, I'm pretty sure we'd consider that a bug in the owned JSON implementation. @AndriySvyryd would you agree?

In theory, rather than including the JSON column itself, we could recursively extra all the scalar properties out of it with JSON_VALUE() invocations. However, that's likely to be quite heavy/inefficient too - I'm not sure I see us doing this.

This is all an unfortunate limitation of SQL Server itself; PostgreSQL, for example, does support this:

SELECT * FROM (VALUES (1, '{ "foo": 8 }'::jsonb))
UNION
SELECT * FROM (VALUES (1, '{ "foo": 9 }'::jsonb), (1, '{ "foo": 9 }'::jsonb));

I'm not sure what else to suggest here - but definitely signal this to SQL Server themselves so they fix it. The json type is new, and there's a good chance it'll get better.

[ArnaudB88]

I need to see a bit more of both the code and the actual SQL getting generated - this seems to be going beyond what you posted above. Ideally just post a minimal runnable console progarm, rather than snippets.

@roji See https://github.com/ArnaudB88/EfCore37726

But in any case, if you're doing a set operation (e.g. Union) over an entity type, and that entity type has an owned JSON column that isn't getting included in the SQL query, I'm pretty sure we'd consider that a bug in the owned JSON implementation

This is currently the case, see VS output window when debugging.

In theory, rather than including the JSON column itself, we could recursively extra all the scalar properties out of it with JSON_VALUE() invocations.

I would suggest automatically excluding complex properties (of JSON type) if collections of entities are joined like
p.ProjectOrganisaties.Union(p.ParentProjects.SelectMany(p2 => p2.ProjectOrganisaties))

If someone needs to join on complex properties, they could use select statements to join.
This way, it's equal to the behavior of owned entities and works out of the box for SQL.

[roji]

I would suggest automatically excluding complex properties (of JSON type) if collections of entities are joined like
p.ProjectOrganisaties.Union(p.ParentProjects.SelectMany(p2 => p2.ProjectOrganisaties))

That does not match the general semantics we have for complex properties (and what I believe we should also have for owned entities). If you compare two Customers that have a complex Address type, it seems pretty clear that the expectation is to also compare their Addresses, and not to leave those out; the fact that a property happens to be nested within a complex type shouldn't mean that it's suddenly ignored for the purpose of equality (or set operations, which also imply equality). It's unfortunate that SQL Server has a limitation here, but I'm very hesitant to change semantics in this way because of that.

I'll reopen to discuss with @AndriySvyryd.