EF Core查询转换为T-SQL不符合预期的问题排查

实体定义 #

public class Member
{
    public int Id { get; set; }
}

public class MemberHistory
{
    public int Id { get; set; }
    public int MemberId { get; set; }
    public Member Member { get; set; } = default!;
    public string CT1 { get; set; } = default!;
    public DateTime CreatedAt { get; set; }
}

原EF Core查询代码 #

var maxDate = new DateTime(2022, 1, 1);
var result = context.MemberHistories
    .Where(x => x.CreatedAt.Date <= maxDate.Date)
    .GroupBy(x => x.MemberId)
    .Select(x => x
        .OrderByDescending(mh => mh.CreatedAt)
        .First())
    .Where(x => EF.Functions.Like("CT1", "%1"))
    .ToList();

return result;

EF Core生成的T-SQL #

SELECT [t0].[Id], [t0].[CT1], [t0].[CreatedAt], [t0].[MemberId]
      FROM (
          SELECT [m].[MemberId]
          FROM [MemberHistories] AS [m]
          WHERE CONVERT(date, [m].[CreatedAt]) <= '2022-01-01'
          GROUP BY [m].[MemberId]
          HAVING N'CT1' LIKE N'%1'
      ) AS [t]
      LEFT JOIN (
          SELECT [t1].[Id], [t1].[CT1], [t1].[CreatedAt], [t1].[MemberId]
          FROM (
              SELECT [m0].[Id], [m0].[CT1], [m0].[CreatedAt], [m0].[MemberId], ROW_NUMBER() OVER(PARTITION BY [m0].[MemberId] ORDER BY [m0].[CreatedAt] DESC) AS [row]
              FROM [MemberHistories] AS [m0]
              WHERE CONVERT(date, [m0].[CreatedAt]) <= '2022-01-01'
          ) AS [t1]
          WHERE [t1].[row] <= 1
      ) AS [t0] ON [t].[MemberId] = [t0].[MemberId]

预期与实际结果差异 #

• 预期结果：
  1. 先获取每个MemberId在maxDate或更早时间点的最新记录
  2. 再从这些记录中过滤出CT1以'1'结尾的条目
• 实际结果：
  获取的是存在符合日期条件且CT1匹配'%1'的记录的MemberId对应的最新日期记录，而非最新记录本身满足CT1条件

问题分析 #

生成的T-SQL中存在逻辑错误：HAVING N'CT1' LIKE N'%1'这一行位置完全错误。这里是把字符串常量'CT1'和'%1'做匹配，而非对分组后的记录字段进行判断，同时错误地将过滤逻辑放到了分组的HAVING子句中，而非对最终的最新记录结果集做过滤。

正确的T-SQL逻辑应该是先筛选出每个MemberId的最新记录，再对这些记录做CT1的过滤，比如：

SELECT [t1].[Id], [t1].[CT1], [t1].[CreatedAt], [t1].[MemberId]
  FROM (
      SELECT [m0].[Id], [m0].[CT1], [m0].[CreatedAt], [m0].[MemberId], ROW_NUMBER() OVER(PARTITION BY [m0].[MemberId] ORDER BY [m0].[CreatedAt] DESC) AS [row]
      FROM [MemberHistories] AS [m0]
      WHERE CONVERT(date, [m0].[CreatedAt]) <= '2022-01-01'
  ) AS [t1]
  WHERE [t1].[row] <= 1 AND [CT1] LIKE '%1'

替代尝试的异常 #

尝试将EF.Functions.Like("CT1", "%1")替换为x.CT1.EndsWith("1")时，抛出异常：

给定的键“EmptyProjectionMember”不在字典中。

问题原因与修复方案 #

1. 原查询的错误点 #

你在Where过滤时犯了低级错误：EF.Functions.Like("CT1", "%1")中的第一个参数是字符串常量"CT1"，而非实体属性x.CT1。EF Core无法识别这个常量对应的字段，因此错误地将过滤逻辑转换到了GROUP BY的HAVING子句中。

2. 正确的查询写法 #

将过滤条件改为引用实体属性x.CT1即可：

var maxDate = new DateTime(2022, 1, 1);
var result = context.MemberHistories
    .Where(x => x.CreatedAt.Date <= maxDate.Date)
    .GroupBy(x => x.MemberId)
    .Select(x => x
        .OrderByDescending(mh => mh.CreatedAt)
        .First())
    .Where(x => EF.Functions.Like(x.CT1, "%1")) // 修正为引用x.CT1
    .ToList();

return result;

或者使用EndsWith的正确写法（同样要引用x.CT1）：

.Where(x => x.CT1.EndsWith("1"))

3. 异常说明 #

之前使用EndsWith时抛出的EmptyProjectionMember异常，本质也是因为原查询错误地使用字符串常量而非属性引用，导致EF Core的查询解析器无法正确映射投影字段，修正属性引用后即可解决。

4. 更高效的查询写法（可选） #

为了避免EF Core生成冗余的JOIN逻辑，可以改用窗口函数的写法，直接在子查询中完成分组取最新+过滤：

var maxDate = new DateTime(2022, 1, 1);
var result = context.MemberHistories
    .Select(mh => new 
    {
        mh,
        RowNum = EF.Functions.RowNumber().Over()
            .PartitionBy(mh.MemberId)
            .OrderByDescending(mh.CreatedAt)
    })
    .Where(x => x.mh.CreatedAt.Date <= maxDate.Date && x.RowNum == 1 && x.mh.CT1.EndsWith("1"))
    .Select(x => x.mh)
    .ToList();

这种写法生成的T-SQL会更接近你预期的简洁版本，性能也更优。

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