CODEWITHSUNDEEP
c#entity-frameworklinqsqlitedatetime
JumpingJezza
JumpingJezza

Reputation: 5665

Can't compare dates using SQLite with EF6

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??

Upvotes: 2

Views: 1769

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123549

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".)

Upvotes: 1

Ash
Ash

Reputation: 2595

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

Upvotes: 0

Related Questions