2

I'm trying to compare dates using Linq to Entities on a SQLite database. The following code works, but I need to trim off the time portion to get the correct results.

return (from c in Context.Car
        join d in Context.Driver on c.CarID equals d.DriverID
        join r in Context.Rides on c.CarID equals r.RideID into rideJoin
        from rides in rideJoin.DefaultIfEmpty()
        where c.IsActive && d.IsActive 
        group rides by new { c.CarID, d.FullName, d.HireDate, d.FirstPayRiseDate } into grp
        select new MyCustomClass
        {
            CarID = grp.Key.CarID,
            Driver = grp.Key.FullName,
            NumberOfRides = grp.Count(x => x != null && x.RideDate >= grp.Key.HireDate && x.RideDate <= grp.Key.FirstPayRiseDate)
        }).OrderBy(x => x.Driver ).ToList();

I've tried using System.Data.Entity.DBFunctions like so and I get this error:

NumberOfRides = grp.Count(x => x != null && DbFunctions.TruncateTime(x.RideDate) >= grp.Key.HireDate && DbFunctions.TruncateTime(x.RideDate) <= grp.Key.FirstPayRiseDate)

SQL logic error or missing database no such function: TruncateTime

I also get the same error with DBFunctions.DiffDays()

I've also tried casting to Date like so and get this error:

NumberOfRides = grp.Count(x => x != null && x.RideDate.Date >= grp.Key.HireDate && x.RideDate.Date <= grp.Key.FirstPayRiseDate)

'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported

What gives? How am I supposed to do Date functions in Linq to Entities with SQLite??

JumpingJezza
  • 5,498
  • 11
  • 67
  • 106
  • SQLite does not have a time-truncate function named `TruncateTime`, instead use the intrinsic `DATE()` function to retrieve the `date` portion of a `datetime` value. – Dai Oct 15 '15 at 01:10
  • @Dai How do you do that in linq? – JumpingJezza Oct 15 '15 at 01:12
  • Perhaps @Dai means to create a view in SQLite with a column that uses DATE() to isolate the date part, then use the view instead of the table. I do see what you mean re: `RideDate.Date` in LINQ to Entities; it works fine for me with LINQ to SQL. Maybe another possibility might be to tweak your Ride object to add a date-only property as `RideDate.Date`, then use that in your LINQ expressions. (Just a guess, though.) – Gord Thompson Oct 15 '15 at 16:15
  • Also, [this question](http://stackoverflow.com/q/2264714/2144390) looks similar. Perhaps it might help. – Gord Thompson Oct 16 '15 at 01:47
  • @GordThompson [That question](http://stackoverflow.com/questions/2264714/entity-framework-object-limitations-in-aggregate-linq-query) helped me with 1 of my queries. Unfortunately to compare 2 of the dates for another query it involved pulling back too much of the database. Also, I needed to do a ToList() on the 1st part or I got the same error. – JumpingJezza Oct 16 '15 at 11:40
  • @GordThompson I have 8 of these queries that involve dates in some way and the last 1 I ended up having to create a view that compared the dates using DATE() (as you and Dai suggested) and then returning that set so I could further search on it. Ridiculously ineffective but at least it worked without throwing exceptions. – JumpingJezza Oct 16 '15 at 11:41

2 Answers2

1

I need to trim off the time portion to get the correct results

No you don't. If you want to include the rows from startDate through endDate inclusive then just use

... && x.RideDate >= startDate && x.RideDate < endDate.AddDays(1)

(Note that the second comparison is now "strictly less than".)

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • This is the solution I came up with as I was writing the question. This is a workaround though, surely I should be able to do some date manipulations? – JumpingJezza Oct 15 '15 at 01:18
  • Yes but it's a good workaround since it increases the chances that the WHERE conditions of the underlying SQL query will be [sargable](http://en.wikipedia.org/wiki/Sargable). – Gord Thompson Oct 15 '15 at 01:23
  • Sargable is good. However this won't work when I'm trying to compare 2 database dates. I'll update the question to reflect this. – JumpingJezza Oct 15 '15 at 01:36
0

How are you storing dates on the database ? as unix time integrs ? in that acse you can amend your connection string to include this following config setting and it will make it easy to read the datetime value via EF.

datetimeformat=UnixEpoch;datetimekind=Utc

So something like :

data source=&quot;|DataDirectory|\data.sqlite&quot;;datetimeformat=UnixEpoch;datetimekind=Utc

Ref: https://stackoverflow.com/a/24323591/3660930

Community
  • 1
  • 1
Ash
  • 2,575
  • 2
  • 19
  • 27