EF Core中使用Include关联不同排序规则的SQL视图报错

由于两个数据源的排序规则不同，直接使用导航属性+Include会因列排序规则不匹配报错。以下是几种无需完全手写SQL的解决方案：

方法1：修改视图定义（推荐） #

直接在目标视图VariantsView中，将关联列的排序规则转换为与Masters表一致的格式，从根源解决匹配问题：

-- 修改视图定义，将ItemNo转换为目标排序规则
SELECT 
    ItemNo COLLATE Latin1_General_CI_AS AS ItemNo,
    -- 其他列...
FROM AnotherDB.dbo.Variants

修改后，EF Core的默认导航属性关联会自动生效，原有的Include代码无需任何改动，其他6个导航属性也可按此逻辑处理对应视图。

方法2：手动配置查询关联（无需修改视图） #

如果无法修改视图，可在查询中用GroupJoin（对应LEFT JOIN）替代Include，手动指定带排序规则的关联条件：

var q = dbContext.Masters
    .Where(m => m.Id == request.Id)
    // 处理VariantView的LEFT JOIN关联
    .GroupJoin(
        dbContext.VariantViews,
        m => EF.Functions.Collate(m.ItemNo, "Latin1_General_CI_AS"),
        vv => EF.Functions.Collate(vv.ItemNo, "Latin1_General_CI_AS"),
        (master, variantViews) => new { Master = master, VariantViews = variantViews }
    )
    .SelectMany(
        x => x.VariantViews.DefaultIfEmpty(),
        (x, variantView) => 
        {
            x.Master.VariantView = variantView;
            return x.Master;
        }
    )
    // 依次处理其他6个导航属性，重复上述GroupJoin+SelectMany逻辑
    .GroupJoin(dbContext.OtherView1, ...)
    .SelectMany(...);

这种方式需要为每个导航属性编写关联逻辑，但能保留实体的导航属性结构。

方法3：使用EF Core拦截器自动修改SQL #

通过自定义DbCommandInterceptor拦截EF生成的SQL，自动在JOIN条件中添加排序规则转换，这样原有的Include代码可完全复用：

1. 编写拦截器类 #

public class CollateFixInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        // 替换VariantView的关联条件
        command.CommandText = command.CommandText.Replace(
            "ON [vv].[ItemNo] = [m].[ItemNo]",
            "ON [vv].[ItemNo] COLLATE Latin1_General_CI_AS = [m].[ItemNo] COLLATE Latin1_General_CI_AS"
        );
        
        // 为其他6个视图添加类似的替换规则
        command.CommandText = command.CommandText.Replace(
            "ON [av].[SomeKey] = [m].[SomeKey]",
            "ON [av].[SomeKey] COLLATE Latin1_General_CI_AS = [m].[SomeKey] COLLATE Latin1_General_CI_AS"
        );
        
        return base.ReaderExecuting(command, eventData, result);
    }
}

2. 注册拦截器 #

在ApplicationDbContext的OnConfiguring方法中添加拦截器：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.AddInterceptors(new CollateFixInterceptor());
    // 其他数据库配置...
}

注册后，EF生成的所有包含目标JOIN的SQL都会自动带上排序规则转换，原有的Include查询可正常运行。

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