CODEWITHSUNDEEP
c#datetimelinq-to-sql
navig8tr
navig8tr

Reputation: 1844

How do I use LinqToSql to query on specific datetime

When using Linq2Sql to query the database on a specific datetime, the query always comes back with no results. I suspect the times are off by nanoseconds causing the difference. The times must be equal but not that equal. How do I query the database for a datetime, down to just the second for precision?

The following code works but it excludes the time portion altogether:

        var Foos = Bars
                .Where(x =>
                    DbFunctions.TruncateTime(x.EndDate) == DbFunctions.TruncateTime(endDate))
                .Select(x => x.Id);

Also tried this code (which isn't pretty) but the compiler complained about having Date inside a linq query.

        var Foo = Bars
                .Where(x => 
                        x.EndDate.Value.Date == endDate.Value.Date &&
                        x.EndDate.Value.Hour == endDate.Value.Hour &&
                        x.EndDate.Value.Minute == endDate.Value.Minute &&
                        x.EndDate.Value.Second == endDate.Value.Second)
                .Select(x => x.Id);

Also tried this, but the query always came back empty. Possibly ToString() not working in the Linq query.

var foo = bars.Where(x => x.EndDate.ToString() == endDate.ToString())

Upvotes: 2

Views: 69

Answers (2)

Anil Garlapati
Anil Garlapati

Reputation: 227

A way around is to compare date part and hour part separately. Instead of trying to cast , lets divide and conquer

Suppose you have the data stored in DB as below 

Pickup Time :10/9/2015 12:53:15 AM

and if you want to compare it present system time up to hours, the below query works

 orddb.order.Where(x.PickupDate.Hour >= DateTime.Today.Hour 
                    && x.PickupDate >= DateTime.Today)

Upvotes: 0

Yacoub Massad
Yacoub Massad

Reputation: 27861

You can use the DiffSeconds method like this:

var Foo = Bars
       .Where(x => DbFunctions.DiffSeconds(x.EndDate, endDate) == 0)
       .Select(x => x.Id);

Upvotes: 1

Related Questions