Timing when querying between 2 date ranges

Question

Timing when querying between 2 date ranges

Navigation

#1 by (3 votes)
#2 by (-1 votes)

3

I have a query where I query between 2 date ranges. On these dates, I apply DbFunctions.TruncateTime to filter searches for dates only, ignoring the time.

Note: these are DateTime fields

.Where(p => DbFunctions.TruncateTime(p.DataCadastro) >= DbFunctions.TruncateTime(dataInicial) && 
            DbFunctions.TruncateTime(p.DataCadastro) <= DbFunctions.TruncateTime(dataFinal));

However, when using DbFunctions.TruncateTime the request always returns me:

Expired Execution Timeout. Timeout ended before the operation is completed or the server is not responding.

sql-server c#

asked by anonymous 03.09.2018 / 20:58

2 answers

-1

Try this:

.Where(p => DbFunctions.TruncateTime(p.DataCadastro) >= dataInicial.Date
         && DbFunctions.TruncateTime(p.DataCadastro) <= dataFinal.Date);

03.09.2018 / 21:10

How to get the date and time of the day with LocalDateTime How to validate the JSON schema in C #?

score 3 · Accepted Answer

One suggestion:

var dataIni = dataInicial.Date + new TimeSpan(0, 0, 0);
var dataFin = dataFinal.Date + new TimeSpan(23, 59, 59);

expressao.Where(p => p.DataCadastro >= dataIni && 
                     p.DataCadastro <= dataFin);

Because the error is timeout and the TruncateTime function is internally translated by the database into something that can not be determined by the query text, it is likely that something in this transformation might be leaving the query too slow, unable to use some index, and so timeout occurs.

So, one attempt is to continue making a comparison with 2 values of type DateTime by setting the fixed time at the ends of the day.