如何在EF Core中将此SQL查询转换为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核心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?

推荐答案

这实际上是一对一的翻译.

It's literally one to one translation.

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?强制转换，否则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.

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