Try this code
var today = DateTime.Today;
var q = db.Games.Where(t => DbFunctions.TruncateTime(t.StartDate) <= today);
The Date property is not supported by LINQ to Entities -- you'll get an error if you try to use it on a DateTime field in a LINQ to Entities query. You can, however, trim dates using the DbFunctions.TruncateTime method.
var today = DateTime.Today;
var q = db.Games.Where(t => DbFunctions.TruncateTime(t.StartDate) >= today);
The .Date
answer is misleading since you get the error mentioned before. Another way to compare, other than mentioned DbFunctions.TruncateTime
, may also be:
DateTime today = DateTime.Now.date;
var q = db.Games.Where(t => SqlFunctions.DateDiff("dayofyear", today, t.StartDate) <= 0
&& SqlFunctions.DateDiff("year", today, t.StartDate) <= 0)
It looks better(more readable) in the generated SQL query. But I admit it looks worse in the C# code XD. I was testing something and it seemed like TruncateTime
was not working for me unfortunately the fault was between keyboard and chair, but in the meantime I found this alternative.
I found this question while I was stuck with the same query. I finally found it without using DbFunctions. Try this:
var q = db.Games.Where(t => t.StartDate.Day == DateTime.Now.Day && t.StartDate.Month == DateTime.Now.Month && t.StartDate.Year == DateTime.Now.Year ).OrderBy(d => d.StartDate);
This way by bifurcating the date parts we effectively compare only the dates, thus leaving out the time.
Hope that helps. Pardon me for the formatting in the answer, this is my first answer.
It happens that LINQ doesn't like properties such as DateTime.Date
. It just can't convert to SQL queries. So I figured out a way of comparing dates using Jon's answer, but without that naughty DateTime.Date
. Something like this:
var q = db.Games.Where(t => t.StartDate.CompareTo(DateTime.Today) >= 0).OrderBy(d => d.StartDate);
This way, we're comparing a full database DateTime, with all that date and time stuff, like 2015-03-04 11:49:45.000 or something like this, with a DateTime that represents the actual first millisecond of that day, like 2015-03-04 00:00:00.0000.
Any DateTime we compare to that DateTime.Today
will return us safely if that date is later or the same. Unless you want to compare literally the same day, in which case I think you should go for Caesar's answer.
The method DateTime.CompareTo()
is just fancy Object-Oriented stuff. It returns -1 if the parameter is earlier than the DateTime you referenced, 0 if it is LITERALLY EQUAL (with all that timey stuff) and 1 if it is later.
I found that in my case this is the only way working: (in my application I want to remove old log entries)
var filterDate = dtRemoveLogs.SelectedDate.Value.Date;
var loadOp = context.Load<ApplicationLog>(context.GetApplicationLogsQuery()
.Where(l => l.DateTime.Year <= filterDate.Year
&& l.DateTime.Month <= filterDate.Month
&& l.DateTime.Day <= filterDate.Day));
I don't understand why the Jon's solution is not working ....
Try
var q = db.Games.Where(t => t.StartDate.Date >= DateTime.Now.Date).OrderBy(d => d.StartDate);
DateTime dt=DateTime.Now.date;
var q = db.Games.Where(
t =>EntityFunction.TruncateTime(t.StartDate.Date >=EntityFunction.TruncateTime(dt)).OrderBy(d => d.StartDate
);
Source: Stackoverflow.com