如何将此 SQL 查询转换为 EF Core 中的 LINQ 查询? [英] How to translate this SQL query to a LINQ query in EF Core?

问题描述

我有下表:

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

我有映射到这个表的 EF Core 映射实体.

And I have the EF Core mapping entity that maps to this table.

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

I need to translate this SQL query to ef core Linq equivalent query .

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 生成相同的查询或获得相同结果的更好的查询?

How to write the LINQ query so that EF Core generate the same query or a better query that achieves the same result?

推荐答案

实际上是一对一翻译.

SQL 查询

SELECT A, B, C, D , TimeInsertedLocal
FROM Indicators
WHERE TimeInsertedLocal >= 
(   
    SELECT MAX(I.TimeInsertedLocal) 
    FROM Indicators AS I
)

EF Core LINQ 查询:

var indicators = dbContext.Set<Indicator>();
var query = indicators
    .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] >= (
    SELECT MAX([i2].[TimeInsertedLocal])
    FROM [Indicators] AS [i2]
)

LINQ 查询中唯一的具体细节是 Max 中的 DateTime? cast，否则 EF Core 将尝试模拟 LINQ Max 方法抛出行为并将评估查询客户端.

The only specific detail in LINQ query is the DateTime? cast inside Max, otherwise EF Core will try to emulate LINQ Max method throwing behavior and will evaluate query client side.

这篇关于如何将此 SQL 查询转换为 EF Core 中的 LINQ 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！