Stored Procedures with Entity Framework Core in .NET 6 🚀
HTML-код
- Опубликовано: 13 июл 2024
- 🚀 .NET Jumpstart Course: www.udemy.com/course/net-core...
📧 Newsletter: newsletter.patrickgod.com/
❤️ Ko-fi: ko-fi.com/patrickgod
☕ Coffee: www.buymeacoffee.com/patrickgod
💻 GitHub: github.com/patrickgod/EfCoreS...
🐦 Twitter: / _patrickgod
📓 Articles: / patrickgod
🎥 Skillshare: www.skillshare.com/r/user/pat...
🔥 Blazor E-Commerce Course: www.udemy.com/course/blazor-e...
🔥 Blazor Bootcamp: www.udemy.com/course/blazor-w...
💡 Online Course Lab: www.kristinagod.com/online-co...
Table of Contents:
00:00 Stored Procedures with Entity Framework Core in .NET 6 🚀
01:02 The Web API Project with a SQL Server DB
03:18 Call a simple Stored Procedure with no Parameters
04:54 Call a simple Stored Procedure with Parameters
08:34 Call a Stored Procedure with an Update and two Parameters
11:34 A Sidenote on Affected Rows
🙏MANY THANKS TO THESE LOVELY PEOPLE:
@jacoviza
Thomas McMeekin
Ameet Jayawant
JesperNielsen
Paul
@Alaffer
Alvin Majid
Slava Ukrayini
Piotr Franczak
Jan Trichter
Tom
H. Ali
Ashley Gahl
Daniel Viveros
Mehmet Ali Ege
Winther
harry j
@Alaffer
Sam Catlow
Jim
Brian Williams
Gonzo
Jonas
Ariel Sartor
Andy Benson
@stevelikespies
Raphael Lukas
James Goforth
@williamdevine
Doug Nelson
Arif Hasanov
@kohji1712
@dlb78dlb
Thomas Korsgaard
FastTrak
Jeff Sager
Dan Moyer
AlgoMarket
Mennskr
Simon
Michael Mel
Hermann Rösch
Jim McNeal
Kieron
@mgrainger231
Gerrit Esmeijer
Axel
Kareem Skinner
Joaquim
Alain Legrand
Per Rieland
@prodrigu75
Greg
Brendan Maunder
Stageoner
Andrew Campbell
pascalditzfeld
PovilasSimanskas
jenö
Marco Otilli
Mark
l33ter
alibux
g.dassel
Luke Vincent
mrcl
Alain Legrand
Thomas Werner
István Csibor
Grant Burdon
@gianluigiconti
Gerrit Esmeijer
Illia
Németh-Pók István
Michael Mel
Marko Zdilar
GerryB
@DADA_universe
Domen Hren
@heroesch
Carey
Peter De Tender
Baris Keskin
@Big_Bear_27390
Mathias
walter e watkins
Günther Dassel
Axel
Josh
Daniel Huber
Fredric Adell
Juan Carlos
Manolis
Pajani
P.
Jonas Granlund
Bobby
Kristina
@xamarin007
#DotNet #EfCore #PatrickGod
Always use the ExecuteSqlInterpolatedAsync and the FromSqlInterpolated instead of the older methods shown in this video. This will defend your API for SQL Injection attack.
Good point! Thanks for sharing that! ☺️
Wouldn't using a stored procedure do this as well? Assuming you're not using dynamic sql of course.
Thanks for sharing your time and knowledge with the community, as always an excellent video.
Thank you!!
Thank you!
Nice explanation 👍
I always use Scaffold-dbcontext rather than migrations as I find it a much more clean work flow. Is there a way to get the sprocs now?
I recall we used to be able to do this back in the day (was it ef or linqtosql?). It was great, it generated a method on the context with mapped parameters and return types 👌
In particular, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw. The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.
A wise thing. Better than having a visit from the user ... Timmy"; Drop table users
:)
It's clear explanation 👌
Thanks
How would it be if the stored procedure has several parameters but some are optional or depending on what is required only some may be needed?
Thanks , Can you tell if Oracle Entity Framework also we can work with stored Procedures ?
and can you show one example of inner join in stored procedure ?
Is there a way to call a stored procedure that returns compounded data (union of tables) without using third-party nuget package? TIA
most stored procedures return info from multiple table sources. EF Core cannot use sp's with JOINS in them or passing multiple parameters, correct?
Hi Patrick could you please record a session on CRUD operation with YugabyteDB in .Net Core 6
Hi Patrick, i hope you are fine & doing well,
is it safe to pass parameters as you passed to query? i hope there isn't any SQL Injection Issue?
please confirm about SQL Injections with these queries... if any then how to avoid ?
Thanks
Thanks you, this video is very good.
Can you create a video subject ' hangfire with .Net'.
do you have any example with output parameters?
thanks for sharing this, you are awesome...
Thank you so much. Appreciate it. 😊
Hello my friend, you have any example whit call stored procedure from project in clean arquitecture?
what is best for heigh performance use store procedure or else what ??
Please make one video of order and order details using store procedure
thanks you, very good.!!!
Thanks! Glad I could help! 😊
One thing I'm stuck on, and isn't shown in this video is how to run a SELECT SP that returns a set of results that doesn't correspond exactly to a table. It's a complex stored procedure that returns a series of joins, etc.
ExecuteSqlRawAsync only returns the number of rows affected and I can't run [TableName].FromRawSql since there is no corresponding scaffolded class (I'm using DB first since the database is huge and already existing).
Switch to Dapper, for that sort of thing especially if you are just doing read only operations. Even when you want to update the data you can use to Dapper acquire the objects with raw sql or SPs then attach those objects to a DbContext if you want to update them.
if your procedure returns more than one dataset, you can convert that complex data to json (FOR JSON PATH), map the procedure result to a keyless dto and then deserialize
Try EF Core Power Tools to scaffold stored procs into code. This will show you how the fields and input parameters are configured for the query.
@@robsonarruda3722 Hey bro, can you please tell me how to do it. I tried but it didn't work I guess I am doing something wrong. Please explain this in more detail.
Can i do it using dotnet cli ? I use linux, and can't do it using visual studio
is the FromSqlRaw safe? against SqlInjection?
As others have poineted out you should have parameterised your userId: var p1 = new SqlParameter("@userId", userId); To prevent sql injections.
Hi,
You can return objects with a relationship using a keyless entity.
Hi Yazan, I've been looking for a guide on using stored procedure to return object into my viewmodel(keyless entity). Appreciate if you can provide a guide on how to do this? Thanks!
Thank you very much , only one question , what is your professional opinion regarding using Stored Procedures or coding the logic on service layer side ? appreciate your answer very much.
Imo, stored procedures are a great way to support quick changes to your T-SQL code by you or third-parties, but most of the time you'll get locked-in to database vendor and in the long run this might become an issue.
SP is the best way of doing things. Faster, clearer, no need to learn any custom tech to do something you already know (sql), and no intermediate layer like ef dapper or anything. You need to know C#, .net, sql. Be good at that. This video (7:00) is a good example of the limitations of an ORM. Who is going to change DB in practice? From sql server to oracle...come on nobody.
Hi Patrick! Great video - thanks a lot. But one very interesting topic is not explained in your video: How can I create a stored procdedure within the DbContext? This DbContext would be the best place to create a stored procedure if a new database will be set up (Code first migrations). Thanks and best greetings from Austria. Jürgen
You could create an empty migration, and add the code to create the SP. when you execute the migration, the SP are going to be created. Is the best way to create the SP from EF.
Hey there, glad to meet again.
I am working my way around sql language and still have one major enquiry.
Is there a command to test an SQL query without executing it?
I wish that i would play with my code and to return results without altering my physical data in the server.
Thanks a lot for taking care of this.
if you have SQL that updates a database and you don't want to actually update, start your query with BEGIN TRAN. Then run the SQL. When the sql is done, you can run ROLLBACK TRAN
All of this was done in SQL Server Management Studio
CREATE TABLE [dbo].[Dogs](
[id] [int] IDENTITY(1,1) NOT NULL,
[DogBreedName] [varchar](50) NULL,
CONSTRAINT [PK_DogBreeds] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Dogs(dogbreedname)
values('Basset Hound')
select * from Dogs
---you should see one row for the basset hound
begin tran
insert into Dogs(dogbreedname)
values('Beagle')
select * from Dogs
---you should see two rows now. New row for beagle
rollback tran
select * from Dogs
should see the basset row now
@@bassethoundgang2800 I was only wondering what does this part of your code does or indicate?
CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
thanks a lot
what if the sproc just returns an int?
You're getting that alert because to changed the code logic and you already have the app running with the old code complied.
What is the difference between creating an API to perform these operations or creating an SP to do the same?
Strictly seen you could change the SP without the need of changing any code and therefore without the need of re-deploying the application! BUT I would not suggest doing that
when I add migration and update the database it continuously add the data procedure [dbo].[some random name] which is already exit what is the solution for this please help
Hi you, I want to ask Why direction hitpoints/{characterId}/{hitoints} in HttpGet
Thank you
thank you so much i try it in insert procedure but alwas show error (syntax error at or near "mark") thats first argument ? do you know what that probelm
I am using npgslq
how to write scalar query in .net core 6 ?
I have a stored procedure which contains inner join. I'm confused about which model to pass with _context while calling raw sql?
You would need to create a new model to represent the data returned from the stored procedure.
Good video. Don't see the Github link in the description tho.
github.com/patrickgod/EfCoreStoredProcedureTutorial 😉
And thank you! 😊
This only works if the stored proc result cleanly maps to a known entity, so it's not really real life ready. How do I get EF to analyze the parameters and results of stored procedures, and map stored proc names to methods?
it's better to use Stored Procedures only for UPDATE & DELETE OPERATIONS to avoid first fetch record then call UPDATE OR DELETE method in LINQ...
for fetching data simply use LINQ....
Guessing you can't auto generate classes of SPs and Views like you could in framework entity 6?
I From Nicaragua Excuse my english, hahaha
For all youtubers who post educational content pls avoid background music. It totally annoying
100đ
bro delete this video youre getting ppl sqlinjected