Timout when searching between 2 date intervals

Asked

Viewed 377 times

2

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

Obs: are fields Datetime

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

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

Run Time Out. The time limit has expired before completion of the operation or the server is not responding.

c# sql-server lambda-expressions

  • Timeout usually indicates a valid query. Does your table have many records? It has index in this date field?

  • When I do the same query without Truncatetime, I can do the queries. However, because it is Datetime, some records stop coming in this list.

  • These functions are mapped internally on each provider to other functions and it may be that this is slowing down enough to give the timeout. Already tried to use the start date with time "00:00:00" and the end with time "23:59:59" and compare directly instead of truncating the field Date Registration for Date?

  • @Pagotti, I didn’t get to compare that way, but I’ll test.

  • @Pagotti, would be setting a timeSpan and concatenating with the date?

  • Timespan ts = new Timespan(23, 59, 59); (p => p.Dataregistration>= stardate.Date + ts)

  • It may be so but does it outside lambda function

Show 2 more comments

2 answers

2


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

Since the error is timeout and the Truncatetime function is translated internally by the database into something that cannot be determined by the text of the question, it is likely that something in this transformation may be slowing down the query, without being able to use any index, and so the timeout occurs.

Thus, an attempt is to continue making a comparison with 2 values of type Datetime putting the fixed time at the extremes of the day.

  • Thank you, @Pagotti

-1

Try it this way:

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

  • That way it won’t. It was the first one I tested.

  • What happens?

  • "The specified type Member 'Date' is not supported in LINQ to Entities. Only initializers, Entity Members, and Entity navigation properties are supported."

  • 2

    @Pedropaulo Entityfunctions is for EF versions smaller than 5. The only thing that changes is the function itself, it would never cause a timeout.

Browser other questions tagged c# sql-server lambda-expressions

You are not signed in. Login or sign up in order to post.