Efcore: Union fails after update to 3.0

[remiskaune]

Union from different tables selecting to class throw error " Set operations over different store types are currently unsupported". It was working in EF core 2.2

I have select

           var q = query
                .Select(x => new ListItem
                {
                    Id = x.Id,
                    SkiepasId = x.SkiepasId,
                    PlanuojamaData = x.Skiepas.SuplanuotaData,
                    PaskirtaData = x.Skiepas.PaskyrimoData,
                    AtlikimoData = x.Skiepas.AtlikimoData,
                    Pacientas = new Pacientas
                    {
                        Vardas = x.Skiepas.Asmuo.Vardas,
                        Pavarde = x.Skiepas.Asmuo.Pavarde,
                        GimimoData = x.Skiepas.Asmuo.GimimoData
                    },
                    Skiepas = x.Skiepas.SkiepoPriezastis.Pavadinimas,
                    Vakcina = x.Skiepas.VaistoFirminisPavadinimas.Pavadinimas,
                    DozeEilNr = x.Skiepas.DozesEilesNumeris,
                    Reakcija = x.Skiepas.SkiepoReakcija.Pavadinimas,
                    E063BusenaId = x.Skiepas.BusenaId,

                    AsmuoId = x.AsmuoId,
                    FormosTipas = x.SveikatosIrasoTipasId,
                    FormaId = x.SveikatosIrasasKey,
                    CheckBoxShowCondition = x.Skiepas.BusenaId == Busena.PateiktasPasirasymui && x.Skiepas.PaskyrePadalinioDarboVieta.PadalinioDarbuotojas.AsmuoId == userAsmuoId
                });
               var sq = skiepasQuery
                    .Select(x => new ListItem
                    {
                        Id = x.Id,
                        SkiepasId = x.Id,
                        PlanuojamaData = x.SuplanuotaData,
            Pacientas = new Pacientas
                        {
                            Vardas = x.Asmuo.Vardas,
                            Pavarde = x.Asmuo.Pavarde,
                            GimimoData = x.Asmuo.GimimoData
                        },
                        Skiepas = x.SkiepoPriezastis.Pavadinimas,
                        Vakcina = x.VaistoFirminisPavadinimas.Pavadinimas,
                        DozeEilNr = x.DozesEilesNumeris,
                    });
                q = q.Union(sq);
          var list = await q
                .OrderBy(x => x.PlanuojamaData)
                .Skip(message.StartIndex)
                .Take(message.Count)
                .ToListAsync();

ListItem.cs
Removing property set from first query all works.
Error:

An unhandled exception has occurred while executing the request.
System.InvalidOperationException: Set operations over different store types are currently unsupported
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplySetOperation(SetOperationType setOperationType, SelectExpression select2, Boolean distinct)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyUnion(SelectExpression source2, Boolean distinct)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateUnion(ShapedQueryExpression source1, ShapedQueryExpression source2)
   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.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.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.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__DisplayClass12_0`1.<ExecuteAsync>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.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

Further technical details

EF Core version 3.0.0:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0)
Operating system: Windows 10
IDE: Visual Studio 2019 16.3.1

[roji]

Duplicate of #16298.

This is a known limitation of the current set operation translation support, which we hope to address for 5.0. However, note that prior to 3.0, no set operations were translated to the server - they were always client-evaluated. As EF Core 3.0 has removed most client evaluation, you simply need to explicitly opt into it by inserting an AsEnumerable() right before your Union call. This should get you the same behavior as 2.2.

Hope this clarifies the situation, feel free to post back if you need more guidance.

[MenaceSan]

Am i understanding this correctly? Union in EF never actually works as a db server side union ? or at least not in cases where i am pulling from multiple tables with different queries? It always pulls the whole set back to the client to perform the union and then any paging operations? That seems to kill most of the purpose of doing server side paging.

[smitpatel]

@MenaceSan - EF Core added support for server side set operations in 3.0 release. See #6812

[MenaceSan]

I'm seeing this error in Code 3.1 "System.InvalidOperationException: Set operations over different store types are currently unsupported". This is code that used to work in Core 2.2. I assume adding the AsEnumerable() would allow it to run without the error. But my question was about its efficiency. I assume the change has not yet been made in 3.1 to union the sets db server side and it is doing the union and paging on the client? So its going to be inefficient with paging of large data sets ? And my second question. Was it also inefficient in 2.2 but i just didnt notice ?

[smitpatel]

@MenaceSan - Which error? Can you file a new issue with error details and steps to reproduce the issue? It is hard to tell anything without information. Remember that LINQ allows you to write a whole variety of queries which cannot be represented in server side.

[roji]

@MenaceSan "Set operations over different store types are currently unsupported" is tracked by #19129 - please confirm that your error case is the same (i.e. union over varchar with different sizes or similar).

"Set operations over different entity types are currently unsupported" is a different case (union over different entity types, not primitive columns) is tracked by #16298.

Regardless, you are correct that performing the operation client-side (with AsEnuymerable) could be more inefficient as more data gets pulled the client. However:

1. This was the exact 2.2 behavior, except that client evaluation was implicit. No set operations (e.g. Union) were ever translated to SQL or evaluated server-side in 2.2.
2. For set operations specifically, there may not be a big perf difference between server-side and client-side. For example, performing Concat (or Union with few duplicates) will likely perform the same, since all (or most) of the data eventually has to get transferred to the client anyway. However, if you apply another LINQ operator on the result of the set operation (e.g. Where) that could introduce a big perf change.