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

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 and EF LINQ queries, the difference is:

From	Query/Code	Result
SQL	CAST( GETDATE() AS Date )	Returns current Date without Time.
EF LINQ	DateTime.Now	Returns current Date with Time.

Hence the queried results are different.

(Example: Only queried records with the date-time field equal/after the query date-time).

From Date and time functions, you are looking 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 Core 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();