1

I want to develop on sql server database and use MySql for production.

I've created my database using SSMS Diagram and generated ADO.NET Enity Model in my ASP.NET MVC project. Then I modified connection string from Sql Server to point to MySql Server and specifically passed MySqlConnection object to DbContext. Now I'm trying to execute this below code but it complains that provided underlying connection is not of .NET Sql Server Connection type.

dbContext.CreateDatabase();
IsmailS
  • 10,797
  • 21
  • 82
  • 134

2 Answers2

1

You have to add MySql to the System.Data part of your web.config file.

   <system.data>
            <DbProviderFactories>
                    <clear />
                    <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.4.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
            </DbProviderFactories>
    </system.data>

Notice, that I'm using version 6.4.4.0 of the Mysql.Data library, if you're using a different version, you'll need to update that.

taylonr
  • 10,732
  • 5
  • 37
  • 66
1

In the web.config you need to:

  • add the provider factory for MySQL (as shown above)
  • modify your connection string (or create a new one) where providerName attribute is set accordingly (something like <add name="NorthwindEntities" connectionString="metadata=NorthwindModel;provider=MySQL Data Provider;provider connection string="Data Source=.\SQLExpress;Initial Catalog=Northwind;Integrated Security=True"" providerName="System.Data.EntityClient"/>

On top of these changes you need to have two SSDL files - one that is for Sql Server and one that is for MySQL. These files are specific to the store you use. They not only use the types the store understands but also have provider specific information (I put it in the {curly brackets}, sorry I don't know an exemplary provider manifest token for my Sql):

<Schema Namespace="NorthwindEFModel.Store" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl"
    Provider="{MySQL Data Provider}" ProviderManifestToken="{Provider manifest token}">

I think you can save some hassle if you use code first. You will create and possibly configure your model using code (classes + DbContext.OnModelCreating()) and have EF create the database for you. Depending on your connection string (which in this case will be a "regular" connection string and not Entity Connection String) it will be talking either to Sql Server or MySQL and will create database accordingly.

Pawel
  • 31,342
  • 4
  • 73
  • 104
  • Thanks for help. Using code first now. All other suggestions were nice but didn't worked as Varchar(Max) and money was creating problem with MySql – IsmailS May 05 '12 at 11:20
  • I was not using Code First because I didn't wanted to write Entity classes by hand. But I found [Entity Framework Power Tools](http://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d) which helped me reverse engineer Code First entities from DB – IsmailS May 05 '12 at 11:23
  • That's why you for MySql you have a separate SSDL that uses MySql specific types. If you are using CodeFirst and are curious how your MySql SSDL looks like you can use EdmxWriter.WriteEdmx to see the EDMX file that contains all artifacts. For the same model you can do that for MySql and Sql Server and compare/diff files. – Pawel May 05 '12 at 16:32
  • You are awesome. Thanks a ton for sharing wealth of knowledge. – IsmailS May 05 '12 at 17:53