CODEWITHSUNDEEP
c#sql-serverentity-frameworkdatetimeedmx
Blaise
Blaise

Reputation: 22222

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

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?

Upvotes: 3

Views: 775

Answers (1)

Krishna
Krishna

Reputation: 1985

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.

Upvotes: 0

Related Questions