ASP.NET Core 1.0 with MySQL and Entity Framework Core

This article shows how to use MySQL with ASP.NET Core 1.0 using Entity Framework Core.

Code: https://github.com/damienbod/AspNet5MultipleProject

2016.12.01: Updated to ASP.NET Core 1.1

Thanks to Noah Potash for creating this example and adding his code to this code base.

The Entity Framework MySQL package can be downloaded using the NuGet package SapientGuardian.EntityFrameworkCore.MySql. At present no official provider from MySQL exists for Entity Framework Core which can be used in an ASP.NET Core application.

The SapientGuardian.EntityFrameworkCore.MySql package can be added to the project.json file.

{
    "dependencies": {
        "DomainModel": "*",
        "Microsoft.AspNetCore.Diagnostics": "1.1.0",
        "Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore": "1.1.0",
        "Microsoft.AspNetCore.Mvc": "1.1.0",
        "Microsoft.AspNetCore.StaticFiles": "1.1.0",
        "Microsoft.EntityFrameworkCore": "1.1.0",
        "Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final",
        "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.1.0",
        "Microsoft.Extensions.Configuration.Json": "1.1.0",
        "Microsoft.Extensions.Logging": "1.1.0",
        "Microsoft.Extensions.Logging.Console": "1.1.0",
        "Microsoft.Extensions.Logging.Debug": "1.1.0",
        "Microsoft.Extensions.Options.ConfigurationExtensions": "1.1.0",
        "Microsoft.NETCore.App": {
            "version": "1.1.0",
            "type": "platform"
        },
        "SapientGuardian.EntityFrameworkCore.MySql": "7.1.4"
    },

    "tools": {    },

    "frameworks": {
        "netcoreapp1.1": {
            "imports": [
                "dotnet5.6",
                "portable-net45+win8"
            ]
        }
    }
}

An EfCore DbContext can be added like any other context supported by Entity Framework Core.

using System;
using System.Linq;
using DomainModel.Model;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace DataAccessMySqlProvider
{ 
    // >dotnet ef migration add testMigration
    public class DomainModelMySqlContext : DbContext
    {
        public DomainModelMySqlContext(DbContextOptions<DomainModelMySqlContext> options) :base(options)
        { }
        
        public DbSet<DataEventRecord> DataEventRecords { get; set; }

        public DbSet<SourceInfo> SourceInfos { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<DataEventRecord>().HasKey(m => m.DataEventRecordId);
            builder.Entity<SourceInfo>().HasKey(m => m.SourceInfoId);

            // shadow properties
            builder.Entity<DataEventRecord>().Property<DateTime>("UpdatedTimestamp");
            builder.Entity<SourceInfo>().Property<DateTime>("UpdatedTimestamp");

            base.OnModelCreating(builder);
        }

        public override int SaveChanges()
        {
            ChangeTracker.DetectChanges();

            updateUpdatedProperty<SourceInfo>();
            updateUpdatedProperty<DataEventRecord>();

            return base.SaveChanges();
        }

        private void updateUpdatedProperty<T>() where T : class
        {
            var modifiedSourceInfo =
                ChangeTracker.Entries<T>()
                    .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified);

            foreach (var entry in modifiedSourceInfo)
            {
                entry.Property("UpdatedTimestamp").CurrentValue = DateTime.UtcNow;
            }
        }
    }
}

In an ASP.NET Core web application, the DbContext is added to the application in the startup class. In this example, the DbContext is defined in a different class library. The MigrationsAssembly needs to be defined, so that the migrations will work. If the context and the migrations are defined in the same assembly, this is not required.

public Startup(IHostingEnvironment env)
{
	var builder = new ConfigurationBuilder()
		.SetBasePath(env.ContentRootPath)
		.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
		.AddJsonFile("config.json", optional: true, reloadOnChange: true);

	Configuration = builder.Build();
}
		
public void ConfigureServices(IServiceCollection services)
{	
	var sqlConnectionString = Configuration.GetConnectionString("DataAccessMySqlProvider");

	services.AddDbContext<DomainModelMySqlContext>(options =>
		options.UseMySQL(
			sqlConnectionString,
			b => b.MigrationsAssembly("AspNet5MultipleProject")
		)
	);
}

The application uses the configuration from the config.json. This file is used to get the MySQL connection string, which is used in the Startup class.

{
    "ConnectionStrings": {  
        "DataAccessMySqlProvider": "server=localhost;userid=damienbod;password=1234;database=damienbod;"
        }
    }
}

MySQL workbench can be used to add the schema ‘damienbod’ to the MySQL database. The user ‘damienbod’ is also required, which must match the defined user in the connection string. If you configure the MySQL database differently, then you need to change the connection string in the config.json file.

mySql_ercore_aspnetcore_01

Now the database migrations can be created and the database can be updated.

>
> dotnet ef migrations add testMySql
>
> dotnet ef database update
>

If successful, the tables are created.

mySql_ercore_aspnetcore_02

The MySQL provider can be used in a MVC 6 controller using construction injection.

using System.Collections.Generic;
using DomainModel;
using DomainModel.Model;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;

namespace AspNet5MultipleProject.Controllers
{
    [Route("api/[controller]")]
    public class DataEventRecordsController : Controller
    {
        private readonly IDataAccessProvider _dataAccessProvider;

        public DataEventRecordsController(IDataAccessProvider dataAccessProvider)
        {
            _dataAccessProvider = dataAccessProvider;
        }

        [HttpGet]
        public IEnumerable<DataEventRecord> Get()
        {
            return _dataAccessProvider.GetDataEventRecords();
        }

        [HttpGet]
        [Route("SourceInfos")]
        public IEnumerable<SourceInfo> GetSourceInfos(bool withChildren)
        {
            return _dataAccessProvider.GetSourceInfos(withChildren);
        }

        [HttpGet("{id}")]
        public DataEventRecord Get(long id)
        {
            return _dataAccessProvider.GetDataEventRecord(id);
        }

        [HttpPost]
        public void Post([FromBody]DataEventRecord value)
        {
            _dataAccessProvider.AddDataEventRecord(value);
        }

        [HttpPut("{id}")]
        public void Put(long id, [FromBody]DataEventRecord value)
        {
            _dataAccessProvider.UpdateDataEventRecord(id, value);
        }

        [HttpDelete("{id}")]
        public void Delete(long id)
        {
            _dataAccessProvider.DeleteDataEventRecord(id);
        }
    }
}

The controller api can be called using Fiddler:

POST http://localhost:5000/api/dataeventrecords HTTP/1.1
User-Agent: Fiddler
Host: localhost:5000
Content-Length: 135
Content-Type: application/json;
 
{
  "DataEventRecordId":3,
  "Name":"Funny data",
  "Description":"yes",
  "Timestamp":"2015-12-27T08:31:35Z",
   "SourceInfo":
  { 
    "SourceInfoId":0,
    "Name":"Beauty",
    "Description":"second Source",
    "Timestamp":"2015-12-23T08:31:35+01:00",
    "DataEventRecords":[]
  },
 "SourceInfoId":0 
}

The data is added to the database as required.

mySql_ercore_aspnetcore_03

Links:

Announcing Entity Framework Core 1.1

https://github.com/SapientGuardian/SapientGuardian.EntityFrameworkCore.MySql

http://dev.mysql.com/downloads/mysql/

Experiments with Entity Framework Core and ASP.NET Core 1.0 MVC

https://docs.efproject.net/en/latest/miscellaneous/connection-strings.html

HowTo: Starting with MySQL EF Core provider and Connector/Net 7.0.4

25 comments

  1. Ah u r using webapi in this example

  2. […] ASP.NET Core 1.0 with MySQL and Entity Framework Core […]

  3. […] via ASP.NET Core 1.0 with MySQL and Entity Framework Core — Software Engineering […]

  4. This is cool, but how do I do a DB First with MySQL? Or is that not so simple?

    1. Hi neokushan

      Should work, but I haven’t tried it.

      https://docs.efproject.net/en/latest/platforms/aspnetcore/existing-db.html

      I will give a go myself, thanks for the question

      Greetings Damien

      1. witoong623 · ·

        I already tried it, it doesn’t work because no DesignTimeProviderServicesAttribute in MySql.Data.EntityFrameworkCore package, in case of SQL server, it is in Microsoft.EntityFrameworkCore.SqlServer.Design package.

      2. witoong623 · ·

        I already tried it, it doesn’t work because it require DesignTimeProviderServicesAttribute to be in package.

  5. Vaclav Elias · · Reply

    Let me tell you some secret🙂

    22th August
    http://insidemysql.com/mysql-connectornet-7-0-4-m3-has-been-released/

    24th August

    HowTo: Starting with MySQL EF Core provider and Connector/Net 7.0.4

    🙂

    1. Cheers thanks for the links, tried this already, but the migrations didn’t work and the extension methods for ASP.NET Core didn’t work for me.

      Greetings Damien

  6. ‘dotnet ef migrations add testMySql’ works fine and it creates a new database in mysql… but
    after ‘dotnet ef database update’ I receive the next message:

    System.NotImplementedException: The method or operation is not implemented.
    at MySQL.Data.EntityFrameworkCore.Migrations.Internal.MySQLHistoryRepository.get_ExistsSql()
    at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
    at Microsoft.EntityFrameworkCore.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
    at Microsoft.EntityFrameworkCore.Tools.Cli.DatabaseUpdateCommand.c__DisplayClass0_0.b__0()
    at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
    at Microsoft.EntityFrameworkCore.Tools.Cli.Program.Main(String[] args)

    any suggestion?

    thank you

    1. Hi Carlos
      Which provider are you using, or are you testing with this src code? You also need to create an empty schemaand user first
      Greetings Damien

      1. thank you for your response

        I’ve tried follow your guide but using the official mysql connector. And now I created a new project based in a previous example but I get the same issue.

        my code is here https://github.com/careuno/newtestEfcoreMysql

        thank you damienbod for your attention.

      2. The migrations do not work for the official provider. Use either of the other 2 and it should work. I use SapientGuardian.EntityFrameworkCore.MySql and this works perfect.

        Greetings Damien

      3. thank you very much, I will use SapientGuardian.EntityFrameworkCore.MySql then
        🙂

      4. “MySql.Data.EntityFrameworkCore”: “7.0.5-ir21” does not work yet

  7. Database.EnsureCreatedAsync() doesn’t seem to be implemented in SapientGuardian drivers…

  8. Thank you for this tutorial

  9. if I try to run the asp.net project I get:
    Cannot read configuration file
    Config File \\?\C:\git\damienbod\AspNet5MultipleProject\src\AspNet5MultipleProject\web.config

    where is this path baked in? I cannot find it in any configs?

    1. Hi Kunga

      The only config should be in config.json which needs to be changed to your path. Delete the project lock file, restore and rebuild. Then it should work.

      Greetings Damien

  10. […] Source: ASP.NET Core 1.0 with MySQL and Entity Framework Core […]

  11. Hello! Thanks a lot for your article!

    Can u help me to resolve some problems? I have console app, but followed your instructions as much close as I can.

    Migrations works well, but I can’t update database.

    The error is here: http://pastebin.com/cHhPBJR2

    Thanks in advance, Nick.

    1. Hi Nick, thanks is your connection string correct?

      Greetings Damien

      1. Hello again. Thanks for your response!

        You absolutely right, some parameters was left from MS SQL connection.
        Other problems was with packages. Running ‘ update-package -pre ‘ solved this.

        Now I have other problem. Trying to select data using IQueriable as method result.

        Got exception says
        Method not found: ‘Void Microsoft.EntityFrameworkCore.Query.ExpressionTranslators.Internal.EndsWithTranslator..ctor()’.

        Stack trace added in this link: http://pastebin.com/hLGejTAp

        Trying to google this give me nothing. Maybe you will have some suggestions?🙂

        Thanks in advance, Nick.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: