无法使用SQLite与EF6比较日期 [英] Can't compare dates using SQLite with EF6
问题描述
我试图在SQLite数据库中使用Linq与实体比较日期。以下代码工作,但是我需要修剪时间部分以获得正确的结果。
return(from c in Context .Car
加入d在C上的Context.Driver等于d.DriverID
在上下文中加入r在c.CarID上加载r等于r.RideID到rideJoin
从rideJoin.DefaultIfEmpty ()
其中c.IsActive&& d.IsActive
组乘以新的{c.CarID,d.FullName,d.HireDate,d.FirstPayRiseDate}到grp
中选择新的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() ;
我试过使用 System.Data.Entity.DBFunctions 像这样,我得到这个错误:
NumberOfRides = grp.Count(x => x!= null&& DbFunctions.TruncateTime(x.RideDate)> = grp.Key.HireDate&& DbFunctions.TruncateTime(x.RideDate)< = grp.Key.FirstPayRiseDate)
pre>
SQL逻辑错误或缺少数据库没有这样的功能:TruncateTime
我也遇到与DBFunctions相同的错误.DiffDays()
我也试过转换为日期如此,并得到此错误:
NumberOfRides = grp.Count(x => x!= null& & x.RideDate.Date> = grp.Key.HireDate&& x.RideDate.Date< = grp.Key.FirstPayRiseDate)
LINQ to Entities不支持
'Date'。只支持初始值设定,实体成员和实体导航属性。
什么给了?我应该使用SQLite在Linq到Entities中执行Date函数?
解决方案
我需要修剪时间部分以获得正确的结果
不,你没有。如果你想通过
endDate
包含startDate
中的行,那么只需使用...&& x.RideDate> = startDate&&& x.RideDate< endDate.AddDays(1)
(请注意,第二个比较现在是严格小于)
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??
解决方案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
throughendDate
inclusive then just use... && x.RideDate >= startDate && x.RideDate < endDate.AddDays(1)
(Note that the second comparison is now "strictly less than".)
这篇关于无法使用SQLite与EF6比较日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!