How to have LINQ do a CAST() in SQL Server?

Question

I have a table in SQL Server with an entry that came from:

INSERT INTO table(data) VALUES CAST(getdate() AS BINARY(8))

I would like to do a LINQ query that will perform:

SELECT CAST(data AS DATETIME) FROM table

How can I accomplish this?

The datetime is stored as binary(8) in SQL Server for reasons beyond my control, and I need to retrieve the datetime.

Answer 1

You can use a model defined function to implement the CAST

http://msdn.microsoft.com/en-us/library/dd456857.aspx

See here for an analogous example: Convert String to Int in EF 4.0

Answer 2

I had a similar issue with a sql_variant column.

sql_variant is not supported in LINQ to SQL / EF, hence a trick is needed to get the query generated with a CAST(column as your_desired_type) otherwise LINQ to SQL will throw an exception Unable to cast object of type 'System.Int32' to type '%your desired type%'.

So, first define the column in your model with type object (instead of e.g. string)

[System.Data.Linq.Mapping.Table]
public class CompanyDimensionValue
{
  // type "object" + private setter because Linq to SQL cannot map sql_variant
  [System.Data.Linq.Mapping.Column]
  public object Code { get; private set; }
}

and secondly add a System.Convert.ToString() to the query:

from v in table select
  System.Convert.ToString(v.Code) // <- this generates a CAST(... as nvarchar...)