Compare dates linq c#

Question

I cant seem to get my query to check the database if a date exists. Please help.

 DateTime value = new DateTime(2018, 10, 26);
 item = await table.Where(todoItem => todoItem.Date == value.date)
                   .ToListAsync();

I think the format which c# writes their dates and SQL are different so it cant find a match. Also the database is correctly linked to the app as I am able to query other rows in the same table.

`todoItem.Date` might be including a time that `value` is not including. It's often a good idea to force both dates to midnight for comparison. — theGleep, Nov 13 '17 at 20:17
@StevenWexler it works perfectly if I query the name for instance todoItem.Date == "Steven" so Im unsure as to why it isnt working. The data type ive given the 'date' field is DATE — , Nov 13 '17 at 20:22
@theGleep forcing row dates to midnight is horrible for performance (basically can't use indexes), [basically causes the sql to be non-sargable](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). — Erik Philips, Nov 13 '17 at 20:22
@theGleep the data type I'm using for the date field is 'DATE' instad of 'DATETIME' — , Nov 13 '17 at 20:23
That makes it less likely - but now we go to @StevenWexler's first comment. — theGleep, Nov 13 '17 at 20:24
@theGleep the code appears to send the *comparison* or *query* to the db to run. It does not run client side. — Erik Philips, Nov 13 '17 at 20:24
@JohnDoe just include the table structure so there's no ambiguity. — Steven Wexler, Nov 13 '17 at 20:24
@ErikPhilips ... argh! That's not what the code *looks* like - but you're right. double-argh! — theGleep, Nov 13 '17 at 20:25
@JohnDoe I assume this is [tag:linq-to-sql] or [tag:entity-framework]? — Erik Philips, Nov 13 '17 at 20:26

score 3 · Answer 1 · edited Nov 13 '17 at 20:36

3

As said before, this is likely due to the fact that your DateTime column in the database includes time information.

Rewrite your query like this:

DateTime value = new DateTime(2018, 10, 26);
item = await table.Where(todoItem => todoItem.Date >= value &&
                                     todoItem.Date < value.AddDays(1))
                   .ToListAsync();

This will retrieve all records that fall within the specified range (that is, dates that match the given day, and the DBMS will be able to use indexes that might be defined on the date-column.

edited Nov 13 '17 at 20:36

Igor

60,821
10
100
175

answered Nov 13 '17 at 20:27

Frederik Gheysels

56,135
11
101
154

Thank Frederik for your answer but it is still returning nothing for me. – Nov 13 '17 at 20:51
You sure you have records in your table that match the criteria ? – Frederik Gheysels Nov 13 '17 at 20:53
The date type for the sql is 'DateTime' and the type for the c# is DateTime. I entered the date as above and the way the day is displayed in the database is "2018-10-26 00:00:00.000". Please let me know if I am doing anything wrong – Nov 13 '17 at 21:03
Remove the `.Date` parts of the code sample - they are not needed. – mjwills Nov 13 '17 at 22:18
1

@JohnDoe If you run `Select * from TableName WHERE ColumnName >= '20181026' and ColumnName < '20181027'` directly against the database (not through your app) is your row returned then? – mjwills Nov 13 '17 at 22:24

score 0 · Answer 2 · answered Nov 13 '17 at 20:28

Similar to Brandon's answer.. this is most likely happening due to todoItem.Date including the time portion of the Date property.

I assume the property Date is of type DateTime, so you could do this:

DateTime value = new DateTime(2018, 10, 26);
item = await table.Where(todoItem => DbFunctions.TruncateTime(todoItem.Date) == 
  value).ToListAsync();

DbFunctions.TruncateTime

When used as part of a LINQ to Entities query, this method invokes the canonical TruncateTime EDM function to return the given date with the time portion cleared.

Here is more information on DbFunctions.TruncateTime.

Let me know if this helps!

This will most likely prevent any indexes on the date-column to be used. — Frederik Gheysels, Nov 13 '17 at 20:39

score -1 · Answer 3 · edited Nov 13 '17 at 20:23

-1

This is most likely caused by including a time in your dates, and not formatting them to just the actual date string. You can resolve this by using the .toLocaleString() method. Something like this should work:

DateTime value = new DateTime(2018, 10, 26);
item = await table.Where(todoItem => todoItem.Date.toLocaleString() == 
  value.date.toLocaleString())
  .ToListAsync();

edited Nov 13 '17 at 20:23

Erik Philips

53,428
11
128
150

answered Nov 13 '17 at 20:22

Brandon Miller

1,534
1
11
16

This will prevent the engine from using any Index (worst performance), [non-sargable query](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). – Erik Philips Nov 13 '17 at 20:23
Please don't convert your date to a string in order to compare them ;... – Frederik Gheysels Nov 13 '17 at 20:26
'DateTime' does not contain a definition for 'toLocaleString' and no extension method 'toLocaleString' accepting a first argument of type 'DateTime' could be found (are you missing a using directive or an assembly reference?) – Nov 13 '17 at 20:27

Compare dates linq c#

3 Answers3