The specified cast from a materialized 'System.DateTime' type to the 'System.String' type is not valid

Question

I'm using Entity Framework 5 and when count() is called from below

string sqlStr = @"SELECT top 20 ID, 
               CAST(DOI as DATETIME) as IDate
               FROM DTable";

var results = db.Database.SqlQuery<SRVM>(sqlStr);


//get total count
var total = results.Count();

I get the error:

The specified cast from a materialized 'System.DateTime' type to the 'System.String' type is not valid.

Any ideas why?

Answer 1

It looks like you are placing a DateTime into a String variable in a linq query. I'm also guessing that the first to the query is the "results.Count()" which is actually executing the query and trying to populate whatever object you are populating. If you change the Count() call to any method that will execute the call (ToList(), First(), ...) you will see the same error.

When you are projecting a DateTime into a string match sure you are calling ToString() with a format provider to make the conversion.