GroupBy之后Select(x => x.First())无法翻译为Sql执行

[wellsjiang]

我有一个按天分表的需求，此表需要按时间间隔分组后取时间间隔内的最后一条数据。 例如按照10秒一个分组： 00:00 ~ 00:09 00:10 ~ 00:19 00:20 ~ 00:29 00:30 ~ 00:39 00:40 ~ 00:49 00:50 ~ 00:59 1分钟分为6个分组，每个分组取分组内按时间排序的最后一条 Timestamp为对应的UTC时间戳，这样我可以通过取整来排序。

表结构：

public class TagHistory
{
public long PathId { get; set; }
public long ProviderId { get; set; }
public byte Quality { get; set; }
public bool StopScan { get; set; }
public int? IntegerVal { get; set; }
public long Timestamp { get; set; }
}

对应的Linq： var list = queryable.Select(x => new { Interval = x.Timestamp / query.Interval, Timestamp = x.Timestamp, Quality = x.Quality, StopScan = x.StopScan, Value = x.IntegerVal, }).GroupBy(x => x.Interval).Select(x => x.OrderByDescending(y => y.Timestamp).First()).Take(100).ToListAsync();

原生EFCore6是不支持的以上写法的，但是升级EFCore7后支持该写法，能正常执行。翻译后的Sql如下： Compiling query expression: 'DbSet() .AsNoTracking() .Where(x => x.ProviderId == 1 && x.PathId == 1) .Where(x => x.Time >= query_Begin_0) .Where(x => x.Time <= query_End_1) .Where(x => x.IntegerVal != null) .Select(x => new { Interval = x.Timestamp / __p_2, Timestamp = x.Timestamp, Quality = x.Quality, StopScan = x.StopScan, Value = x.IntegerVal }) .GroupBy(x => x.Interval) .Select(x => x .OrderByDescending(y => y.Timestamp) .First()) .Take(__p_3)'

SELECT "t1"."Interval", "t1"."Timestamp", "t1"."Quality", "t1"."StopScan", "t1"."Value", "t1"."c" FROM ( SELECT "t"."Key" FROM ( SELECT "s"."Timestamp" / @p_2 AS "Key" FROM "TagHistory" AS "s" WHERE "s"."ProviderId" = 1 AND "s"."PathId" = 1 AND "s"."Time" >= @query_Begin_0 AND "s"."Time" <= @query_End_1 AND ("s"."IntegerVal" IS NOT NULL) ) AS "t" GROUP BY "t"."Key" LIMIT @__p_3 ) AS "t0" LEFT JOIN ( SELECT "t2"."Interval", "t2"."Timestamp", "t2"."Quality", "t2"."StopScan", "t2"."Value", "t2"."c", "t2"."Key" FROM ( SELECT "t3"."Timestamp" / @p_2 AS "Interval", "t3"."Timestamp", "t3"."Quality", "t3"."StopScan", "t3"."IntegerVal" AS "Value", 1 AS "c", "t3"."Key", ROW_NUMBER() OVER(PARTITION BY "t3"."Key" ORDER BY "t3"."Timestamp" DESC) AS "row" FROM ( SELECT "s0"."IntegerVal", "s0"."Quality", "s0"."StopScan", "s0"."Timestamp", "s0"."Timestamp" / @__p_2 AS "Key" FROM "TagHistory" AS "s0" WHERE "s0"."ProviderId" = 1 AND "s0"."PathId" = 1 AND "s0"."Time" >= @query_Begin_0 AND "s0"."Time" <= @query_End_1 AND ("s0"."IntegerVal" IS NOT NULL) ) AS "t3" ) AS "t2" WHERE "t2"."row" <= 1 ) AS "t1" ON "t0"."Key" = "t1"."Key"

但是ShardingCore 7.7.1.16 版本并不支持该写法。报Linq无法翻译 控制台捕获的错误日志： The LINQ expression 'DbSet() .Where(s => s.ProviderId == 1 && s.PathId == 1) .Where(s => s.Time >= query_Begin_0) .Where(s => s.Time <= query_End_1) .Where(s => s.IntegerVal != null) .GroupBy(s => s.Timestamp / p_2) .Select(g => g .AsQueryable() .Select(e => new { Interval = e.Timestamp / __p_2, Timestamp = e.Timestamp, Quality = e.Quality, StopScan = e.StopScan, Value = e.IntegerVal }) .First()) .Take(p_3) .OrderBy(e0 => e0.Interval)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

EFcore打印的Debug日志： dbug: 2023/10/23 10:17:46.194 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) Compiling query expression: 'DbSet() .AsNoTracking() .Where(x => x.ProviderId == 1 && x.PathId == 1) .Where(x => x.Time >= query_Begin_0) .Where(x => x.Time <= query_End_1) .Where(x => x.IntegerVal != null) .Select(x => new { Interval = x.Timestamp / __p_2, Timestamp = x.Timestamp, Quality = x.Quality, StopScan = x.StopScan, Value = x.IntegerVal }) .GroupBy(x => x.Interval) .Select(x => x .First()) .Take(__p_3) .OrderBy(o => o.Interval) .ThenBy(o => o.Timestamp) .ThenBy(o => o.Quality) .ThenBy(o => o.StopScan) .ThenBy(o => o.Value)' warn: 2023/10/23 10:17:46.195 CoreEventId.RowLimitingOperationWithoutOrderByWarning[10102] (Microsoft.EntityFrameworkCore.Query) The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results. If the 'Distinct' operator is used after 'OrderBy', then make sure to use the 'OrderBy' operator after 'Distinct' as the ordering would otherwise get erased.

[xuejmnet]

@wellsjiang 是在不好意思,目前框架不支持跨表分片 group+first，可以在begin和end时间内先将时间进行按天分组然后进行每天的group+first

[xuejmnet]

就是说如果你要查询的是1号2号和3号,分别查询1号[time>2023-01-01 00:00:00 and time <2023-01-02 00:00:00] [time>2023-01-02 00:00:00 and time <2023-01-03 00:00:00] [time>2023-01-03 00:00:00 and time <2023-01-04 00:00:00] 切分成3组,后分别进行efcore调用,这样就使用原生的efcore7调用因为本质是不会出现跨分片的

[xuejmnet]

当然还有一种就是使用unionall merge进行sharding分片聚合查询,这种情况就是将多个table进行unionall合并，具体代码参考 https://github.com/dotnetcore/sharding-core/tree/main/samples/Sample.SqlServer

[wellsjiang]

好的，多谢解答，问题已解决