missing stored procs when scaffolding mysql using EF Core on .net core and pomelo drivers

Question

I'm trying to use Scaffolding in EFCore against a mysql database where the generated code will be run on Linux CentOS (NetCoreApp framework). I'm developing on Windows with VS2015 Update3.

Tables are successfully reverse engineered, but stored procedures are not included. Without stored procedures, I'm afraid we'll have to ditch EFCore.

---

• Does scaffolding in EFCore against mysql support reverse engineering stored procedures?
• Using any combination of drivers?
• Assuming this functionality turns out to be absent from EFCore currently, do you know of any solution, paid or unpaid, to accomplish this - reverse engineer existing mysql database to be used in netcoreapp on CentOS?

As a sample case, I started with a mysql database with a single table and a parameterless stored proc. I think I'm using the latest EFCore and Pomelo drivers as shown in project.json below...

{
  "version": "1.1.0-*",
  "buildOptions": {
    "emitEntryPoint": true
  },

  "tools": {
    "Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final"
  },

  "dependencies": {
    "Microsoft.EntityFrameworkCore.Design": "1.1.0",
    "Pomelo.EntityFrameworkCore.MySql": "1.1.1-prerelease-10011",
    "Pomelo.Data.MySql": "1.0.0",
    "Pomelo.EntityFrameworkCore.MySql.Design": "1.1.1-prerelease-10011",
    "Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final"
  },

  "frameworks": {
    "netcoreapp1.1": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.1.0-*"
        }
      }
    }
  }
}

And after doing the dotnet restore use the following to invoke scaffolding...

Scaffold-DbContext "server=localhost;user id=root;password=;database=simpledb" Pomelo.EntityFrameworkCore.MySql -OutputDir Models -force

---

After running the scaffolding, in both my real case and in the simple test case, the generated code only contains tables, and does not contain stored procedures.

Answer 1

There is a Known issue with Pomelo and latest version of Entity Framework.

It is working for me with this configuration:

Microsoft.EntityFrameWorkCore 2.0.3
Microsoft.EntityFrameworkCore.Tools 2.0.3
Pomelo.EntityFrameworkCore.MySql 2.0.1

To call the stored procedure in MySql you can use something like:

var values = _context.Test.FromSql("call test_proc2(0)").ToList();

Answer 2

EF Core doesn't scaffold the stored procedures.

Option # 1: to go with FromSQL and configure all needed to execute your stored procedure. The simplest case is when you don't have parameters at all:

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Option # 2: use some wrappers (there is a bunch of implementations already) which allow to configure stored procedure call in a fluent way. For instance, EFCore-FluentStoredProcedure looks like this:

Executing A Stored Procedure

var dbContext = GetDbContext();
dbContext.LoadStoredProc("dbo.SomeSproc")
    .WithSqlParam("fooId", 1)
    .ExecuteStoredProc((handler) =>
    {
        var fooResults = handler.ReadToList<FooDto>();
        // do something with your results.
    });

Handling Multiple Result Sets

var dbContext = GetDbContext();
dbContext.LoadStoredProc("dbo.SomeSproc")
    .WithSqlParam("fooId", 1)
    .ExecuteStoredProc((handler) =>
    {
        var fooResults = handler.ReadToList<FooDto>();
        handler.NextResult();
        var barResults = handler.ReadToList<BarDto>();
        handler.NextResult();
        var bazResults = handler.ReadToList<BazDto>()
    });

Handling Output Parameters

var dbContext = GetDbContext();
dbContext.LoadStoredProc("dbo.SomeSproc")
    .WithSqlParam("fooId", 1)
    .WithSqlParam("myOutputParam", (dbParam) =>
    {
        dbParam.Direction = System.Data.ParameterDirection.Output;
        dbParam.DbType = System.Data.DbType.Int32;
        outputParam = dbParam;
    })
    .ExecuteStoredProc((handler) =>
    {
        var fooResults = handler.ReadToList<FooDto>();
        handler.NextResult();
        var barResults = handler.ReadToList<BarDto>();
        handler.NextResult();
        var bazResults = handler.ReadToList<BazDto>()

    });

int outputParamValue = (int)outputParam?.Value;