Reverse engineer/scaffold stored procedures

[WalterW]

The tooling of SP mapping with EF 6x is great. Please migrate it over to EF code so it can be easier for developer.

[ajcvickers]

@WalterW Which tooling are you referring to, specifically?

[WalterW]

Specially the Model Browser which available for EF6x. The goal is have a easy method to map and generate a c# method for SP. One of the stopping block for us to start a project in .Net Core is because of this due to productivity. Spoke to @divega on this matter earlier of the week in campus.

[divega]

My understanding (@WalterW correct me if I am wrong), is that you would like to see EF Core's DbContext scaffolding becoming capable of generating methods for each stored procedure in the database that return entity results.

[WalterW]

Scaffolding to generate method just one part of it, another part is to have a tool such as Model Browser to refresh the custom entity object (complex types) every time SP change the return values.

[ajcvickers]

Triage:

• Reverse engineer stored procedures - this issue
• Update model from database - Update model from database #831
• Visual tool such as the Model Browser - this is something we have no plans to implement.

[WalterW]

"Visual tool such as the Model Browser - this is something we have no plans to implement."

@ajcvickers interesting.... any specific reason on this?

[ajcvickers]

@WalterW We have found visual tools (especially those in Visual Studio) are very expensive to both build and maintain. So, even with a bigger team, I'm not sure the ROI would be worth it, but with the current team size there is no way we can take on anything like this.

[WalterW]

@ajcvickers understand. Thanks for the information.

[jwr456]

pretty please add this!

[Lupin1st]

We are eagerly waiting for the support of scaffolding stored procedures with EFCore to migrate parts of our application from EF6 to EFCore. For our project, there is no need for a visual tool, a simple whitelist like the current “-tables” parameter should be enough. What we cannot discern of the conversations in this and other issues is how far the work for the support of Views and Stored Procedures has progressed and how likely it is that parts of this get implemented for 3.0.

[ErikEJ]

@Lupin1st How would you expect a reverse engineer of stored procs to work - can you supply a code sample or the proc and the corresponding generated code?

[jwr456]

Perhaps as it does in EF6? Get Outlook for iOS<https://aka.ms/o0ukef>

…

________________________________ From: Erik Ejlskov Jensen <notifications@github.com> Sent: Thursday, May 9, 2019 12:27 PM To: aspnet/EntityFrameworkCore Cc: Joel Roberts; Comment Subject: Re: [aspnet/EntityFrameworkCore] Reverse engineer/scaffold stored procedures (#15105) @Lupin1st<https://github.com/Lupin1st> How would you expect a reverse engineer of stored procs to work - can you supply a code sample or the proc and the corresponding generated code? — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#15105 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/ACMKFFAIDYL52VTAVJJAAMTPURNHPANCNFSM4HAHP2NQ>.

[gulshan]

EF6 is being ported to .net core. It can be an option for some scenarios.

[Lupin1st]

I have created a small project with a simple use case for a procedure mapping sample that fits our needs. Functions could be generated in a similar way but should return an IQueryable object.

We currently use EF6 and will surely port to EF6 on .Net Core 3.0 when its available. But we still want to migrate new modules to EFCore as well.

Concerning the way procedures should get generated from EFCore scaffold. For us the procedures and functions generated from the Entity framework 6 work well enough but using EFCore could probably make a meaningful difference for our project.

Our application uses many hundred tables and Views and even more procedures and functions. Most of our legacy code uses only procedures to access data. For newer Modules however, we decided to use procedures only for performance reasons and query with EF6 queries. We want both, queries and procedure/function calls to get replaced by EF Core equivalents, at least for new modules. We do not make changes to the generated EF6 Models, so we use the designer only for selecting which objects should get imported.

The reasons we do not want to stick with EF6 are as following (ordered by priority):

• Larger EF6 contexts need a lot of time to instantiate and therefor our contexts must stay small, so we have many small EF6 contexts which are not very convenient to use.
• The edmx designer is quite slow and inconvenient to use. Instead a single command that generates all db objects as well as a faster one that updates just one db object would be perfect for us.
• Merge conflicts for the edmx code often force us to select all objects for a context again.
• The generated procedures are not async.
• The ObjectParameter is not generic.

For generating the procedures and functions, it would be enough for us to use the following SQL-Server objects.

[sys].[dm_exec_describe_first_result_set_for_object]:
For the result sets for each programmability object.
This has however some restrictions in comparison to the way EF6 searches for result sets which are no deal breaker for our project. Some restrictions are: Only available for SQLServer 2012+ and throws error for procedures that use TempTables directly or indirectly

INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS:
For the method parameters for the programmability objects.

[harley333]

We also use EF6 and rely on the support of stored-procedures. Stored-procedures provide a level of control that generated SQL cannot provide. (This is not a limitation of ef-core; it's merely the nature of the beast. Some times, you actually need to manhandle the SQL.) And we also have hundreds of tables.

Concerning the problems mentioned by @Lupin1st:

• Slowness of instantiation is a primary concern.
• We never use the EDMX designer. And I agree with the general assessment of visual-designers; they're not worth the trouble.
• We have a homegrown command-line tool to generate the EDMX, which alphabetizes everything with the EDMX. This alleviates merge-conflicts (the built-in visual-designer will re-sort the EDMX in bizarre ways).
• We've customized the EDMX-dependent T4 templates. Async procedures are doable.
• We haven't encountered a problem with ObjectParameter.

Our homegrown tool (written in C#) pulls the model from the database and generates POCO objects which (when serialized as XML) generate the EDMX. To be clear, we have a collection of simple objects to represent the database (Schema, Table, Column, StoredProcedure, etc.); and we have a collection of simple objects to represent the EDMX (EntitySet, Entity, Property, ImportFunction, etc.).

Secondarily, the tool provides a simple mechanism to pass the model into T4 templates (which can be specified by config-file or on a command-line). Then, each T4 template can generate whatever it wants using the information from the model. (We have a series of templates which enforce our database-conventions. For example, one template will rename any foreign-key which doesn't match our convention. Another template will ensure audit-columns (ChangeDate, CreateDate, etc.) are added to each table. Another template generates CUD stored-procedures.)

Plus, as I mentioned earlier, the T4 templates which are dependent on the EDMX have also been customized. Our customizations enable us to use SQL extended-properties for customized purposes as well as things like passing user-defined table-types into stored procedures. Also, our customizations generate resource-files for each entity (easing the support of whole-platform localization). These resource-files are resistant to code-regeneration (if a resource-entry for the FirstName column already exists, don't overwrite it).

Overall, I feel like we've taken ownership of the EF6 experience. And yet, the EDMX still feels overly weighty.

Conceivably, we could use our tool to generate ef-core "code-first" code; but the lack of stored-procedure support is a major loss.

[ajcvickers]

Thanks for the detailed info @harley333 and @Lupin1st. This will be useful when we get to this in EF Core.

[anomepani]

Any updates on scaffolding StoredProcedure in EF Core?

[ajcvickers]

@anomepani This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 5.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[DarioN1]

The "partial" support for the stored procedures is the reason why my company is still not migrating to aspnetcore the existing projects and the new starting projects...
We wait with confidence!

[DarioN1]

Guys, have a look there:

https://github.com/DarioN1/SPToCore

I have created a kind of scaffolder for Stored Procedure, I'm using it in a new project that uses an existing database with more than 300 stored procedures.

At the moment it works very well...

Let me know your opinion !

[jyeagle]

We are eagerly waiting for this! 1000+ stored procs / functions make it a tough move to switch to EFCore.

This is the biggest missing feature vs EF6 for us.

[ErikEJ]

@jyeagle it is possible today with EF Core Power Tools...

[jyeagle]

@ErikEJ I saw this, but unfortunately we are not using EF Core 3.1 yet....trying to find a 2.1 solution.

[blogcraft]

@ErikEJ When I try to Reverse Engineer a Stored Procedure (witch runs other SPs inside) with EF Power Tools (latest version), it fails and then the Output also fails, so I cant get to se why. Any guess?

[ErikEJ]

Noting in the VS output window?

Have a look at the docs and the source code, most likely calling sp_describe_first_resultset fails or sys.parameters returns an unexpected value.

@blogcraft

[blogcraft]

@ErikEJ Yeah, there was a second error "Unable to log error to Output Window".

Thanks a lot! sp_describe_first_result_set helped in finding the problem! There was a Temp Table in one of the internal SPs that caused the problem.

[ErikEJ]

Docs on this feature in EF Core Power Tools https://github.com/ErikEJ/EFCorePowerTools/wiki/Reverse-Engineering#sql-server-stored-procedures

[AraHaan]

I am eagerly waiting for this as well so I can migrate some of the least performant code dealing with my db in my C# on over to an SP.

[roji]

@AraHaan this is just about scaffolding stored procedures, and should not be blocking from using them. Stored procedures can already be managed in migrations via raw SQL.

[AraHaan]

while that is true, it does not go around to generate the code to call it on the .NET side (I think).

[ErikEJ]

@AraHaan EF Core Power Tools can do that for you today (as already mentioned above)

[marafiq]

@ErikEJ does efcore power tools have cli command to generate along with ef scaffolding

[AraHaan]

as much as I like the efcore power tools, I would rather use stuff that is built into the actual scaffolder more and feel it would be better that way if the power tools functionality was actually 1:1 with the scaffolder so that way the power tools become optional, but you still get the same exact functionality in the default scaffolder as well.

[ErikEJ]

@marafiq It does, yes: https://github.com/ErikEJ/EFCorePowerTools/wiki/Reverse-Engineering#requirements

[marafiq]

Ah I see. Thanks It would be great if the underlying packages are published on nugget.

[yori-s]

In a somewhat extreme case, I'm looking at data access options for a project where the db is siloed behind sprocs (so more like generating a web service client than a traditional ORM).

Is this a use case that ef core might eventually support (no linq, change tracking, migrations, or even a domain model)?

[ErikEJ]

@yori-s EF Core Power Tools stored procedure mapping should be able to do that for you...

[subchannel13]

Oh please, I'd really appreciate this! Maybe there is some hoop jumping procedure to make normal Visual Studio run on Linux so I can use Erik's but it would be mighty convenient to this feature in dotnet ef CLI.

[ErikEJ]

@subchannel13 EF Core Power Tools CLI is cross platform and supports stored procedures

[AraHaan]

We also use EF6 and rely on the support of stored-procedures. Stored-procedures provide a level of control that generated SQL cannot provide. (This is not a limitation of ef-core; it's merely the nature of the beast. Some times, you actually need to manhandle the SQL.) And we also have hundreds of tables.

Concerning the problems mentioned by @Lupin1st:

* Slowness of instantiation is a primary concern.

* We never use the EDMX designer.  And I agree with the general assessment of visual-designers; they're not worth the trouble.

* We have a homegrown command-line tool to generate the EDMX, which alphabetizes everything with the EDMX.  This alleviates merge-conflicts (the built-in visual-designer will re-sort the EDMX in bizarre ways).

* We've customized the EDMX-dependent T4 templates.  Async procedures are doable.

* We haven't encountered a problem with ObjectParameter.

Our homegrown tool (written in C#) pulls the model from the database and generates POCO objects which (when serialized as XML) generate the EDMX. To be clear, we have a collection of simple objects to represent the database (Schema, Table, Column, StoredProcedure, etc.); and we have a collection of simple objects to represent the EDMX (EntitySet, Entity, Property, ImportFunction, etc.).

Secondarily, the tool provides a simple mechanism to pass the model into T4 templates (which can be specified by config-file or on a command-line). Then, each T4 template can generate whatever it wants using the information from the model. (We have a series of templates which enforce our database-conventions. For example, one template will rename any foreign-key which doesn't match our convention. Another template will ensure audit-columns (ChangeDate, CreateDate, etc.) are added to each table. Another template generates CUD stored-procedures.)

Plus, as I mentioned earlier, the T4 templates which are dependent on the EDMX have also been customized. Our customizations enable us to use SQL extended-properties for customized purposes as well as things like passing user-defined table-types into stored procedures. Also, our customizations generate resource-files for each entity (easing the support of whole-platform localization). These resource-files are resistant to code-regeneration (if a resource-entry for the FirstName column already exists, don't overwrite it).

Overall, I feel like we've taken ownership of the EF6 experience. And yet, the EDMX still feels overly weighty.

Conceivably, we could use our tool to generate ef-core "code-first" code; but the lack of stored-procedure support is a major loss.

Btw I made a source generator that takes EDMX and writes the code side of it for EG6 AND EFCore, the EdmxSourceGenerator.

[AraHaan]

https://github.com/Elskom/EdmxSourceGenerator/tree/main

https://github.com/Elskom/EdmxSourceGenerator/blob/main/Internal/FunctionImport.cs

Simple I recommend creating dummy .NET Framework project to create and edit the edmx file, then using this source generator with that file set to AdditionalFiles to generate the EFCore code you need.

With this, this issue is technically implemented. Good thing I took the time back when I needed edmx and the edmx code generator built into VS back then was broken 2 years ago now in my Fall Semester where I needed it for my capstone class. So I sat down 1 week and reverse engineered it from scratch by looking at the file's contents and figuring it out and then logged my progress so I got graded on manually fixing this issue with a source generator workaround.