Linq / Entity Framework 选择组的最新记录

Question

我正在尝试弄清楚如何编写LINQ / ENTITY框架查询以返回表中每个符号可用的最新数据。

我的数据库表看起来像这样：

ID    symbol    price_date    price
------------------------------------
1     AAPL      2022-02-28    174.50
2     MSFT      2022-02-28    307.20
3     AAPL      2021-03-01    172.23
4     MSFT      2021-03-01    304.15

并非每个符号都有每天的记录。 ID密钥是顺序的，可以安全地用作给定符号的最高ID，其中包含最新数据。

如果我正在编写SQL查询，则以下内容将返回我要寻找的内容：

select prices.*
from prices 
where id in (select max(id) from prices group by symbol)

在Linq中，我很难将其用于单个查询。 到目前为止，我将其分为两个查询：

var maxIds = from pp in ctx.Prices
             group pp by pp.Symbol
                 into maxIdBySymbol
             select maxIdBySymbol.Max(pp => pp.Id);

var latestPrices = ctx.Prices.Where(it => maxIds.Contains(it.Id)).ToList();

有没有办法在Linq中进行单个查询？

谢谢

Answer 1

补充：建议的解决方案有效，但效率较低
更多信息请参见最后的补充。

原始解决方案

因此，您将首先创建一组记录，其中每个组仅包含一个特定符号的记录。因此，您将拥有一组包含代码 AAPL 的记录，一组包含代码 MSFT 的记录，等等。

我正在尝试...查询...表中每个交易品种的可用最新数据。

因此，一旦获得了组，就可以在组中选择一个元素。根据您的要求，您选择最新的元素，即 PriceDate 值最高的元素。正如您所说，您还可以采用属性 ID 具有最高值的元素。就我个人而言，我不会这样做，因为如果在很远的将来，您的 ID 不再是升序日期，例如因为您添加了在输入错误后编辑 PriceDate 的功能。

为此，我将使用 具有参数 resultSelector 的 Queryable.GroupBy 重载。使用 resultSelector 选择每个组中所需的一个元素。

var newestRecordPerSymbol = dbContext.PriceRecords

// make groups of priceRecords with same value for property Symbol
.GroupBy( priceRecord => priceRecord.Symbol,

// parameter resultSelector: for every symbol and all priceRecords
// that have this symbol, take the newest one
// = order by descending PriceDate and take the first one
(symbol, priceRecordsWithThisSymbol) => priceRecordsWithThisSymbol
    .OrderByDescending(priceRecord => priceRecord.PriceDate)
    .FirstOrDefault();

换句话说：从 PriceRecords 表中，创建具有相同属性 Symbol 值的 PriceRecord 组。从符号和具有此符号的 PriceRecords 的每个组合中，按属性 PriceDate 的降序值对所有 PriceRecords 进行排序，并仅保留第一个。

每个组至少有一个元素，因此您可以使用 First 以及 FirstOrDefault。某些版本的 EntityFramework 或 DBMS 在使用 First 时存在问题。如果遇到此问题，请使用 FirstOrDefault。

如果你还想取ID最高的那个：

  .OrderByDescending(priceRecord => priceRecord.ID)
  .FirstOrDefault(),

为什么这个解决方案效率较低。

在原来的解决方案中，对一组中的所有记录进行排序，只取第一个记录。如果只取第一个元素，那么对第二个、第三个等元素进行排序有点浪费。

在原始 SQL 中，您将看到如下代码：

select maxIdBySymbol.Max(pp => pp.Id);

因此，并非所有元素都已排序。该序列仅枚举一次，并返回最大的序列。这比对您无论如何都不会使用的元素进行排序要有效得多。

要创建这样的代码，我们需要更改 GroupBy 的参数 resultSelector。让我们使用类似的方法 Max(propertySelector)，或 可查询.聚合。像这样的事情：

// parameter resultSelector: keep the record with the largest ID
(symbol, priceRecordsWithThisSymbol) => priceRecordsWithThisSymbol
    .Max(record => record.Id);

唉，虽然实体框架的人做了很多工作，但不支持 Max 方法的这种重载，因此也不支持任何 Aggregate 方法。请参阅支持和不支持的 Linq 方法列表

Answer 2

您可以将 Where 与 not Any 结合使用：

ctx.Prices.Where(prices1 => !ctx.Prices.Any(prices2 => (prices2.Id > prices1.Id) && (prices1.symbol.Equals(prices2.symbol))))