Entity Framework database first with MySQL and MS SQL

Question

I currently have an application which uses EF database first which was built against SQL Server database. I would like to allow the ability to connect to either MSSQL or MySQL.

I assumed that by using EF I could just change the connection string to point to MySQL, however, when I do that I get the following error:

Unable to cast object of type 'MySql.Data.MySqlClient.MySqlConnection' to type 'System.Data.SqlClient.SqlConnection'.

What other steps are needed to make EF work with MySQL and MSSQL.

Update: Connection String:

<add name="EntityConnString" connectionString="metadata=res://*/Entities.csdl|res://*/Entities.ssdl|res://*/Entities.msl;provider=MySql.Data.MySqlClient;
provider connection string=&quot;server=localhost;User Id=root;password=password;Persist Security Info=True;database=mydb;Connection Protocol=NamedPipe&quot;" providerName="System.Data.EntityClient" />

Have you made any changes in your generated code? Your tt files or everything is default? Your connection string is fine. But also have a look inside your edmx it may contain some reference to mssql in storage namespace (ssdl). — Akash Kava
– Akash Kava, Commented Jun 6, 2011 at 6:05

Ladislav Mrnka · Accepted Answer · 2011-06-06 08:24:08Z

3

You need separate EDMX for that - or at least SSDL part and most probably also MSL part specified in the connection string. The reason is that SSDL (storage description in EDMX) is provider dependent = at the moment your SSDL says: "I can connect only to MSSQL". If you want to support MySQL you need SSDL which will say: "I can connect only to MySQL" and define the connection string accordingly to used database server. SSDL contains database specific definitions. If your MySQL database will use different names of tables or columns or different schema you will also need a new MSL part (describing mapping between database tables and your entities).

answered Jun 6, 2011 at 8:24

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework database first with MySQL and MS SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related