使用Raw SQL映射ClaimBatch导航属性EventHistory遇错求助

初始问题 #

我编写了如下原生SQL查询代码：

var queryResult = await (await GetDbContextAsync()).Database.SqlQuery<ClaimBatch>(@$"SELECT [CB].[Id], [CB].[TenantId], [InterchangeControlNumber], [BatchIdentifier], [BatchDate], [SenderIdentifier], [ReceiverIdentifier], [AgencyIdentifier], [ReceivedDate], [CreationTime], [CreatorId], [LastModificationTime]
,[LastModifierId], [Filename], [ProcessingRuleSetId], [ProcessingStatus], [SourceClaimBatchId], [ProcessingResultMessage], [ProcessingMethod], [CBE].Id, [CBE].[ClaimBatchId], [CBE].[Description],
[CBE].[TenantId], [CBE].[TimeStamp], [CBE].[Type] FROM [CmClaimBatch] AS [CB]
LEFT JOIN [CmClaimBatchEvent] AS [CBE] ON [CB].[Id] = [CBE].[ClaimBatchId]
")
.ToListAsync();

运行时报错：

The property 'ClaimBatch.EventHistory' of type 'IList' appears to be a navigation to another entity type. Navigations are not supported when using 'SqlQuery". Either include this type in the model and use 'FromSql' for the query, or ignore this property using the '[NotMapped]' attribute.

原因是ClaimBatch实体包含一个导航属性列表：

public IList<ClaimBatchEvent> EventHistory { get; set; }

想知道如何通过左联查询映射这个列表属性？

更新后的问题 #

根据建议修改代码为：

var context = await GetDbContextAsync();

var claimBatch = context.ClaimBatch
               .FromSql(@$"
        SELECT 
            [CB].[Id] AS ClaimBatchId, 
            [CB].[TenantId],
            [CB].[InterchangeControlNumber], 
            [CB].[BatchIdentifier], 
            [CB].[BatchDate], 
            [CB].[SenderIdentifier], 
            [CB].[ReceiverIdentifier], 
            [CB].[AgencyIdentifier], 
            [CB].[ReceivedDate], 
            [CB].[ConcurrencyStamp],
            [CB].[ExtraProperties], 
            [CB].[CreationTime], 
            [CB].[CreatorId], 
            [CB].[LastModificationTime],
            [CB].[LastModifierId], 
            [CB].[Filename], 
            [CB].[ProcessingRuleSetId], 
            [CB].[ProcessingStatus], 
            [CB].[SourceClaimBatchId], 
            [CB].[ProcessingResultMessage], 
            [CB].[ProcessingMethod], 
            [CBE].[Id] AS EventId, 
            [CBE].[Description],
            [CBE].[TenantId],
            [CBE].[TimeStamp], 
            [CBE].[Type] 
        FROM 
            [CmClaimBatch] AS [CB]
        LEFT JOIN 
            [CmClaimBatchEvent] AS [CBE] ON [CB].[Id] = [CBE].[ClaimBatchId]
    ")
               .ToList();

又报错：

The column 'TenantId' was specified multiple times for 'd'.

因为ClaimBatch实体和其EventHistory列表中的ClaimBatchEvent实体均包含TenantId属性，分别对应[CB].[TenantId]和[CBE].[TenantId]，请问如何指定这两个TenantId分别对应哪个实体？

解决方案 #

1. 核心限制说明 #

EF Core的FromSql/SqlQuery只能直接映射当前实体的标量属性，无法自动将联表查询的扁平结果映射到集合类型的导航属性（比如EventHistory）。所以直接用左联查询无法自动填充导航列表，同时重复列名会导致解析冲突。

2. 先解决列名重复问题 #

给重复的TenantId列添加明确别名，区分归属：

SELECT 
    [CB].[Id] AS ClaimBatchId, 
    [CB].[TenantId] AS ClaimBatchTenantId, -- ClaimBatch的TenantId别名
    [CB].[InterchangeControlNumber], 
    -- 其他ClaimBatch属性...
    [CBE].[Id] AS EventId, 
    [CBE].[TenantId] AS EventTenantId, -- ClaimBatchEvent的TenantId别名
    [CBE].[Description],
    [CBE].[TimeStamp], 
    [CBE].[Type] 
FROM 
    [CmClaimBatch] AS [CB]
LEFT JOIN 
    [CmClaimBatchEvent] AS [CBE] ON [CB].[Id] = [CBE].[ClaimBatchId]

3. 正确映射导航属性的两种方法 #

方法一：使用EF Core原生导航加载（推荐）

如果不需要自定义复杂SQL，直接用Include加载关联数据，EF会自动处理联表和属性映射：

var claimBatchList = await context.ClaimBatch
    .Include(cb => cb.EventHistory) // 加载关联的EventHistory集合
    .ToListAsync();

方法二：手动处理扁平查询结果（自定义SQL场景）

如果必须用自定义原生SQL，需要手动将扁平数据分组转换为实体：

• 第一步：定义DTO接收扁平查询结果

public class ClaimBatchWithEventDto
{
    // ClaimBatch属性
    public int ClaimBatchId { get; set; }
    public Guid ClaimBatchTenantId { get; set; }
    public string InterchangeControlNumber { get; set; }
    // 其他ClaimBatch属性...

    // ClaimBatchEvent属性（左联可能为空，用可空类型）
    public int? EventId { get; set; }
    public Guid? EventTenantId { get; set; }
    public string Description { get; set; }
    // 其他ClaimBatchEvent属性...
}

• 第二步：查询DTO

var flatResults = await context.Database.SqlQuery<ClaimBatchWithEventDto>(@$"
    SELECT 
        [CB].[Id] AS ClaimBatchId, 
        [CB].[TenantId] AS ClaimBatchTenantId,
        [CB].[InterchangeControlNumber], 
        -- 其他ClaimBatch属性...
        [CBE].[Id] AS EventId, 
        [CBE].[TenantId] AS EventTenantId,
        [CBE].[Description],
        [CBE].[TimeStamp], 
        [CBE].[Type] 
    FROM 
        [CmClaimBatch] AS [CB]
    LEFT JOIN 
        [CmClaimBatchEvent] AS [CBE] ON [CB].[Id] = [CBE].[ClaimBatchId]
").ToListAsync();

• 第三步：分组转换为实体

var claimBatchList = flatResults
    .GroupBy(dto => dto.ClaimBatchId)
    .Select(g => new ClaimBatch
    {
        Id = g.Key,
        TenantId = g.First().ClaimBatchTenantId,
        InterchangeControlNumber = g.First().InterchangeControlNumber,
        // 其他ClaimBatch属性赋值...
        EventHistory = g
            .Where(dto => dto.EventId.HasValue)
            .Select(dto => new ClaimBatchEvent
            {
                Id = dto.EventId.Value,
                TenantId = dto.EventTenantId.Value,
                Description = dto.Description,
                // 其他ClaimBatchEvent属性赋值...
                ClaimBatchId = g.Key
            })
            .ToList()
    })
    .ToList();

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