开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜