What is the difference between LINQ research forms with Datetime.Now?

Asked

Viewed 129 times

4

I am running two code snippets and they are giving different results. Does anyone know why?

This code shows coupons only that have the validity of the current date and time + 3h (I don’t know why):

var cupons = Listar().Where(c => c.Validade > Datetime.Now);

While this code shows the coupons correctly: 

Datetime agora = Datetime.Now;
var cupons = Listar().Where(c => c.Validade > agora);

NOTE: The function List query in the database.

c# .net linq datetime

  • As is indicated apenas que possuem a validade da data e hora atual + 3h the problem is the time difference between where the code is executed and the database. Try using Datetime.UtcNow.

  • managed to verify the problem?

  • The problem was the time difference between the server that was running the code and the time of the database (the time zones were different anyway). To solve I used the 2°approach, as it filters the list with the server time (which was the right one). A 1° approach converts Datetime.Now to a select that queries the time of the database. In the case of postgres, Datetime.Now was converted to 'select current_timestamp;' Thank you for your answers!

  • In this case, you can mark the answer as accepted and vote for those you found relevant. What should I do if someone answers my question?

  • Don’t forget to vote on the answers you find relevant and accept the one that solves your problem.

2 answers

6


At first the question was not clear, now I am highlighting this part after editing because this is the problem. The second part is a complement to people better understand LINQ.

Search in a database

If the data is in a database (the question says nothing about it) the and expression will be translated to be executed in the database and depending on the provider used the DateTime.Now will be translated into an expression that it recognizes and will run there in the database that may be with different time zone from the application. None of this is guaranteed, but it happens all the time. When you take an application variable this translation is not possible and then it compares with the fixed data of the moment it is sent to the database.

I even think this is another mistake because the schedules should all be UTC and never have a zone problem, but it’s not the focus of the question. Probably the error is modeling that adopted time zone in storage.

Another common error with LINQ

The first is using an expression that compares a data from the data collection being evaluated to the time of the moment. As time passes this moment changes. At each check of a collection element the value of Datetime.Now changes, then in this example tends to filter a little more by having a movable value, it will narrow.

The second takes the current moment of code execution and stores it in a variable. This value is fixed, it does not change anymore. There when you go through the data collection the comparison is always being made with the same value, the value does not narrow, it does not change during the execution.

But LINQ still has a feature that further aggravates the problem. This technology uses a technique called Lazy Evaluation. When you run this line of code nothing is actually running. Only when you need the data or materialize the data collection will the execution be done. This can be done seconds, minutes or hours later in some cases. Even the time between checking one element and another can take a long time. It’s not so common, but it can happen.

Can you see how the results can be very different? One of them will always use the time of the actual execution of an element, because you used a code that tells you to take a new time each time you pass it. The other takes a time and keeps, there every time evaluating an element even though long after the time to be used is always the same, is the one who picked up right at the beginning of the execution.

I like LINQ, but I see a problem with it because people don’t understand what it is, they think there’s magic inside it and they don’t need to understand any of this "magic" stuff. Without deep understanding of LINQ I think people shouldn’t use it.

If you write these codes in your hand maybe you could understand better why this happens:

foreach (var c in Lista()) {
    if (c.Validade > DateTime.Now) {
        //faz alguma coisa
    }
    //talvez chama algo aqui demorado
}

And

var agora = DateTIme.Now;
foreach (var c in Lista()) {
    if (c.Validade > agora) {
        //faz alguma coisa, poderia montar uma outra lista, mas seria a materialização
    }
    //talvez chama algo aqui demorado
}

I put in the Github for future reference.

LINQ is not simple

And one of the problems I see most people do with LINQ is to use a ToList() to materialize the data collection. When a person does this they probably shouldn’t use LINQ, the advantage of this technique is precisely not to materialize needlessly and if ever needed, then LINQ doesn’t help as much as it could. In case you are probably accessing the data collection without materializing for nothing and this is good, but it is also the cause of the difference.

Completion

Which gives the expected result? Go with it. I think the second is better in most cases, but I see scenario where the first is most useful. Or fix the modeling error.

  • It was the time zone problem with the database! I forgot to specify in the question that the query was in a database, I already edited it! Thank you!

3

I imagine that Listar execute a query in the database. If so, when Where, Line adds the conditions to the query sent to base, which nay must have the Brazilian time zone (-3 hours).

In the other case, the date added to the query is that of your computer, which must have the time zone.

One way to avoid this kind of problem is to always use the dates on UTC and convert them only when necessary.

Browser other questions tagged c# .net linq datetime

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