How to translate this SQL query to a LINQ query in EF Core?

Question

I have the following table :

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

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

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
)

and this is the entity :

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; }
 }

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

How can a record be greater than the `MAX(TimeInsertedLocal)`? — SS_DBA, Apr 23 '19 at 16:34
TimeInsertedLocal is the DateTime at which the rows was inserted. — Ondama papaoutai, Apr 23 '19 at 16:39

score 5 · Accepted Answer · answered Apr 23 '19 at 17:43

It's literally one to one translation.

SQL query

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

EF Core LINQ query:

var indicators = dbContext.Set<Indicator>();
var query = indicators
    .Where(i => i.TimeInsertedLocal >= indicators.Max(i2 => (DateTime?)i2.TimeInsertedLocal));

EF Core generated SQL query:

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]
)

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.

Yes you nailed it. and without that cast there is two round-trips to the database server that is is weird. — Ondama papaoutai, Apr 24 '19 at 09:52

score 2 · Answer 2 · answered Apr 23 '19 at 19:41

2

Of course there are no indicators with a value of TimeInsertedLocal that is larger than the largest value of TimeInsertedLocal of all Indicators.

However, it might be that you have several indicators with a value equal to the largest value of TimeInsertedLocal.

If that is the case, you need to make groups of Indicators with the same TimeInsertedLocal, and take the group with the largest value.

var indicatorsWithLargestTimeInsertedLocal = myDbContext.Indicators
    // make groups of Indicators with same TimeInsertedLocal:
    .GroupBy(indicator => indicator.TimeInsertedLocal)

    // put the group with the largest TimeInsertedLocal first:
    .OrderByDescending(group => group.Key)

    // The first group of indicators, is the group with the largest value of TimeInsertedLocal
    .FirstOrDefault();

If you are certain the TimeInsertedLocal is unique, you don't have to GroupBy, there will only be one indicator with the largest TimeInsertedLocal

var indicatorWithLargestTimeInsertedLocal = myDbContext.Indicators
    .OrderByDescending(indicator => indicator.TimeInsertedLocal)
    .FirstOrDefault();

answered Apr 23 '19 at 19:41

Harald Coppoolse

28,834
7
67
116

This brings all the data in the table and then filter them in the app. I need this to be done in the database level. – Ondama papaoutai Apr 24 '19 at 09:50
What make you think so? If myDbContext.Indicators is`IQueryable`, then GroupBy, OrderByDescending and FirstOrDefault will be executed `AsQueryable`, meaning that they will be executed by the DBMS – Harald Coppoolse Apr 24 '19 at 11:54
1

@HaraldCoppoolse Currently EF Core `IQueryable` does not guarantee DBMS execution - see [Client vs. Server Evaluation](https://learn.microsoft.com/en-us/ef/core/querying/client-eval) and [How Queries Work](https://learn.microsoft.com/en-us/ef/core/querying/overview). From your postings looks like you have little or no experience with EF Core. EF Core is completely different system than EF6, so you can't apply your knowledge from EF6 there. – Ivan Stoev Apr 24 '19 at 12:21
You are right, I tried it on entity framework. Didn't pay attention that it was core – Harald Coppoolse Apr 24 '19 at 14:41

How to translate this SQL query to a LINQ query in EF Core?

2 Answers2

Linked