求Entity Framework Core中对应条件聚合SQL的等效查询实现

需求说明 #

需要将Item表中同一ItemKey的键值对合并为单行数据，同时筛选出Location以s开头的记录，对应你提供的条件聚合SQL语句的EF Core实现。

步骤1：定义合并结果的DTO类 #

由于合并后的结构与原Item实体（键值对）不一致，需要创建一个DTO来接收聚合后的结果：

public class MergedItem
{
    public string ItemKey { get; set; }
    public string Id { get; set; }
    public string ItemName { get; set; }
    public string Location { get; set; }
    public string Date { get; set; }
}

步骤2：仓储类中的实现方法 #

以下是两种等效的实现方式，均能生成与你提供的SQL逻辑一致的查询：

方式一：方法语法（链式调用）

public async Task<IEnumerable<MergedItem>> GetMergedItemsAsync()
{
    return await itemDbContext.Items
        // 按ItemKey分组，对应SQL的GROUP BY ItemKey
        .GroupBy(item => item.ItemKey)
        // 条件聚合生成单行数据，对应SQL中的CASE WHEN + MAX
        .Select(group => new MergedItem
        {
            ItemKey = group.Key,
            Id = group.Max(item => item.Key == "Item Id" ? item.Value : null),
            ItemName = group.Max(item => item.Key == "Item Name" ? item.Value : null),
            Location = group.Max(item => item.Key == "Location" ? item.Value : null),
            Date = group.Max(item => item.Key == "Date" ? item.Value : null)
        })
        // 筛选Location以's'开头的记录，对应SQL的WHERE子句
        .Where(merged => merged.Location != null && merged.Location.StartsWith("s"))
        .ToListAsync();
}

方式二：查询语法（更贴近SQL结构）

public async Task<IEnumerable<MergedItem>> GetMergedItemsAsync()
{
    var mergedQuery = from item in itemDbContext.Items
                      group item by item.ItemKey into itemGroup
                      // 先定义各字段的聚合结果
                      let id = itemGroup.Max(i => i.Key == "Item Id" ? i.Value : null)
                      let itemName = itemGroup.Max(i => i.Key == "Item Name" ? i.Value : null)
                      let location = itemGroup.Max(i => i.Key == "Location" ? i.Value : null)
                      let date = itemGroup.Max(i => i.Key == "Date" ? i.Value : null)
                      // 映射到DTO
                      select new MergedItem
                      {
                          ItemKey = itemGroup.Key,
                          Id = id,
                          ItemName = itemName,
                          Location = location,
                          Date = date
                      };

    // 应用筛选条件并执行查询
    return await mergedQuery.Where(m => m.Location != null && m.Location.StartsWith("s"))
                            .ToListAsync();
}

关键说明 #

• 由于你的表约束保证每个ItemKey下的每个Key唯一，使用Max()和First()效果一致，但Max()更贴近原SQL的写法。
• 注意Key的字符串匹配要与表约束中的值完全一致（比如"Item Id"而非"Item id"），避免大小写或拼写导致的匹配失败。
• 如果需要不区分大小写的筛选，可以将StartsWith("s")改为StartsWith("s", StringComparison.OrdinalIgnoreCase)，适配不同数据库的大小写规则。

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