why datetime.now not work when I didn't use tolist?

Question

When I use

datacontext.News
    .Where(p => p.status == true)
    .Where(p => p.date <= DateTime.Now)
    .ToList();

the system will return no results;

When I use

datacontext.News
    .Where(p => p.status == true)
    .ToList()
    .Where(p => p.date <= DateTime.Now)
    .ToList();

system will return expected results. Can anyone tell me what's up?

Thanks in advance !

Answer 1

I best guess is that the time settings on your database server differ from that on your developer machine (or the machine you run .NET on).

The difference between the two snippets is that in the second snippet the condition p.date <= DateTime.Now is executed locally, and not on the database server.

If you want to use local time, you can do this:

var now = DateTime.Now;
var newNews = datacontext.News
    .Where(p => p.status == true)
    .Where(p => p.date <= now)
    .ToList();

Answer 2

I think that you need to ask it this way...

datacontext.News
    .Where(p => p.status == true && p.date <= DateTime.Now)
    .ToList();

or

datacontext.News
    .Where(p => p.status && p.date <= DateTime.Now)
    .ToList();

the " == true" is only help to understand a little better what on the question.

Answer 3

Why use multiple .Where() ?

datacontext.News
    .Where(p => p.status && p.date <= DateTime.Now)
    .ToList();

would also work.

This answer may also help you understand why it doesn't work as expected: http://stackoverflow.com/questions/1544006/can-multiple-compiled-linq-queries-be-chained-together/1544288#1544288

Answer 4

Hey,

Its the difference between .NET evaluating date comparisons and SQL; ToList() executes the response and so I think the second where is using LINQ to Objects. For date comparisons, you can also consider SqlMethods

http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods_members.aspx

And so you can use:

SqlMethods.DateDiffDay(d1, d2) > 0

If dates aren't still working for you.

HTH.

Answer 5

Check out result in the debugger to see differences between the database server's time and the local time.

var result = datacontext.News
  .Take(1)
  .Select(n => DateTime.Now)
  .ToList()
  .Select(x => new {SqlTime = x, LocalTime = DateTime.Now})
  .Single();