Visual Studio 2015 was recently released, and with it came a newer beta of ASP.NET 5 (formerly referred to as "ASP.NET vNext"). ASP.NET 5 is a complete rewrite of ASP.NET, focusing on being lightweight, composible, and cross-platform. It also includes an alpha version of Entity Framework 7. However, EF7 is not yet production-ready and does not support all features of EF6. One feature that is missing from EF6 is support for other database providers - Only SQL Server and SQLite are supported at this time.
I wanted to transition a site over to ASP.NET 5, but needed to continue using MySQL as a data source. This meant getting Entity Framework 6 running on ASP.NET 5, which is pretty much undocumented right now. All the documentation and tutorials for EF6 heavily relies on configuration in Web.config, which no longer exists in ASP.NET 5. In this post I'll discuss the steps I needed to take to get it running. An example project containing all the code in this post can be found at https://github.com/Daniel15/EFExample.
Since EF6 does not support .NET Core, we need to remove .NET Core support (delete "dnxcore50": { } from project.json). Once that's done, install the EntityFramework and MySql.Data.Entity packages, and add references to System.Data and System.Configuration. For this post, I'll be using this basic model and DbContext, and assume you've already created your database in MySQL:
public class MyContext : DbContext { public virtual DbSet<Post> Posts { get; set; } } public class Post { public int Id { get; set; } public string Title { get; set; } public string Content { get; set; } }
Entity Framework 6 relies on the provider and connection string being configured in Web.config. Since Web.config is no longer used with ASP.NET 5, we need to use code-based configuration to configure it instead. To do so, create a new class that inherits from DbConfiguration:
public class MyDbConfiguration : DbConfiguration { public MyDbConfiguration() { // Attempt to register ADO.NET provider try { var dataSet = (DataSet)ConfigurationManager.GetSection("system.data"); dataSet.Tables[0].Rows.Add( "MySQL Data Provider", ".Net Framework Data Provider for MySQL", "MySql.Data.MySqlClient", typeof(MySqlClientFactory).AssemblyQualifiedName ); } catch (ConstraintException) { // MySQL provider is already installed, just ignore the exception } // Register Entity Framework provider SetProviderServices("MySql.Data.MySqlClient", new MySqlProviderServices()); SetDefaultConnectionFactory(new MySqlConnectionFactory()); } }
The first part of the configuration is a hack to register the ADO.NET provider at runtime, by dynamically adding a new configuration entry to the system.data section. The second part registers the Entity Framework provider. We also need to modify the configuration file to include the connection string. You can use any configuration provider supported by ASP.NET 5, I'm using config.json here because it's the default provider.
{
"Data": {
"DefaultConnection": {
"ConnectionString": "Server=localhost; Database=test; Uid=vmdev; Pwd=password;"
}
}
}
Now that we have the configuration, we need to modify the context to use it:
[DbConfigurationType(typeof(MyDbConfiguration))] public class MyContext : DbContext { public MyContext(IConfiguration config) : base(config.Get("Data:DefaultConnection:ConnectionString")) { } // ... }
An instance of IConfiguration will be automatically passed in by ASP.NET 5's dependency injection system. The final step is to register MyContext in the dependency injection container, which is done in your Startup.cs file:
public void ConfigureServices(IServiceCollection services) { // ... services.AddScoped<MyContext>(); }
AddScoped specifies that one context should be created per request, and the context will automatically be disposed once the request ends. Now that all the configuration is complete, we can use MyContext like we normally would:
public class HomeController : Controller { private readonly MyContext _context; public HomeController(MyContext context) { _context = context; } public IActionResult Index() { return View(_context.Posts); } }
Hope you find this useful!
Until next time,
— Daniel
Comments
Hi Daniel,
Awesome sample, got it working, very helpful!!!
A couple of things I found that may help others; I am on a Mac, OS X Yosemite, using ASP.Net 5 beta6:
The code starting in line 7 of MyDbConfiguration():
dataSet.Tables[0].Rows.Add(
didn't work for me - I still got an exception regarding the ADO.Net provider not being found. Based on advice I found from others, I then manually installed MySQL.Data.dll and MySQL.Data.Entify.EF6.dll, did the gacutil thing, and then manually edited the machine.config under mono to registered MySql:
<system.data>
<dbproviderfactories>
<remove invariant="MySql.Data.MySqlClient"></remove>
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.6.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
After that I got an error indicating a unique constraint on invariant, so I commented out the code code starting in line 7 of MyDbConfiguration(): dataSet.Tables[0].Rows.Add( ...
Then everything worked ....
Some more details for the unfamiliar: some samples auto initialize the db - this sample will throw an error if you don't have a Posts table in the db.
The MVC view failed silently for most of the above errors - I found it helpful to add logging and exception handling, and also to enable an error handler view:
in Startup.cs:
public void Configure(IApplicationBuilder app)
{
app.UseErrorHandler("/Home/Error");
...
..
/Home/Error.cshtml:
@model Exception
@{
ViewBag.Title = "EXCEPTION!";
}
<h2 class="text-danger">An error occurred while processing your request.</h2>
@if (Model != null)
{
@Html.ValueFor(model => model.Source)
@Html.ValueFor(model => model.Message)
@Html.ValueFor(model => model.StackTrace)
}
Thanks!!
I'm glad it helped!
I noticed the same thing with Mono - Adding the ADO.NET provider at runtime doesn't work. I fixed it the same way you did - by GACing MySQL.Data.dll and editing machine.config. I didn't actually test on Mono until after I published the post so I didn't notice this until afterwards. When I get some free time I'll amend the post to include this information :)
Hi,
I ran in to problem that if you deploy this to Azure website, the MySql data provider is already in the Configuration, but this data provider was too old version. This caused multiple problems.
I had to change the configuration code to look like:
var dataSet = (DataSet)ConfigurationManager.GetSection("system.data");
dataSet.Tables[0].Rows.Clear();
dataSet.Tables[0].Rows.Add(
"MySQL Data Provider",
".Net Framework Data Provider for MySQL",
"MySql.Data.MySqlClient",
typeof(MySqlClientFactory).AssemblyQualifiedName
);
SetProviderServices("MySql.Data.MySqlClient", new MySqlProviderServices());
SetDefaultConnectionFactory(new MySqlConnectionFactory());
Hi, first of all thanks so much for the tutorial, have been looking everywhere for this...
I am having some issues resolving the MySql.Data.Entity dependency. Even when cloning your sample project the dependency cannot be resolved.
I have updated mysql Connctor 6.9.7 and tried the following as per another forum post:
Overwrite Assemblies: MySql.Data.dll, MySql.Data.Entity.EF6.dll, MySql.Web.dll, copying and pasting
from "C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.7\Assemblies\v4.5"
to "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies.
Any idea what step im missing...
Cheers,
Shane.
Hi Shane Arthur,
You can install from NuGet Package of the proyect.
Running the command "Install-Package MySql.Data.Entity" in the package manager console gives:
"Install-Package : Unable to find package 'MySql.Data.Entity.
I see the reference to the MySql Connector in the machine.config file, and I also see the references in the gacutil to the .dlls...
Very stumped what could be the issue...
Thank you Ramiro.
That's strange, I see the package here: https://www.nuget.org/packa.... Try install it through the package management UI in Visual Studio instead?
Also, make sure you disable .NET Core (remove the "dnxcore50" section from project.json), as EF6 doesn't support .NET Core.
Thanks for the reply, I've tried all the above (cannot see the package from the ui in VS2015)..
I've also moved the .dll's for MySql to the projects bin file, to no avail,
Changed to 6.9.8 as thats the version of mysql connector i dl'd
Hi Daniel,
It's any possibility to run this in a short time on .NET Core with mysql? For example, on linux or osx?
Because as far as I understand in this way you can not run on linux. What did u heard about?
By the way, your work it's awesome and greetings from Cali, Colombia
Unfortunately .NET Core is not supported yet, as Entity Framework 6 does not support .NET Core. Entity Framework 7 does, but there's no MySQL driver for EF7 yet.
Having said that, you can run EF6 and MySQL on Linux if you want. In fact, this blog is running on that exact stack :) You just need to install Mono and use the "full" .NET Framework 4.5 rather than .NET Core.
Thanks for this post Daniel - it's most useful!
I've just followed your instructions in an ASP.NET 5 MVC 6 web application in Visual Studio, using the recently released RTM. I've found that IConfiguration is not provided by the ASP.NET 6 DI. This is the error I received:
System.InvalidOperationException: Unable to resolve service for type 'Microsoft.Extensions.Configuration.IConfiguration' while attempting to activate 'MyProj.MyDbContext'.
I've searched for a solution online and came up with an interesting finding. The ASP.NET 5 documentation on configuration (http://docs.asp.net/en/late... includes this note:
"You could store your Configuration
instance as a service, but this would unnecessarily couple your
application to a single configuration system and specific configuration keys. Instead, you can use the Options pattern to avoid these issues."
You might want to consider adjusting your code sample to this (new?) recommendation.
Best Regards,
urig
Hi Uri
I am having the same error. Could you please let me know what exactly did you do to resolve this issue. System.InvalidOperationException: Unable to resolve service for type 'Microsoft.Extensions.Configuration.IConfiguration' while attempting to activate 'MyProj.MyDbContext'.
I already have my database designed. How can I use database first approach with EF6? All examples I am getting is for code first only. please suggest.
Just use the code-first approach, and build classes to match your current database schema. That's what I did. Code first doesn't necessarily mean that Entity Framework needs to create the database itself.