lundi 28 septembre 2015

Multiple Database Providers in single application on single model

I am currently working on an application which was build around EF4 connecting to MSSQL. Now it was decided, that we should also be able to connect to SQLite.

I've upgraded the application to EF6, made sure everything works as intended with MSSQL and am now trying to get the application to connect to an SQLite DB (which is a converted version of our mssql db).

Sadly I've kinda hit a wall here.

If I try to create the SQLite connection the same way we do for MSSQL

            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
            entityBuilder.Provider = "System.Data.SQLite.EF6";
            entityBuilder.ProviderConnectionString = "data source=sqlite_master.db";
            entityBuilder.Metadata = @"http://res*/Model.csdl|res*/Model.ssdl|res*/Model.msl";                                
            connection = new EntityConnection(entityBuilder.ToString());
            connection.Open();

            theEntities = new masterEntities(connection); // masterEntities extends System.Data.Entity.DbContext
            getDataBaseVersion();

I get an Exception stating that "System.Data.SQLite.SQLiteConnection" cannot be converted to "System.Data.SqlClient.SqlConnection" in the getDataBaseVersion Method (which is the first one to run a query on the entities).

I've already tried out a few things, but always got either this or an "unintended code first exception".

Btw: I don't really need to create the connection strings for the SQLite version at runtime. If the problem can be fixed by using the config file, I'll gladly do it.

My app.config looks like this:

  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data   Provider for SQLite"
      type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>

My guess is that somewhere within the entity data model the data.sqlclient class is set to be used. But I can't create a second Entity Data Model, as then all classes and properties would be definded twice. (I also can't because the sqlite data source won't show up in the assistent for entity data model, but I hope to be able to fix that by reinstalling everything)

Now I wonder: Is it even possible to use two different providers within the same application for the same model? If yes, I'd be very happy if someone could point me towards a solution to get it working.

Aucun commentaire:

Enregistrer un commentaire