Entity Framework with Code First and MySql 5.6.14 (Current Version)

A question over at  StackOverflow made me curious about how Entity Framework with Code First and MySql are working together. Specifically Code First, as it’s the less obvious for EF.

Without going deep into Entity Framework (if you read this, I assume you already use it), you can use EF following 2 basic scenarios:

  • From an existing database, generate in Visual Studio classes for the data repository model
  • Write classes to define the data model, and generate database tables and columns from there. As this last approach start by writing code, it sound appropriate to name it Code First…

To test Entity Framework with MySql, I created a small console application, so that anything not absolutely mandatory for this purpose would be eliminated. No clutter here! You will be able to download my project at the end of this post, but I’ll start by describing the items needed and main steps.

  • Visual Studio 2012, any version. Not tested with another version, but VS 2013 should be fine.
  • MySql. Unless you already have it on your computer, the easiest way is to download and install MySql Installer, which include, beside the server itself, MySql Workbench, a very good “studio” for MySql, and connectors.
  • Everything else will be installed directly into the project with NuGet.

Creating the App

As stated above, it’s a simple console application, without anything else.

MySQLAppCreate

To be able to use Entity Framework, the very first thing to do is to install it, via NuGet

mySqlAppEF

Current version is 5.0, which is the one I used for this test.

Now, it’s getting a bit more complicated. The MySql connector installed with the server and WorkBench is version 6.7.4, and it matches Entity Framework version 4.2. As I want a connector matching EF 5.0, and able to use its features, I need to install version 6.7.2 beta instead. To do this, in NuGet console, type “Install-Package MySql.Data.Entity –Pre”. You should see something like this:

MySqlAppConnector

Installing EntityFramework with NuGet, beside putting the proper references in your project, modified your app.config to add a default configuration. This is perfect when one use one of MS SQL Servers, its just confusing for MySql. So we are going to eliminate from our config file anything related to EntityFramework, and add what’s needed for mySql:

Now we can start to code our application. Having no other goal than to make a simple test, I did not create a “Model” directory as I would normally do for the repository classes. The code is there for you to read, I believe it to be simple enough, no more comments are needed. Just a note about Eager Loading and Lazy Loading, and an explanation on the commented code in Program.cs: by default, Entity Framework use Lazy Loading. This means data are loaded from the database into memory only when needed. In a  way this is great, as it free the programmer, and his code, to need to know exactly when data are loaded. Great too, as data are loaded in memory only once needed, instead of by large chunk not used now. There are downside, too, but this is out of topic here. The thing is, for this to work, its needed to be able to get more than one query active on the same connection. If you look at this code:

it might not be obvious, but  aPerson.Phones.Count generate a query while we are already reading results from another one. No problem with MS Sql, which implement MARS (Multiple Active Result Sets), a feature allowing to have more than one query active at a time on the same connection. To my knowledge, MySql does not have any comparable feature. So we have to use Eager Loading, that is Entity Framework will relay on what we tell him to do about loading related data. The commented code show a first method, with .Include, while working code show a second method, with .ToList(). To see what happen with Lazy Loading, remove “.ToList()” and the parenthesis, and restart the application.

ToList() forces the query to be evaluated entirely, so Entity Framework will generate all required JOIN statements in SQL. If you have many entities/tables linked together, this might cause a slow down, as all relationships are queried, while Include allow to precisely select what you need to include in the query.

By the way, last remark: if the database in the connection string (test, here) does not exists on your server, Code First will create it for you (as long as the user has rights to do so).

Let us know what you think, and about your own findings, using comments below.

This work is licensed under a Creative Commons license.

Posted in ASP.NET MVC 4.0
13 comments on “Entity Framework with Code First and MySql 5.6.14 (Current Version)
  1. howdy, great job! i really like a great deal of this web site

  2. Sergey says:

    Hello there!
    Excuse me please, that i open again so old post, but. This code didn’t work in a VS2013.
    Where i can read a step-by-step instruction how EntityFramework connects with MySQL?
    Thanks a lot.

    • Don’t worry, it’s perfectly OK to open an older post to comment on it.
      Now, what you mean by “Did not work” ? It’s a little difficult to pinpoint the problem if you are not a bit more precise. This was written with VS 2013, so it is not your problem. Which version of MySQl, MySQL .Net Connector and Entity are you using?

      HTH,
      Bernard

  3. Naveen Raina says:

    Hi Bernard,

    After I install 6.7.2 beta on my machine, I am able to run my app successfully but it does not work on my shared hosted server. I thought every dll I need would be there in my Bin folder when I publish my app as I had set their Copy Local to true. But this does not seem to be the case. Do you have any idea what can be the reason.

    Regards,
    Naveen

    • What error message do you get? Have you compared your 2 bin folders, the local one and on the server? Sorry, no magic solution, but I would start by comparing folders, then, if no differences, comparing the GAC content on both machines.

      Take care,
      Bernard

  4. imran says:

    Nice article!
    @iampano,
    change your project profile from client to 4.0. Client profile does not allows any external reference to the project.
    happy coding!

  5. bottes jonak says:

    I am really thankful to the owner of this site who has shared this wonderful post at at this place.

  6. Your article normally have much of really up to date info. Where do you come up with this? Just stating you are very imaginative. Thanks again

  7. ianpano says:

    Willl do. Thanks for the prompt reply.

  8. ianpano says:

    Hi,

    I tried installing MysSql.Data.Entity -Pre via the PMConsole but I got this message

    Successfully installed ‘MySql.Data.Entity 6.7.2-beta-ef6’.
    Successfully uninstalled ‘MySql.Data.Entity 6.7.2-beta-ef6’.
    Install failed. Rolling back…
    Install-Package : Could not install package ‘MySql.Data.Entity 6.7.2-beta-ef6’. You are trying to install this package into a project t
    hat targets ‘.NETFramework,Version=v4.0,Profile=Client’, but the package does not contain any assembly references that are compatible w
    ith that framework. For more information, contact the package author.
    At line:1 char:16
    + Install-Package <<<< MySql.Data.Entity -Pre
    + CategoryInfo : NotSpecified: (:) [Install-Package], InvalidOperationException

    What to do? Thank you very much.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

NewsLetter

On Site Search

Custom search