如何基于DB2属性对DB1的EF Core查询结果排序？

问题背景 #

DB1包含Users表，User实体通过AddressId关联DB2中的Addresses表，需按DB2中Address.Locality.Name对Users查询结果排序。原方案预计算<AddressId, OrderIndex>映射字典后，因EF Core无法将filteredOrderMap[c.AddressId]转换为SQL而失败。

可行解决方案 #

方案1：利用数据库跨库查询能力（适用于同实例多数据库场景） #

如果DB1和DB2部署在同一个数据库实例（如SQL Server），可直接通过EF Core关联两个库的表，让数据库完成排序和分页，性能最优。

示例代码：

// 确保DbContext能访问两个库的表（可通过配置表的数据库前缀实现，如DB2.dbo.Addresses）
var orderedUsers = await DB1Context.Users
    .Join(
        DB2Context.Addresses.Include(a => a.Locality),
        user => user.AddressId,
        address => address.Id,
        (user, address) => new { User = user, LocalityName = address.Locality.Name }
    )
    .OrderBy(joinResult => joinResult.LocalityName)
    .Select(joinResult => joinResult.User)
    .Skip(pageIndex * pageSize)
    .Take(pageSize)
    .ToListAsync();

方案2：构建Case表达式实现EF可解析的排序逻辑 #

通过手动构建CASE WHEN表达式，将预计算的排序映射转换为EF Core能解析为SQL的排序条件，避免内存排序的性能问题。

示例代码：

// 预计算排序映射（保留原逻辑）
var addressesOrderMap = await DB2Context.Addresses
    .Include(a => a.Locality)
    .OrderBy(a => a.Locality.Name)
    .Select((a, index) => new { a.Id, OrderIndex = index })
    .ToDictionaryAsync(x => x.Id, x => x.OrderIndex);

var pageAddresses = await DB1Context.Users
    .Select(u => u.AddressId)
    .Distinct()
    .ToListAsync();

var filteredOrderMap = addressOrderMap
    .Where(kvp => pageAddresses.Contains(kvp.Key))
    .ToDictionary(kvp => kvp.Key, kvp => kvp.Value);

// 构建CASE WHEN表达式
var userParam = Expression.Parameter(typeof(User), "user");
var addressIdProp = Expression.Property(userParam, nameof(User.AddressId));
var whenClauses = filteredOrderMap.Select(kvp =>
    Expression.When(
        Expression.Equal(addressIdProp, Expression.Constant(kvp.Key)),
        Expression.Constant(kvp.Value)
    )
).ToList();

// 默认值设为int.MaxValue，让无匹配的AddressId排在最后
var caseExpr = Expression.Case(whenClauses, Expression.Constant(int.MaxValue));
var orderByExpr = Expression.Lambda<Func<User, int>>(caseExpr, userParam);

// 执行查询
var orderedUsers = await DB1Context.Users
    .Where(u => filteredOrderMap.ContainsKey(u.AddressId))
    .OrderBy(orderByExpr)
    .Skip(pageIndex * pageSize)
    .Take(pageSize)
    .ToListAsync();

方案3：内存排序（适用于数据量较小的场景） #

若Users表数据量不大，可先查询所有用户数据到内存，再结合排序映射完成排序和分页。此方法无需数据库层面的跨库支持，但数据量大时性能较差。

示例代码：

// 预计算排序映射（同原逻辑）
var addressesOrderMap = await DB2Context.Addresses
    .Include(a => a.Locality)
    .OrderBy(a => a.Locality.Name)
    .Select((a, index) => new { a.Id, OrderIndex = index })
    .ToDictionaryAsync(x => x.Id, x => x.OrderIndex);

// 查询所有用户到内存
var allUsers = await DB1Context.Users.ToListAsync();

// 内存中排序并分页
var orderedUsers = allUsers
    .OrderBy(u => addressesOrderMap.TryGetValue(u.AddressId, out var index) ? index : int.MaxValue)
    .Skip(pageIndex * pageSize)
    .Take(pageSize)
    .ToList();

内容的提问来源于stack exchange，提问作者Vicente Monteiro