How to force entity framework to use datetime instead of datetime2 for stored procedure call?

Question

There is a datetime input in my stored procedure.

When edmx is built with database first approach, we have a

((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("spInsert", ... , payDateParameter);

And the payDateParameter is defined as:

var payDateParameter = payDate.HasValue ?
    new ObjectParameter("PayDate", payDate.Value) :
    new ObjectParameter("PayDate", typeof(System.DateTime));

where payDate is a DateTime?.

The exception:

The version of SQL Server in use does not support datatype 'datetime2'.

I understand there is a range limit in datatime data type. So a minimum value is added for another try.

var payDateParameter = payDate.HasValue ?
    new ObjectParameter("PayDate", payDate.Value) :
    new ObjectParameter("PayDate", new DateTime(1753,1,1));

Still the same exception.

Is there a way to force stored procedure call to use my payDateParameter as a datatime type instead of datetime2?

Is it possible to cast to `datetime` in the stored procedure? Like [this](http://stackoverflow.com/questions/11081571/how-to-cast-datetime2-as-datetime)? — Timothy G., Apr 28 '17 at 14:01
I think the edmx file you created is with one version of sql server and you are trying to access another version of sql server with it. Can you check — Krishna, Apr 28 '17 at 14:04
@Krishna is correct. The edmx is created from a local database, but the production database is on a server with VPN tunnel. We are unable to reach it from local dev machine. — Blaise, Apr 28 '17 at 14:09

score 0 · Accepted Answer · answered Apr 28 '17 at 14:13

You need to open edmx in text editor and change the below

<Schema Namespace="CPTTModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store=....

The important thing to look at is the “ProviderManifestToken” parameter. According to Microsoft, this parameter helps the entity model function while not connected to the database, I think it also helps best structure the queries it generates against the target database. The tricky thing is that every time you update the data model, even to move an object around in the display editor, this value is updated and saved to the database it is connected to.

How to force entity framework to use datetime instead of datetime2 for stored procedure call?

1 Answers1