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

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 !

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

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: Can multiple compiled linq queries be chained together?

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.

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.

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