使用EF Core7的SqlQueryRaw执行左连接遇类型映射错误求助

一、正确的LINQ左连接实现 #

原SQL的核心是带额外条件的左连接（LEFT JOIN ON包含sg.VendorId = @vendorId），不能将该条件放到WHERE子句（否则会变成内连接），正确的LINQ写法如下：

假设你的DbContext包含Agreements和SignedAgreements两个DbSet，实体类分别为Agreement和SignedAgreement，目标Dto为VendorAgreementSignReadModel：

var vendorId = // 传入的vendorId参数
var type = // 传入的type参数

var query = from ag in dbContext.Agreements
            // 用匿名类型组合连接条件，包含vendorId
            join sg in dbContext.SignedAgreements
            on new { AgreeId = ag.Id, VendorId = vendorId } 
            equals new { AgreeId = sg.AgreementId, sg.VendorId } into signedGroup
            // 左连接必须用DefaultIfEmpty()确保返回所有Agreements记录
            from sg in signedGroup.DefaultIfEmpty()
            where ag.Type == type
            select new VendorAgreementSignReadModel
            {
                AgreementId = ag.Id,
                SignedAt = sg?.SignedAt,
                SignedBy = sg?.SignedByName,
                Type = ag.Type,
                Version = ag.Version,
                VendorId = sg?.VendorId,
                AgreementCreated = ag.CreatedAt
            };

var result = query.ToList();

二、SqlQueryRaw报错的解决方法 #

报错原因是EF Core无法自动映射VendorAgreementSignReadModel类型，提供三种可行方案：

方案1：确保Dto符合EF Core映射要求 #

VendorAgreementSignReadModel必须满足以下条件：

• 拥有公共无参构造函数
• 所有需要映射的属性为公共可读写（同时包含get和set）
• 属性名称与SQL返回的列名一致（或用[Column]特性指定对应列名）

示例Dto定义：

public class VendorAgreementSignReadModel
{
    public int AgreementId { get; set; }
    public DateTime? SignedAt { get; set; }
    public string? SignedBy { get; set; }
    public string Type { get; set; } = string.Empty;
    public int Version { get; set; }
    public int? VendorId { get; set; }
    public DateTime AgreementCreated { get; set; }
}

方案2：配置无键实体映射 #

在DbContext的OnModelCreating方法中，将VendorAgreementSignReadModel配置为无键实体（因为它不是数据库表），然后使用FromSqlRaw替代SqlQueryRaw：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置无键实体映射
    modelBuilder.Entity<VendorAgreementSignReadModel>()
        .HasNoKey()
        .Property(x => x.AgreementId).HasColumnName("AgreementId")
        .Property(x => x.SignedAt).HasColumnName("SignedAt")
        .Property(x => x.SignedBy).HasColumnName("SignedBy")
        .Property(x => x.Type).HasColumnName("Type")
        .Property(x => x.Version).HasColumnName("Version")
        .Property(x => x.VendorId).HasColumnName("VendorId")
        .Property(x => x.AgreementCreated).HasColumnName("AgreementCreated");
}

调用代码：

var sql = @"SELECT ag.ID as AgreementId, 
               sg.SignedAt ,
               sg.SignedByName As SignedBy,
               ag.Type As Type,
               ag.Version,
               sg.VendorId,
               ag.CreatedAt As AgreementCreated 
               from `Agreements` ag LEFT JOIN `SignedAgreements` sg on 
                    sg.`AgreementId`= ag.`Id` 
                   and sg.`VendorId` = @vendorId 
               where ag.`Type` = @type";

var parameters = new[]
{
    new SqlParameter("@vendorId", vendorId),
    new SqlParameter("@type", type)
};

// 使用Set<T>().FromSqlRaw替代SqlQueryRaw
var result = dbContext.Set<VendorAgreementSignReadModel>()
                      .FromSqlRaw(sql, parameters)
                      .ToList();

方案3：临时使用动态类型（仅内部处理） #

如果不需要强类型Dto，可改用dynamic类型临时处理，但不建议用于业务逻辑返回：

var result = dbContext.Database.SqlQueryRaw<dynamic>(sql, parameters).ToList();

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