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.

Popular posts from this blog

ASP.NET MVC Release Candidate - Upgrade issues - Spec#

First of all, great news that the ASP.NET MVC Release Candidate has finally been released.  Full credit to the team for the hard work on this.  You can get the download here  However this is the first time I have had upgrade issues.  Phil Haack has noted some of the issues here   If like me you have lot's of CTP's and Add-Ins then you might experience some pain in Uninstalling MVC Beta on Vista SP1  This is the list of Add-Ins / CTP's I had to uninstall to get it to work  Spec# PEX Resharper 4.1  Sourcelinks ANTS Profiler 4   Can't say I'm too impressed as it wasted over an hour of my time.  As it turned out Spec# turned out to be the offending culprit, it's forgiveable to have issues with a third party product but a Microsoft one? Guess no-one on the ASP.NET team has Spec# installed. 

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\Temp Delete all EventViewer logs Save to another Disk if you want to keep them Remove any unused programs, e.g. Firefox Remove anything in C:\inetpub\logs Remove any file/folders C:\Windows\System32\LogFiles Remove any file/folders from C:\Users\%UserName%\Downloads Remove any file/folders able to be removed from C:\Users\%UserName%\Desktop Remove any file/folders able to be removed from C:\Users\%UserName%\My Documents Stop Windows Update service and remove all files/folders from C:\Windows\SoftwareDistribution Deleting 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

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 Started To 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\R