Resolved: Get different results with the same query in T-SQL and LINQ (EF Core)

Question:

I’m trying to retrieve records from a database in Azure and for the checks I run T-SQL queries directly and then pass them to LINQ against the EF Core context, but I’m running into this problem.

select FechaOrientativa,id, Archivo, Estado, Estudiar, Descripcion
from Concursos
where FechaOrientativa>=CAST( GETDATE() AS Date ) and Estudiar='pt'
order by FechaOrientativa, Archivo, Estado

When I filter for the records with FechaOrientativa greater than or equal to Today, the Estudiar field is equal to ‘pt’, I get 2,296 records.
Now in Angular, I do http.Get to my Web API where I execute the following:

[HttpGet("sintratar")]
public async Task<ActionResult<IEnumerable<Concurso>>> GetConcursosSinTratar()
{
    return await _context.Concursos.Where(c => c.Estudiar == "pt" && c.FechaOrientativa >= DateTime.Now).OrderBy(c => c.FechaOrientativa).ToListAsync();
}

And to my surprise, I receive only 2,151 records and I can’t find an explanation.
Any idea, please?
Thanks.

Answer:

Compare both SQL query and EF LINQ query, the difference is:
SQL – CAST( GETDATE() AS Date ) returns Today Date without time.
EF LINQ – DateTime.Now returns current Date Time.
Hence the queried result will be different
(Example: Only queried records with the date-time field equal/after the query date-time).
From Date and time functions, you are looing for DateTime.Today


DateTime.Today	CONVERT(date, GETDATE())

return await _context.Concursos
    .Where(c => c.Estudiar == "pt" && c.FechaOrientativa >= DateTime.Today)
    .OrderBy(c => c.FechaOrientativa)
    .ToListAsync();

Or you can use the SQL query in EF LINQ with .FromSqlRaw().

return await _context.Concursos
    .FromSqlRaw(@"select FechaOrientativa,id, Archivo, Estado, Estudiar, Descripcion
        from Concursos
        where FechaOrientativa>=CAST( GETDATE() AS Date ) and Estudiar='pt'
        order by FechaOrientativa, Archivo, Estado")
    .ToList();

If you have better answer, please add a comment about this, thank you!

Resolved: Get different results with the same query in T-SQL and LINQ (EF Core)

Resolved: Vaadin 14.9 – Redirect on Session Destroy Event

Resolved: How to separate multiple answers in one column, for multiple columns, by creating extra columns

Resolved: How to set consistent decimal separators in R data frame?

Leave A Reply