Entity Framework for MSSQL and MYSQL

As we all know that Entity Frame work supports multiple databases.Here i am converting MSSQL database to MYSQL,Without changing the query.That is the power of linq.

First i am doing one sample on MSSQL.

I am using AdventureWorks Database, and using 2 tables ,Production.ProductCategory and Production.ProductSubcategory.

The ER Diagram for Production.ProductCategory and Production.ProductSubcategory is shown below

Steps to Add Entity Frame Work for MSSQL

Step 1 : Right Click on Solution Explorer –> Add –> New Item –> Select ADO.NET Entity Data Model.

Step 2 : Select sever name and AdventureWorks as DataBase and Click ok

Step 3 : Click Next

Step 4 : Select two tables Production.ProductCategory and Production.ProductSubcategory and Click Finish

So your Edmx Looks like

Code for getting the data

 AdventureWorksEntities dbEntities = new AdventureWorksEntities();
            var query = from PC in dbEntities.ProductCategory
                        join PSC in dbEntities.ProductSubcategory
                      on PC.ProductCategoryID equals PSC.ProductSubcategoryID
                        select new { PCName = PC.Name, PCModifiedDate = PC.ModifiedDate, PSCName = PSC.Name, PSCModifiedDate = PSC.ModifiedDate };
            GridView1.DataSource = query;
            GridView1.DataBind();

Here i cannot use Debug Visualizer.Entity Frame work wont support Debug Visualizer.So i am using one gridview and dispaly the data.

Note : If you are getting the error “An anonymous type cannot have multiple properties with the same name “ Read this

Output shown in Gridview.

The same result i am getting from MYSQL without changing the query.Only i am changing the database from MSSQL to MYSQL.

Converting MSSQL to MYSQL by using Full Convert Enterprise .

Steps to Covert

Step 1 : Already created AdventureWorks DataBase in MYSQL.

Step 2 : Enter username and Password

Step 3 : I selected all tables selected to convert MYSQL.

Step 4 :Click Convert.

Step 5 : Click Continue.

After Convert It shows a result like

Steps to Add Entity Frame Work for MYSQL.

Step 1 : install Connector/ NET 6.0 Windows Binaries,it automatically install mysql.data.dll and mysql.data.entity.dll to GAC.

Step 2 : Right Click on Solution Explorer –> Add –> New Item –> Select ADO.NET Entity Data Model.

Step 3 : Select New Connection.

Step 4 : Change the Data Source from MSSQL to MYSQL.

Step 5 : Select MySQL .if you install Connector/ NET 6.0 Windows Binaries ,only it will come.

Step 6 : Enter the necessary Details and click ok.

Step 7 : Select the necessary fields and click next.

Step 8 : Select two tables Production.ProductCategory and Production.ProductSubcategory.

The edmx will be same as previous MSSQL edmx.

Use the same query in MSSQL .

 adventureworksEntities dbEntities = new adventureworksEntities();
            var query = from PC in dbEntities.productcategory
                        join PSC in dbEntities.productsubcategory
                      on PC.ProductCategoryID equals PSC.ProductSubcategoryID
                        select new { PCName = PC.Name, PCModifiedDate = PC.ModifiedDate, PSCName = PSC.Name, PSCModifiedDate = PSC.ModifiedDate };
            GridView1.DataSource = query;
            GridView1.DataBind();

The result will display as same result.

Note it is easy to convert from one database to another database. But still some errors are there in EDMX Framework ( it wont support some joins ,Math operations etc).All the bugs will overcome on .NET 4.0 . Wait and see.