Entity Framework Getting current date rows(while time in that day)-CodePudding

I have table that the value of dateime is like this

2021-12-08 10:10:54.657
2021-12-08 10:10:41.567
2021-12-08 10:09:51.960
2021-12-08 10:10:54.657
2021-12-08 10:10:41.567
2021-12-08 10:09:51.960
2021-12-08 10:10:54.657

and I want to get that day or today (now is 8 dec 2021) . So i have tried using EF in controller :

 ViewBag.CByUserToday = _db.ArrayDatas.Where(a => a.CreatedBy == user && a.CreatedDate == DateTime.Today.Date).Count();

But i still did not get the rows. When i tried to debug DateTime.Today.Date , it's said DateTime.Today = {09/12/2021 00:00:00} . But when i tried to update that createddate to '2021-12-09 00:00:00.000' it can be retrieved. So, how to retrieve that rows that i have created today(ignoring the time) ?

CodePudding user response：

a.CreatedDate.Date == DateTime.Today.Date

CodePudding user response：

Check that the column is greater than or equal to today, and less than tomorrow:

DateTime today = DateTime.Today;
DateTime tomorrow = today.AddDays(1);
ViewBag.CByUserToday = _db.ArrayDatas
    .Where(a => a.CreatedBy == user 
             && a.CreatedDate >= today 
             && a.CreatedDate < tomorrow)
    .Count();

That way, your DBMS will be able to use a suitable index on the CreatedBy and CreatedDate columns to satisfy your query.

It's usually preferable to avoid calling functions on a column when you're trying to filter it, since this means the query is not SARGable. However, according to this thread on DBA, casting a datetime/datetime2 column to date is SARGable, at least in Microsoft SQL Server.

CodePudding user response：

You can use

linqsource.Where(a => a.Created.Date == DateTime.Today.Date).Count();

To get your count