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

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

3 Reasons Why Progressive Web Apps (PWAs) Won’t Replace Native Apps

Many people believe Progressive Web Apps (PWAs) are the future of the mobile web, but in my opinion, PWAs are not a replacement for native mobile apps. Here are three reasons why: 1. Native mobile apps provide a smoother & faster experience  Mobile websites, progressive or otherwise are slower and not as smooth. 90% of the time spent is spent using apps vs the browser . The single most significant contributing factor to a smooth experience on mobile is the speed of the network and latency of the data downloaded and uploaded. When you visit websites on desktop or mobile, there is a lot of third-party code/data that gets downloaded to your device, which more often than not has zero impact on the user experience. This includes: CSS (Cascading Style Sheets) JavaScript Ad network code Facebook tracking code Google tracking code The median number of requests a mobile website makes is a shocking  69 . On the other hand, native apps only get the data that is requi

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