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.

CodePudding user response：

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

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();