Parse string as DateTimeOffset in Expression<Func<T, bool>>

Question

Hey, I am trying to create an Expression<Func<T, bool>> in which a string property is converted/cast to a DateTimeOffset so that DateTimeOffset operations can be performed on it.

We are using Linq2SQL as our data provider, which does support converting strings to the SQL equivalent of DateTimeOffset. Ideally I want the expression to be evaluated directly inside the IQueryable data source, and not in memory as an IEnumerable.

See below for an example of what I have tried to far:

public class MyClass
{
    public string MyValue;
}

Expression<Func<MyClass, bool>> filter = mc => DateTimeOffset.Parse(mc.MyValue) > new DateTimeOffset(2007,1,1);

Unfortunately this definition of filter causes an in memory evaluation.

Answer 1

Since the underlying physical table column is nvarchar not datetime, I think you are out of luck. The LINQ2SQL provider knows that it is nvarchar data, and therefore will not generate native SQL expressions that deal with it as a datetime value.

To be honest, I can't even see a safe way of doing this with raw hand-crafted SqlCommand objects. You really ought to read the data into memory as String, then convert it to DateTimeOffset, then manually filter it in memory. If you want native SQL filtering on this column, you need to change the table schema.