1

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" />
Omar
  • 39,496
  • 45
  • 145
  • 213
  • Can you post your connection string with dummy data? – Akash Kava Jun 06 '11 at 04:43
  • Added connection string. – Omar Jun 06 '11 at 05:47
  • 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 Jun 06 '11 at 06:05

1 Answers1

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).

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670