Linq | Date | Subtraction with matching issue

Question

I want to check 1 day old created record(s), and below is my code. my problem is, it does returning any record even if I do have a match.

And even I run it on LinqPad, It just outputing blank result.

from x in Users
where (DateTime.Today - x.CreatedDate).ToString().Equals("1.00:00:00")
select x

but when i try to remove the "where"

from x in Users
select x

here are may result below, as you can see, there is 1 matches:

ToDay : 8/7/2009 12:00:00 AM | CreatedDate : 8/6/2009 12:00:00 AM | Result : 1.00:00:00 | Condition : True 
ToDay : 8/7/2009 12:00:00 AM | CreatedDate : 8/7/2009 12:00:00 AM | Result : 00:00:00 | Condition : False 
ToDay : 8/7/2009 12:00:00 AM | CreatedDate : 8/7/2009 12:00:00 AM | Result : 00:00:00 | Condition : False 
ToDay : 8/7/2009 12:00:00 AM | CreatedDate : 8/7/2009 12:00:00 AM | Result : 00:00:00 | Condition : False 
ToDay : 8/7/2009 12:00:00 AM | CreatedDate : 8/7/2009 12:00:00 AM | Result : 00:00:00 | Condition : False 
ToDay : 8/7/2009 12:00:00 AM | CreatedDate : 8/7/2009 12:00:00 AM | Result : 00:00:00 | Condition : False 
ToDay : 8/7/2009 12:00:00 AM | CreatedDate : 8/7/2009 12:00:00 AM | Result : 00:00:00 | Condition : False 
ToDay : 8/7/2009 12:00:00 AM | CreatedDate : 8/7/2009 12:00:00 AM | Result : 00:00:00 | Condition : False 

NUnit.Framework.AssertionException:   Expected: greater than 0
  But was:  0

I am running this under IQueryable, AddDays and alike are not supported, even though some post below will run on LinqPad, but still not supported by IQueryable on c#.

So may solution is:

IList<Users> users = new List<Users>();
var qry = from x in Users
select x;
foreach(user in qry)
{
if(!DateTime.Today - x.CreatedDate).ToString().Equals("1.00:00:00")) continue;
users.Add(user);
}
..

Answer 1

Have you tried it without the string conversion?

from x in Users
where DateTime.Today.Subtract(x.CreatedDate).Equals(New Timespan(1, 0, 0, 0))
select x;

EDIT: Using Linq to Sql will throw fits on DateTime functions. Alter your select so you're doing the subtract on DateTime and modify the string so it'll look like what SQL Server is producing. I noticed, for example, that my SQL Server adds a leading space character for dates so yesterday is "Aug__5_2009_12:00AM": (spaces changed to underscore so you can see the difference). Anyway, I got this to work in LINQPad against a local database. It's a bit of a kludge, but seems to work.

from x in Users
where x.CheckDate.ToString() == DateTime.Today.AddDays(-1).ToString("MMM dd yyyy hh:mmtt").Replace(" 0", "  ")
select x

Answer 2

How about

from x in Users where (x.CreatedDate < DateTime.Today.AddDays(1).Date && x.CreatedDate > DateTime.Today.AddDays(-2).Date) select x

Answer 3

i have the same problem. I am try

from i in tableConnection where DateTime.Now.Subtract(i.timeout).TotalSeconds > Config.timeoutUser select i

and this doesn't work.

I received this error System.NotSupportedException: Method 'System.TimeSpan Subtract(System.DateTime)' has no supported translation to SQL.