带投影的IQueryable Union在SQLite测试报错，SQL Server运行正常

问题 #

将两个IQueryable查询投影至同一ViewModel类型后执行Union操作并排序，该逻辑在SQL Server生产环境正常运行，但在SQLite集成测试中失败。

示例实体 #

public class EntityA
{
    public Guid Id { get; set; }
    public string Name { get; set; } = "";
    public string EntityASpecificLabel { get; set; } = "";
}

public class EntityB
{
    public Guid Id { get; set; }
    public string Name { get; set; } = "";
    public string EntityBSpecificLabel { get; set; } = "";
}

ViewModel定义 #

public class ViewModel
{
    public Guid Id { get; set; }
    public string Name { get; set; } = "";
    public string EntityASpecificLabel  { get; set; } = "";
    public string EntityBSpecificLabel { get; set; } = "";
}

查询代码 #

var queryA = context.EntityAs
                    .Where(...)
                    .Select(x => new ViewModel
                             {
                                 Id = x.Id,
                                 Name = x.Name,
                                 EntityASpecificLabel = Convert.ToString(x.EntityASpecificLabel),
                                 EntityBSpecificLabel = ""
                             });

var queryB = context.EntityBs
                    .Where(...)
                    .Select(x => new ViewModel
                             {
                                 Id = x.Id,
                                 Name = x.Name,
                                 EntityASpecificLabel = "",
                                 EntityBSpecificLabel = Convert.ToString(x.EntityBSpecificLabel)
                             });

queries = queryA.Union(queryB)
                .OrderBy(x => x.Name)
                .ThenBy(x => x.EntityASpecificLabel)
                .ThenBy(x => x.EntityBSpecificLabel);

var result = await queries.ToListAsync();

报错信息 #

System.InvalidOperationException : Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.
   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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.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.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 LinqKit.ExpandableQuery`1.System.Collections.Generic.IAsyncEnumerable<T>.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

排查后确认问题出在ViewModel中EntityASpecificLabel和EntityBSpecificLabel的投影逻辑，注释这些字段及对应排序后查询可正常运行，需解决SQL Server与SQLite的兼容差异。

解决方案 #

方案1：调整查询顺序，先Union再投影 #

EF Core对不同数据库的SQL翻译能力存在差异，SQLite无法正确解析「先投影再Union」的逻辑，而SQL Server支持。按照报错提示的建议，先对原始实体的查询执行Union，再统一投影到ViewModel：

// 先从两个实体中取出需要的字段，构造匿名类型执行Union
var unionQuery = context.EntityAs
    .Where(...)
    .Select(x => new 
    {
        x.Id,
        x.Name,
        EntityASpecificLabel = x.EntityASpecificLabel,
        EntityBSpecificLabel = (string)null // 用null代替空字符串，后续统一处理
    })
    .Union(
        context.EntityBs
            .Where(...)
            .Select(x => new 
            {
                x.Id,
                x.Name,
                EntityASpecificLabel = (string)null,
                EntityBSpecificLabel = x.EntityBSpecificLabel
            })
    );

// 再投影到ViewModel，并将null转换为空字符串
var queries = unionQuery
    .Select(x => new ViewModel
    {
        Id = x.Id,
        Name = x.Name,
        EntityASpecificLabel = x.EntityASpecificLabel ?? "",
        EntityBSpecificLabel = x.EntityBSpecificLabel ?? ""
    })
    .OrderBy(x => x.Name)
    .ThenBy(x => x.EntityASpecificLabel)
    .ThenBy(x => x.EntityBSpecificLabel);

var result = await queries.ToListAsync();

方案2：改用Concat替代Union（无需去重场景） #

如果业务逻辑不需要Union的去重特性，改用Concat可以避免SQLite的翻译问题——Concat的SQL翻译逻辑更简单，兼容性更好：

var queryA = context.EntityAs
    .Where(...)
    .Select(x => new ViewModel
    {
        Id = x.Id,
        Name = x.Name,
        EntityASpecificLabel = x.EntityASpecificLabel,
        EntityBSpecificLabel = ""
    });

var queryB = context.EntityBs
    .Where(...)
    .Select(x => new ViewModel
    {
        Id = x.Id,
        Name = x.Name,
        EntityASpecificLabel = "",
        EntityBSpecificLabel = x.EntityBSpecificLabel
    });

// 用Concat替换Union
var queries = queryA.Concat(queryB)
    .OrderBy(x => x.Name)
    .ThenBy(x => x.EntityASpecificLabel)
    .ThenBy(x => x.EntityBSpecificLabel);

var result = await queries.ToListAsync();

方案3：客户端执行Union（小数据量场景） #

如果数据量不大，可以先将两个查询的结果加载到客户端内存，再执行Union和排序。此方案适合集成测试等小数据量场景，大数据量下会引发性能问题：

var listA = await context.EntityAs
    .Where(...)
    .Select(x => new ViewModel
    {
        Id = x.Id,
        Name = x.Name,
        EntityASpecificLabel = x.EntityASpecificLabel,
        EntityBSpecificLabel = ""
    })
    .ToListAsync();

var listB = await context.EntityBs
    .Where(...)
    .Select(x => new ViewModel
    {
        Id = x.Id,
        Name = x.Name,
        EntityASpecificLabel = "",
        EntityBSpecificLabel = x.EntityBSpecificLabel
    })
    .ToListAsync();

var result = listA.Union(listB)
    .OrderBy(x => x.Name)
    .ThenBy(x => x.EntityASpecificLabel)
    .ThenBy(x => x.EntityBSpecificLabel)
    .ToList();

内容的提问来源于stack exchange，提问作者Steven Manuel