Support stored procedures

[ErikEJ]

From @OmiCron07 on April 2, 2018 15:8

Copied from original issue: ErikEJ/SqlCeToolbox#680

[ErikEJ]

As EF Core does not support stored procedures (to a large extent) - could you give an exmaple of what type of support you are looking for?

[ErikEJ]

From @OmiCron07 on April 2, 2018 15:25

I don't know exactly what you can currently do, but in the meantime, I think I will use this NuGet : https://github.com/verdie-g/StoredProcedureEFCore

Can you list the stored procedures in the database and create some classes or constants with their parameters, because, I don't want to use magic string.

That would be nice to import the stored procedure with its list of parameters and using these with the other library.

[ErikEJ]

Moving to backlog - PRs accepted!

[ErikEJ]

dotnet/efcore#245 (comment)

[intomneato]

Hi @ErikEJ, I'm using Ef Power Tools to reverse engineer my sql server database in a c# Core 2.2 project. It's been great so far, but now I have a query that is just too complicated to try to write in linq. I can put it in a view or a stored procedure but I can't seem to find anything on how to include views or procs in the reverse engineer process. This thread seems the closest dead end I've found. Can you point me in a good direction? Thanks.

[ErikEJ]

Just extend your DbContext manually, and/or use FromSql. EF Core 3.0 can map views for you.

[intomneato]

Thanks. I was able to use FromSql to get something functional for now. I'll clean it up down the road after we update to Core 3.x.

[ErikEJ]

https://github.com/Lupin1st/efcore_scaffold_stored_procedures

[DarioN1]

In the meanwhile, have a look here: https://github.com/DarioN1/SPToCore

and here https://www.c-sharpcorner.com/article/learn-about-stored-procedure-scaffolding-utillity-for-netcore3/

[ErikEJ]

I have now started work on this:

• Get stored procedure model ( thanks @DarioN1 )
• Write result classes ( thanks @Lupin1st )
• Write extended DbContext
• Add scaffolder tests - test based on result set with all possible parameter types
• Add hidden option to generate stored procedures (will generate all initially)

[ErikEJ]

@Tagwerk-Berlin and others - this is now in the latest daily build.

[ErikEJ]

This is now completed for SQL Server.

[intomneato]

Hi Eric, this looks like it could be a great update for us. Since my last question on this thread we've updated our project to Core 3.1 and I'm currently calling stored procedures like my code example below. I've reverse engineered my entities with the new Generate stored procedures mappings box checked and I see the new procedure models all with "Results" tacked on to the name. Most of my procedures don't actually return data, they just handle bulk operations EF struggles with, so their new models are empty. Would this update allow me to call the procedures differently? And if so would you mind a quick example of how you'd handle something like the code below? My procs are not on dbo schema if that causes issues. Thanks.

SqlParameter[] spa = new[] {
new SqlParameter("@account", a.AccountId),
new SqlParameter("@customer", a.CustomerId),
new SqlParameter("@supplier", a.SupplierId)
};
_context.Database.ExecuteSqlRaw("EXEC nsd.Account_Suspend @account, @customer, @supplier", spa);

[ErikEJ]

@intomneato have you tried it? And have you read the wiki docs?

[ErikEJ]

@intomneato Actually unsure how I deal with procedures with no result... Let me know your findings.

[intomneato]

Yes, I've been messing with it this morning trying to see how I can use the new models but so far I have not figured out how to call the procedures. Should I be able to do something like...
Account_SuspendResult result = _context.Account_Suspend(a.AccountId, a.CustomerId, a.SupplierId);
instead of the code block I posted above? I have the result model, but the proc is still not a known object. I was just wondering if you had some examples or use cases. I did not see anything in the wiki for this, but if you've got a link to documentation I didn't find that's fine too. Thanks.

[ErikEJ]

I just blogged: https://erikej.github.io/efcore/2020/08/10/ef-core-power-tools-stored-procedures.html

[intomneato]

That blog was helpful, though following the first code example in my own project implementation my ContextProcedures does not appear to have any of my procedures as objects within it. Is that due to my procs being in a different schema than dbo maybe?

[ErikEJ]

@intomneato doubt it, maybe because they are not returning results.
Could you share a repro project, with a sample sproc?

[intomneato]

I'm running a little short on time today to spin up a project, but I just tried creating a testing proc with a return value in my real project, after a fresh reverse engineer it did show up in my ContextProcedures.cs file and was an available object to use in code. So I'd say that pretty well confirms that you are right, lack of return value is the problem. I guess I could add a return value to my other procs as a work around, even if it's just a bool to say it finished or if it hit the catch in the proc.

[ErikEJ]

Please create an issue for this, and I will investigate...

[intomneato]

No problem. Issue #449 created. Thanks for your help, EFCorePowerTools really is very helpful and easy to use!