HowTo Connect EF6 to MsSQL with existing MySQL Data Model

Question

I have an existing data model Created from a existing MySQL Database. Is it possible to use this model but connect to a identical MsSQL Database?

If I change the connection string and use the metadata from MySql it complains that it can not convert the SqlConnection to a MySqlConnection (of course).

How is this properly done? Is it possible at all?

I have never tried this but my guess is this would not be possible because the metadata will contain words that are only for MySQL and would not apply to SQL Server. For example the data type may be specific to MySql — CodingYoshi, Aug 23 '17 at 12:21
If you have an identical MS SQL db then why dont you just create an edmx from that? You will have one for each. — CodingYoshi, Aug 23 '17 at 12:27
wouldn't that mean I have to change the code? context creation object references etc.... — Marco Rebsamen, Aug 23 '17 at 12:29

score 1 · Accepted Answer · answered Aug 24 '17 at 08:19

I think Miguel led me to a solution:

Solution 1

In my case I had a separate project in my solution which keeps the data model. In that project I added another ADO.NET Entity Data Model generated from the MS SQL DB. At this point it would already work, besides that we would have to change the code wherever we create or access the context. To solve this I've created a interface for the context object that the EF Wizard generated, and implemented it in a partial type. This way I can "Update the Model from Database" without loosing it:

public partial class EntitiesMs : DbContext
{
    public EntitiesMs()
        : base("name=EntitiesMs")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    // Your DbSet<...> Stuff
}

public partial class Entities : DbContext
{
    public Entities()
        : base("name=Entities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    // Your DbSet<...> Stuff
}

And in separate files of course:

public partial class Entities : IDbEntities
{
}

public partial class EntitiesMs : IDbEntities
{
}

A Factory could then return the desired object. Although this has some downsides:

If a new table is created in the DB the interface has to be modified
You would have to cast the object from the factory to access methods like SaveChanges() (methods from the base type)

And maybe some others I have overlooked.

Solution 2

Another "quick and dirty" way I've found is to add another project to the solution and add the MS Data Model there. Make sure that the Model has the exact same name as the one for MySQL. Then the only things you have to do in your startup project is, switch the reference to the other project and switch the connection strings.

score 0 · Answer 2 · answered Aug 23 '17 at 15:41

0

The only problem that you need to solve if the EDMX file. You may need to have one to the MySQL and one distinct to the MSSQL and specify which one you want at the connection string level.

An EF data model connection string looks like this:

<add name="Entities" connectionString="metadata=res://*/CMBS.csdl|res://*/CMBS.ssdl|res://*/CMBS.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source={database};initial catalog=;persist security info=True;user id=;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

You need to update the metadata part of the connection string. Here you can find more information to manipulate EDMX files.

Hope this help!

answered Aug 23 '17 at 15:41

Miguel

3,786
2
19
32

But this would also mean that I have to change the type at all places where I create a context...? – Marco Rebsamen Aug 24 '17 at 07:00
No, only the connection string of the context (in the constructor). – Miguel Aug 24 '17 at 15:30
Sorry I really don't see how this should be done... when I create the context I don something like `using (Entities context = new Entities())` if I add another model, I get another type so I would have to change it to `using (MsEntities context = new MsEntities())` and in the question of your link they want to create a DB from a edmx file... – Marco Rebsamen Aug 25 '17 at 14:35
Set the connection string when calling the base constructor. – Miguel Aug 26 '17 at 13:40

HowTo Connect EF6 to MsSQL with existing MySQL Data Model

2 Answers2