So how do you query in the text-based Where clause of an LINQ to Entity query? This question drove me up the wall! I’m used to the CONVERT function, or just passing a string, or with a full-on LINQ query, using a DateTime object. With an EntityDataSource, though, you specify the where clause using the Where property which is a string.
First, I couldn’t even figure out how to refer to a column. “IsCancelled” (my column name) didn’t work. The Visual Studio expression editor did not help one bit. Finally, I came across some samples. You need to prefix your columns/properties with “it”. Iterator, I guess. So my clause can say “it.IsCancelled == false” and it works.
The next part was trying to deal with a DateTime. I couldn’t include a date in quotes, surrounded by hash marks, or in any ToString representation. The trick, it turns out, is the CAST function. This isn’t a T-SQL function. This is specific to the framework. You don’t use it like convert, changing to datetime. You actually cast it to System.DateTime. The syntax is: CAST(date_string as System.DateTime). For example: CAST(DateTime.Today as System.DateTime). Not what I expected!
My final Where clause ended up as:
DSEvents.Where = "it.IsCancelled == false and it.startTime > CAST('" + DateTime.Today + "' as System.DateTime)";