Entity Framework - DateTime in the where clause
I'm trying to convert this code from a stored procedure into a "LINQ to Entities" query:
select * from myTable
where venue_date + start_time <= @EndDate
and dateadd(minute, duration * 24 * 60, venue_date + start_time) >= @StartDate
As you can see there is some manipulation of dates. This sort of thing won't work because functions such as .AddHours()
are not supported:
where b.Venue_Date.Value.AddHours(b.Start_Time.Value.Hour) <= request.EndDate
Something like this won't work either. The error says something about not supporting constructors with parameters:
where new DateTime(b.Venue_Date.Value.Year, b.Venue_Date.Value.Month,
b.Venue_Date.Value.Day, b.Start_Time.Value.Hour, b.Start_Time.Value.Minute,
b.Start_Time.Value.Second) 开发者_如何学编程<= request.EndDate
It seems that the Entity Framework is VERY restrictive when dealing with dates. I guess I'll have to use a procedure. The next problem is figuring out how to make a procedure that can populate several related object collections.
Is changing the schema an option? My experience is that when storing separate columns like:
- Date
- Start Time (as a date)
- Stop time (as a date)
...separately, it's a huge advantage to store the date redundantly in the Date
and StartTime
columns. Not the least because it makes your query (1) easier to write, and (2) the query can now use an index.
I've done this both ways, and I much prefer this method.
精彩评论