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.
To be able to use Entity Framework, the very first thing to do is to install it, via NuGet
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:
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:
<?xml version="1.0" encoding="utf-8"?>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description="Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data" />
<add name="MyContext" connectionString="server=localhost; User Id=username; Password=mypassword; Persist Security Info=True; database=test; providerName="MySql.Data.MySqlClient" />
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:
foreach (var aPerson in allPersons)
Console.WriteLine(aPerson.FirstName + " " + aPerson.LastName + " has " + aPerson.Phones.Count +
if (aPerson.Phones.Count != 0)
foreach (var phone in aPerson.Phones)
Console.WriteLine(" ------------- " + phone.Type + " " + phone.Number);
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.