Skip to main content

Using Entity Framework 4 with MySQL

 

If you’re on the .NET Platform then MS SQL Server is usually the de-facto choice for the RDBMS. However if you’re at all cost conscience then you will realize that scaling and replication is going to cost you a fair chunk of change in licensing fees.

For that reason open source RDBMSs and in particular MySQL offer a much cheaper alternative.

In this post I’d like to demonstrate how you can use Entity Framework 4 with MySQL.

MySQL Connector Net 6.3.6

The first thing you’ll need to do is download and install the latest version of the MySQL Connector for .NET from http://dev.mysql.com/downloads/connector/net/

Make sure that Visual Studio is closed when you install.

Pascal Case Table Names

Because we are going to generate our Entity Framework Model of an existing database we want to make sure that the entity names use pascal casing. By default MySQL on Windows forces lowercase table names.

You can change this behaviour by adding lower_case_table_names=2 to your my.ini file which will located in
C:\Program Files\MySQL\MySQL Server <YOURVERSION>\

Read more about Identifier case sensitivity on the MySQL site.

NOTE: This will need to be done before you create your Schema and Tables.

 

Creating the Entity Framework Model

Right click on the solution and go to Add New Item then select the ADO.NET Entity Data Model. 

AddNewItem

Then choose “Generate from Database”.

GenerateFromDatabase

You will then want to create a connection that points to your MySQL Database.

Go to New Connection. Note: by default the Data Source will be set to Microsoft SQL Server (SqlClient)

Click “Change” and select the Data source and Data Provider as shown below.

NewConnectionString

After doing this you enter your connection properties, shown below.

ConnectionProperties

Once you press OK you will be presented with your database objects like below. Select all the Tables, Views & Stored Procedures you want to include in your model and press Finish

DatabaseObjects

Follow the “Next” buttons through the wizard until your Model has been created.

EFModel

 

Testing the Object Context

To make sure that we can use the ObjectContext correctly I wrote a quick test in a console app.

internal class Program
    {
        private static void Main(string[] args)
        {
            using (var context = new EFDemoEntities())
            {
                var category = new Category
                                   {
                                       Name = "Developer Tools"
                                   };

                var product = new Product
                                  {
                                      Name = "Visual Studio 2010"
                                  };

                category.Products.Add(product);

                context.Categories.AddObject(category); 

                context.SaveChanges();

                category = context.Categories.FirstOrDefault();

                Console.WriteLine("CategoryId: " + category.Id);

                product = context.Products.FirstOrDefault();

                Console.WriteLine("ProductId: " + product.Id);

            }
        }
    }
 
And the output.
commandline

If you receive “The specified value is not an instance of a valid constant type Parameter name: value” when trying to do an insert of a related entity then make sure that the foreign key column is not UNSIGNED. The MySQL connector does not support UNSIGNED columns as foreign keys,

 

Conclusion

I’ve only covered the basic steps to using MySQL with Entity Framework 4.

I have not used this yet in production, however it seems that MySQL and Entity Framework 4 is now a viable solution for those not married to SQL Server.

Till next time.

Comments

  1. I ran into a problem converting a SQL CE compact 4.0 EF/DBContext asp.net website app to MySQL. (versions used MySQL 5.5.27 using inno, mysql ADO connector 6.5.4, EntityFramework 4.3.1, EF 4.x DbContext Generator, VS2010) I converted the schema from SQL CE 4.0 to MYSQL with no problems by upsizing SQL CE 4.0 to full SQL Server and then using MySQL migration tools to move SQL Server to MySQL, I created a new EF using the created MYSQL schema, and the added DBContext code generation to the EF model, which generated context and DbSet record classes for MySQL schema exactly the same as had been created for the SQL CE 4.0 DB schema. Everything built fine with no errors, but on execution I ran into continual problems with getting an error. Typical scenario, a dbcontext record class order had a property orderlines (generated automatically by EF/DBContext because of an FK between those two tables) such that for a given order record, orderlines would return all lines for that order. Worked fine in SQL CE 4.0, but with MYSQL/EF I kept getting "There is already an open DataReader associated with this Connection which must be closed first". Turned out that MySQL does not support MARS (multiple active record sets) and despite some suggestions to do so, adding "MultipleActiveResultSets=True" to MySQL connection string was not supported. So when you got an order object from EF it uses a connection, and when you accessed order.orderlines it attempts to reuse the same connection to get the lines, which is not supported and produces the datareader error. What I did to fix in was add a separate partial class orders. (There is no point to modifying the EF/DBContext generated classes as they are code-generated, but they were thoughtful enough to make them partial, so you can easily extend them) To that new orders class I created a new property get_orderlines, which was coded to create a new DbContext instance, and retrieved the data using that. This obviously must create a new DB connection, and a new DataReader to get the data. I did this for all properties in all EF generated objects for my various tables, and changed my code to use the get_xxx properties instead of the original ones. This pretty much solved the issue. I ran into one other instance where I got the same "datareader" error in some DB batch code, and again, using a new DbContext solved that. I would hope MySQL would fix this and permit MARS so that the EF/DBContext code would work out of the gate. MySQL proved to be considerably faster than SQL CE 4.0 which was my original problem to solve. One other thing ..  I could have used the free SQL Server express 2012 instead, (and will try that next for fun), but the hosting environment I plan to deploy to has older SQL Server and I need paging SQL OFFSET syntax currently so MySQL seemed like an easy solution.

    ReplyDelete
  2. I am getting an exception when I used stored procedures along with entity framework can you help me, I have written some thing like as follows entities.ExecuteStoreCommand("uspInsertUsers @UserName,@Password,@FirstName,@LastName", userName, password, FirstName, LastName);. I am getting an exception as
    error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

    ReplyDelete
  3. ExecuteStoreCommand does not support SQL Parameters http://msdn.microsoft.com/en-us/library/ee358758.aspx. The syntax would be ExecuteStoreCommand("uspInsertUsers {0},{1},{2},{3}", userName, password, FirstName, LastName)

    ReplyDelete
  4. Hi this is the error I am getting if I write as per you said You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'deleteUser {0}' at line 1

    I am using MYSQL database

    This is my code

    //var ietsParameter = new MySqlParameter("@ID", 7);

    entities.ExecuteStoreCommand("deleteUser {0}", ietsParameter );

    ReplyDelete

Post a Comment

Popular posts from this blog

Freeing Disk Space on C:\ Windows Server 2008

I just spent the last little while trying to clear space on our servers in order to install .NET 4.5. Decided to post so my future self can find the information when I next have to do this. I performed all the usual tasks: Deleting any files/folders from C:\windows\temp and C:\Users\%UserName%\AppData\Local\TempDelete all EventViewer logs Save to another Disk if you want to keep themRemove any unused programs, e.g. FirefoxRemove anything in C:\inetpub\logsRemove any file/folders C:\Windows\System32\LogFilesRemove any file/folders from C:\Users\%UserName%\DownloadsRemove any file/folders able to be removed from C:\Users\%UserName%\DesktopRemove any file/folders able to be removed from C:\Users\%UserName%\My DocumentsStop Windows Update service and remove all files/folders from C:\Windows\SoftwareDistributionDeleting an Event Logs Run COMPCLN.exe Move the Virtual Memory file to another disk However this wasn’t enough & I found the most space was cleared by using the Disk Cleanup to…

Consuming the SSRS ReportExecutionService from a .NET Client

I’ve just finished writing a nice wrapper which internally calls the SSRS ReportExecutionService to generate reports.
Whilst it was fairly simple to implement there has been some major changes between 2005 and 2008 and the majority of online and documentation is based on the 2005 implementation. The most important change is that the Report Server and Report Manager are no longer hosted in IIS which will be a welcomed change to Sys Admins but makes the security model and hosting model vastly different. So far I’ve yet to figure out how to allow Anonymous Access, if anyone knows how to do this leave a comment and it will be most appreciated. Getting StartedTo get started you’ll want to add a service reference to http://localhost/ReportServer_SQL2008/ReportExecution2005.asmx where ReportServer_SQL2008 is the name you configure in the Reporting Services Configuration Manager. The Web Application files are located in C:\Program Files\Microsoft SQL Server\MSRS10.SQL2008\Reporting Servic…

Log Shipping in SQL Server Express 2008

When you entrust your Applications and Data to the Cloud Based Service Providers such as GoGrid and Amazon EC2 it becomes absolutely critical to employ a strict Disaster Recovery strategy. As part of of our strategy it was imperative that we have a failover for our SQL Server. The only problem was that SQL Server Express 2008 doesn’t support Log Shipping.
Well technically it does but SQL Server Express does not have SQL Server Agent. Now I’m a big fan of SQL Server, but when it comes to doing something a bit more complicated the Express editions limitations become a bit of a problem, that’s why it’s time to find creative solutions. To set up Log Shipping the first step is to Backup your database and then Restore it on another instance making sure set the Recovery state as “RESTORE WITH STANDBY”. See belowTo get this going you’ll need three stored procedures, one in the Master database and two in the slave database. Now when you are trying to do this across Networks and Hosting Pro…