Comparing date parts in LINQ

Question

In LINQ to SQL, how do I compare only the date part of an sql datetime column and .net datetime object?

I know EF doesn't support this translation, but LINQ to SQL might support using the `.Date` property on the DateTime for the comparison. -- just checked, it does. Link posted in my answer. — tvanfosson, Oct 07 '11 at 21:57
In EF, you can use [EntityFunctions.TruncateTime](http://msdn.microsoft.com/en-us/library/dd395596.aspx). See http://stackoverflow.com/questions/4188066/linq-to-entities-group-by-failure-using-date — Jeff Ogata, Oct 07 '11 at 22:16

score 2 · Accepted Answer · answered Oct 07 '11 at 21:58

2

Try using the Date property of both:

Date today = DateTime.Today; // Effectively DateTime.Now.Date

var dataFromToday = from record in context.Records
                    where record.TimeStamp.Date == today
                    select record;

I believe this works for LINQ to SQL... as tvanfosson says, it doesn't for EF.

answered Oct 07 '11 at 21:58

Jon Skeet

1,421,763
867
9,128
9,194

I know that it doesn't. That bit me yesterday. – tvanfosson Oct 07 '11 at 21:59
@tvanfosson: Duly made more emphatic :) – Jon Skeet Oct 07 '11 at 22:01
1

A real pain, too, since I typically mock out the data context for unit tests and it does work with LINQ to objects (obviously). I have to remember that when developing the repository that the unit tests can lie about stuff like that. – tvanfosson Oct 07 '11 at 22:04

score 1 · Answer 2 · answered Sep 14 '14 at 11:20

1

using System.Data.Entity;

DbFunctions.TruncateTime(u.BirthDate) = DbFunctions.TruncateTime(someDate)

answered Sep 14 '14 at 11:20

Toolkit

10,779
8
59
68

score 1 · Answer 3 · answered Oct 07 '11 at 21:59

1

Linq to SQL supports translation of the Date property to SQL for comparisons, etc. More information on the supported options can be found on MSDN.

answered Oct 07 '11 at 21:59

tvanfosson

524,688
99
697
795

score 0 · Answer 4 · edited Sep 13 '12 at 08:12

using System.Data.Objects.SqlClient; //Don't forget this!!

//You can access to SQL DatePart function using something like this:

YourTable.Select(t => new { DayOfWeek = SqlFunctions.DatePart("weekday", t.dateTimeField) - 1 }); //Zero based in SQL

//You can compare to SQL DatePart function using something like this:

DateTime dateToCompare = DateTime.Today;
YourTable.Where(t => SqlFunctions.DatePart("weekday", t.dateTimeField) - 1 == dateToCompare }); //Zero based in SQL

score 0 · Answer 5 · answered Oct 08 '11 at 08:08

0

You could create a CLR UDF to do the date only compare and then reference that in your linq to sql linq query.

answered Oct 08 '11 at 08:08

Frank Tzanabetis

2,756
2
22
22

Comparing date parts in LINQ

5 Answers5