How to change the database - Schema used by Entity Framework (mysql database) ?

Question

I use EntityFramework in a project to connect to a Mysql database. The provider is Devart Dot.connect.

This application needs to connect to a database using connexion parameters given by the user at runtime. This includes of course the Mysql Database name.

I used the EntityConnectionStringBuiler and EntityConnection classes to build-up and store the custom connexion parameters.

The problem is that even with such given parameters, the application always connect to the database named when designing the EntityModel using the visual studio wizard. What is very strange is that when debuging and checking the status of the ObjectContext, the custom connexion parameters are correctly used... It makes me mad !!!!! Any clue ?

Answer 1

After spending one day on this issue, I finally came to understand that the problem was coming from the model.edmx file.

In this file, you have one line per EntitySet. On each EntitySet element there is an attribute called schema. In case of SQL Server this attribute is set to the related table schema :

EntitySet Name="annee_civile" EntityType="openemisModel.Store.annee_civile" store:Type="Tables" Schema="mydatabase" />

If you provide the name of the Schema when constructiong you own EntityConnection, it seem that there is a conflict and that finally, the Schema defined in the edmx file will be used even if you specified another one in the connection parameters.

The solution is simply to remove the name of the schema in the edmx file. THIS WORKS FOR MYSQL, probably not when connecting to a SQL server.

EntitySet Name="annee_civile" EntityType="openemisModel.Store.annee_civile" store:Type="Tables" Schema="" />

The EntityConnectionStringBuilder :

string providedString = "User Id=xxxx;Password=xxx;Host=xxxx;Database=anydatabasename"; EntityConnectionStringBuilder entityConnBuilder = new EntityConnectionStringBuilder(); entityConnBuilder.Provider = "Devart.Data.MySql"; entityConnBuilder.Metadata = @"res:///OpenEmisModel.csdl|res:///OpenEmisModel.ssdl|res://*/OpenEmisModel.msl"; entityConnBuilder.ProviderConnectionString = providedString;

The EntityConnection and the object context using it:

EntityConnection entityConnexionEmis = new EntityConnection(entityConnBuilder.ConnectionString);

objectcontextEntities testingContext = new objectcontextEntities(entityConnexionEmis);

The software is now able to connect to any database name.

Hope this helps.