CODEWITHSUNDEEP
c#entity-framework
Marco Rebsamen
Marco Rebsamen

Reputation: 605

HowTo Connect EF6 to MsSQL with existing MySQL Data Model

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?

Upvotes: 1

Views: 384

Answers (2)

Marco Rebsamen
Marco Rebsamen

Reputation: 605

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:

  1. If a new table is created in the DB the interface has to be modified
  2. 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.

Upvotes: 1

Miguel
Miguel

Reputation: 3952

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!

Upvotes: 0

Related Questions