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.

Víctor Melgar · Accepted Answer · 2018-06-01 07:26:10Z

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();

answered Jun 1, 2018 at 7:26

Víctor Melgar

717 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Víctor Melgar Over a year ago

Now it is working ok with the las versión of EntityFramework.

Dmitry Pavlov · Accepted Answer · 2018-04-13 18:40:27Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related