如何将此 SQL 查询转换为 EF Core 中的 LINQ 查询？

Question

我有下表：

Indicators(A INT, B INT, C INT, D INT, TimeInsertedLocal DateTime) . 

我有映射到此表的 EF Core 映射实体。

我需要将此 SQL 查询转换为 ef core Linq 等效查询。

SELECT A, B, C, D, TimeInsertedLocal

FROM Indicators

WHERE TimeInsertedLocal >= 

(   

    SELECT MAX(I.TimeInsertedLocal) 

    FROM Indicators AS I

) 

这是实体：

public class Indicator

{

    public int A { get; set; }

    public int B { get; set; }

    public int C { get; set; }

    public int D { get; set; }

    public DateTime TimeInsertedLocal { get; set; }

 }

如何编写 LINQ 查询以便 EF Core 生成相同的查询或获得相同结果的更好的查询？

Answer 1

这实际上是一对一的翻译。SQL查询SELECT A, B, C, D , TimeInsertedLocalFROM IndicatorsWHERE TimeInsertedLocal >=&nbsp;(&nbsp; &nbsp;&nbsp; &nbsp; SELECT MAX(I.TimeInsertedLocal)&nbsp;&nbsp; &nbsp; FROM Indicators AS I)EF Core LINQ 查询：var indicators = dbContext.Set<Indicator>();var query = indicators&nbsp; &nbsp; .Where(i => i.TimeInsertedLocal >= indicators.Max(i2 => (DateTime?)i2.TimeInsertedLocal));EF Core 生成的 SQL 查询：SELECT [i].[A], [i].[B], [i].[C], [i].[D], [i].[TimeInsertedLocal]FROM [Indicators] AS [i]WHERE [i].[TimeInsertedLocal] >= (&nbsp; &nbsp; SELECT MAX([i2].[TimeInsertedLocal])&nbsp; &nbsp; FROM [Indicators] AS [i2])LINQ 查询中唯一特定的细节是DateTime?内部强制Max转换，否则 EF Core 将尝试模拟 LINQMax方法抛出行为并评估查询客户端。

Answer 2

当然，没有指标的 TimeInsertedLocal 值大于所有 Indicators 中 TimeInsertedLocal 的最大值。但是，您可能有多个指标的值等于 TimeInsertedLocal 的最大值。如果是这种情况，您需要将具有相同 TimeInsertedLocal 的指标分组，并选择具有最大值的组。var indicatorsWithLargestTimeInsertedLocal = myDbContext.Indicators&nbsp; &nbsp; // make groups of Indicators with same TimeInsertedLocal:&nbsp; &nbsp; .GroupBy(indicator => indicator.TimeInsertedLocal)&nbsp; &nbsp; // put the group with the largest TimeInsertedLocal first:&nbsp; &nbsp; .OrderByDescending(group => group.Key)&nbsp; &nbsp; // The first group of indicators, is the group with the largest value of TimeInsertedLocal&nbsp; &nbsp; .FirstOrDefault();如果确定TimeInsertedLocal是唯一的，就不用GroupBy，TimeInsertedLocal最大的指标只会有一个var indicatorWithLargestTimeInsertedLocal = myDbContext.Indicators&nbsp; &nbsp; .OrderByDescending(indicator => indicator.TimeInsertedLocal)&nbsp; &nbsp; .FirstOrDefault();