为何EF Core查询结果与SQL Server Management Studio不一致？

核心错误点 #

1. 主键重复导致EF实体跟踪异常 #

你在SQL查询中给id字段统一赋值为-1，而DocTemplateFile实体的主键应为Id字段。EF Core依赖主键区分不同实体，所有记录主键相同时，EF会判定这些是同一实体的重复引用，最终返回的列表全为同一条数据。

2. 排序逻辑不匹配 #

你的SSMS查询是按d.id desc排序后取前10条，但EF代码中是先执行无排序的SQL查询，再在内存中按b.Id排序——而Id全为-1，等于没有排序，自然和SSMS的结果顺序不匹配。额外添加的Distinct()在主键重复的情况下也无法生效。

修复方案 #

方案1：给主键赋值唯一值（推荐） #

如果DocTemplateFile的Id不需要持久化，可在SQL中生成唯一值，确保每条记录主键唯一：

select top 10
    2 as docstorageid, 
    d.ID as originaldocid,
    df.id as originalfileid, 
    c.id as custid,
    u.id as userid,
    NEWID() as id, -- 生成唯一GUID作为主键
    df.Page as PageId, 
    df.FileName as FilePath  
from 
    documents d 
inner join 
    DocumentFiles df on df.Documents_ID = d.id
inner join 
    users u on u.login_id = d.UploadedBy_User_ID
inner join 
    customers c on c.id = u.Customers_ID
where 
    df.FileTypes_ID = 2 
    and substring (df.FileName, 1, 4) = 'arti'
    and df.id not in (select originalfileid 
                      from documenttemplatefiles)
order by 
    d.id desc

对应的EF代码直接使用该查询，无需额外排序和去重：

int count =10;

try
{
    List<DocTemplateFile> rval = _context.DocumentTemplateFiles.FromSql(
        $@"select top {count}
            2 as docstorageid, 
            d.ID as originaldocid,
            df.id as originalfileid, 
            c.id as custid,
            u.id as userid,
            NEWID() as id, 
            df.Page as PageId, 
            df.FileName as FilePath  
        from 
            documents d 
        inner join 
            DocumentFiles df on df.Documents_ID = d.id
        inner join 
            users u on u.login_id = d.UploadedBy_User_ID
        inner join 
            customers c on c.id = u.Customers_ID
        where 
            df.FileTypes_ID = 2 
            and substring (df.FileName, 1, 4) = 'arti'
            and df.id not in (select originalfileid 
                              from documenttemplatefiles)
        order by 
            d.id desc"
    ).ToList();

    return rval;
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    return new List<DocTemplateFile>();
} 

方案2：关闭实体跟踪（临时方案） #

若仅需获取数据、不需要EF跟踪实体，可使用AsNoTracking()，同时对齐排序逻辑：

int count =10;

try
{
    List<DocTemplateFile> rval = _context.DocumentTemplateFiles.FromSql(
        $@"select
            2 as docstorageid, 
            d.ID as originaldocid,
            df.id as originalfileid, 
            c.id as custid,
            u.id as userid,
            -1 as id, 
            df.Page as PageId, 
            df.FileName as FilePath
        from documents d
        inner join DocumentFiles df on df.Documents_ID = d.id
        inner join users u on u.login_id = d.UploadedBy_User_ID
        inner join customers c on c.id = u.Customers_ID
        where df.FileTypes_ID = 2 and substring(df.FileName, 1,4) = 'arti'
            and df.id not in (select originalfileid from documenttemplatefiles)
        order by d.id desc"
    )
    .AsNoTracking() // 关闭实体跟踪，避免主键重复导致的引用异常
    .Take(count)
    .ToList();

    return rval;
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    return new List<DocTemplateFile>();
} 

注：此方案仅能规避重复数据问题，主键重复仍存在潜在风险，优先选择方案1。

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