EF: How to compare today date for filter the data

Question

I am using Entity Framework with MYSQL database. I am trying to get result from following query.

select COUNT(*) from tbldata where CreatedBy is not null and created is not null and DATE(Created) = DATE(NOW())

Above query returned accurate result. I have tried this query in E.F but it returns zero Count.

var todayDate = DateTime.Now.Date;

_db.TblData.Where(c => c.CreatedBy != null && c.Created != null && c.Created == todayDate)
                .Count();

How can I compare only date value in E.F?

Thank you

`c.Created.Date == DateTime.Now.Date` is `Created` of type `DateTime`? — Sebastian Siemens, May 26 '16 at 08:05
Yes, I have tried this one too "c.Created.Value.Date == todayDate". it throws exception "The specified type member 'Date' is not supported in LINQ to Entities" — Shoaib Ijaz, May 26 '16 at 08:09
Then try this: `EntityFunctions.TruncateTime(c.Created) == DateTime.Now.Date` — Sebastian Siemens, May 26 '16 at 08:12
Please note: Since EF6 `EntityFunctions` is obsolete and is replaced with `DbFunctions` — Sebastian Siemens, May 26 '16 at 08:15
I am using latest version of EF. this time thorws exception "FUNCTION dbname.TruncateTime does not exist" — Shoaib Ijaz, May 26 '16 at 08:19
http://stackoverflow.com/questions/28381268/the-specified-type-member-date-is-not-supported-in-linq/30803487#30803487 — Sebastian Siemens, May 26 '16 at 12:05

sumngh · Answer 1 · 2016-05-26T08:45:31.910

0

try to use like this, extension methods not work for linq.

   _db.TblData.Count(c => c.CreatedBy != null && c.Created != null && c.Created.Date == DateTime.Today);

edited May 26 '16 at 08:45

answered May 26 '16 at 08:32

sumngh

566
2
10

"Created" Column already DateTime Type. – Shoaib Ijaz May 26 '16 at 08:37
If that is the case than it must have time value in it, and if it has time value that you cannot compare it with date value as it will always be false. – sumngh May 26 '16 at 08:41
I have updated my answer please can you verify it now. – sumngh May 26 '16 at 08:45
did that work for you? – sumngh May 26 '16 at 09:08

EF: How to compare today date for filter the data

1 Answers1