Creating DbContext with both custom ConnectionString and DbProviderInfo

Question

We have a plethora of databases all sharing the same schema. They are spread about on a number of SQL Servers, some 2005, some 2008. We are getting an exception when connecting to a 2005 server AFTER having connected to a 2008 server first stating the following:

An error occurred while updating the entries. See the inner exception for details. An error occurred while updating the entries. See the inner exception for details. The version of SQL Server in use does not support datatype 'datetime2'.

So as I understand it what is happening is the Model is being created according to a 2008 SQL servers provider info, and thus when connected to a 2005 server, it can't use the DateTime2 data type because that is not supported there.

We would like to build the model against a 2005 server, but I am unsure of how to specify both the connection string AND the DbCompiledModel. Previously I have had a couple of constructors that take the connection string (among other parameters)...

    public ProjectContext(string ConnectionString) : base(ConnectionString)
    {

        // Get the ObjectContext related to this DbContext
        var objectContext = (this as IObjectContextAdapter).ObjectContext;

        // ... does some stuff with the ObjectContext
    }

How can I specify the compiled model built against a 2005 server for ALL contexts (lowest common denominator) and also specify the connection string? I just don't see a way to do it at the moment.

Any help would be appreciated.

Edit: We are using Entity Framework 4.3 Code First, thus, there is not a design time EDMX file to edit.

CodeWarrior · Accepted Answer · 2012-09-06 02:51:59Z

There is an overload for the base class constructor!

I copied my OnModelCreating configuration stuff into a new method that returns DbCompiledModel named BuildModel. When my constructor that has ConnectionString constructor is called, I get the value of dbModel, a private readonly property which if null, gets set to the value returned from BuildModel. The property value gets fed to the base class constructor along with the connection string.

I have access to the SQL server versions that I am connecting to. Eventually I will bring that in and create one model for 2005 and one for 2008.

I will leave this open for a while just in case there is a better way to do this.

public DbContext ProjectContext (string ConnectionString) 
    : base(ConnectionString, dbModel)
{

}

private DbCompiledModel _dbModel;
private DbCompiledModel dbModel;
{
    get
    {
        if (_dbModel == null)
        {
            _dbModel = BuildModel(new DbModelBuilder());
        }
        return _dbModel;
    }
}

    private DbCompiledModel BuildModel(DbModelBuilder builder)
    {
        //Some configuration stuff
        return builder.Build(new DbProviderInfo("System.Data.SqlClient", "2005"))
                      .Compile();
    }

StuartLC · Accepted Answer · 2012-09-05 15:48:25Z

1

To answer the first question, you need to edit the edmx xml file directly, and then change the ProviderManifestToken attribute from 2008 to 2005, on the <Schema> element at the top of the file.

AFAIK you will need to do this for each of your .edmx files, and the worse news is that the Model update wizard will reinstate the ProviderManifestToken every time you update the Model from the database, in accordance with the SQL version that the EF connection uses. AFAIK the EF wizard uses the connection string in the local assembly containing the edmx, so if you change that connection string to point to a SQL 2005 DB you should be OK going forward.

answered Sep 5, 2012 at 15:48

StuartLC

108k18 gold badges225 silver badges294 bronze badges

1 Comment

CodeWarrior Over a year ago

One clarification, and I am going to add this to my question above too, I am using EF4.3 Code First, so, to my knowledge, no EDMX...

Collectives™ on Stack Overflow

Creating DbContext with both custom ConnectionString and DbProviderInfo

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related