Everything You Need To Know About EF Core 8 Raw SQL Queries
Vložit
- čas přidán 25. 07. 2024
- ☄️ Master the Modular Monolith Architecture: bit.ly/3SXlzSt
📌 Accelerate your Clean Architecture skills: bit.ly/3PupkOJ
🚀 Support me on Patreon to access the source code: / milanjovanovic
EF Core is one of the best ORMs out there. And it's getting an excellent new feature allowing you to query unmapped types. You can return data with SQL queries, views, functions, and stored procedures. EF Core is coming closer to Dapper with this feature, although the performance isn't there yet.
And I'll talk about SQL injection attacks and if there's a vulnerability.
Join my weekly .NET newsletter:
www.milanjovanovic.tech
Read my Blog here:
www.milanjovanovic.tech/blog
Subscribe for more:
/ @milanjovanovictech
Chapters
0:00 What are Raw SQL Queries?
0:47 Introducing SqlQuery
1:46 What about SQL Injection?
3:41 Composing SqlQuery with LINQ
4:37 Introducing SqlQueryRaw
6:08 Creating a database view
6:47 Querying views with EF8 SqlQuery - Věda a technologie
Want to master Clean Architecture? Go here: bit.ly/3PupkOJ
Want to unlock Modular Monoliths? Go here: bit.ly/3SXlzSt
Amazing as usual, thanks for keeping us updated
My pleasure, glad you enjoyed it 😁
Excellent content! Thank you Milan.
Any time :)
Excellent video .Thanks you for shared your knowledge .I hope to have more updates related.the topic
More to come!
very good feature I was waiting
Thanks
hey milan! hope you are good. Could you please make one video on how to get data in nested form using ef with stored procedures or raw sql.
Yes
Hi Milan, thanks a lot for your videos 👍.
I'd like to know what in the best way to UNION using EF core ? even if the union was with the same table.
because I have an issue using it
I think we have a Union method in LINQ that will get the job done? What issue are you running into?
@@MilanJovanovicTech Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.
sorry youtube deletes the sample code
Hi there ,first of all ,excellent video a question what it would be like with stored procedure in ef core 8?
You can use them to query unmapped types using this feature
How would it work with joins in Sql ? For example, if the view would return fields from multiples tables, each being mapped to different entities. From what I see, the SqlQuery would materialize only one entity.
Which can be a nice thing!
You gotta map it manually 🤷♂️
How does it know to map the fields from the class OrderSummary to the fields from the database ? Like TotalPrice to total_price ?
The mapping is done by convention: so TotalPrice -> total_price falls under that
How can I configure ModelConfigurationBuilder.DefaultTypeMapping?
I am getting this error
System.InvalidOperationException: The element type 'AuthAPI.Entities.User' used in 'SqlQuery' method is not natively supported by your database provider. Either use a supported element type, or use ModelConfigurationBuilder.DefaultTypeMapping to define a mapping for your type.
Thanks in advance
Try adding some constructor
is there a github repo for this? I am wondering what the OrderSummary class looks like.
I share the code on Patreon
Hi Milan, is ef core doesn't support union AsQueryable?
Of course it supports it
@@MilanJovanovicTech i mean similar issue with this example:
var blogs = await _bloggingContext.Blogs
.GroupBy(p => p.BlogId)
.Select(p => p.First())
.Select(p => new { S = p.BlogId })
.ToListAsync();
Throw an error:
The given key 'EmptyProjectionMember' was not present in the dictionary
Don't like the usage of the formattable string. This means it easy to move the declaration and initialization of the sql outside the invocation, implicitly declaring it with var will change the formattable string to a string, and suddenly, you have sql injection again. Is there an analyzer to notify you about this kind of usage?
One problem with FormattableString is when a junior programmer or someone who has become very used to this approach, suddenly writes codes a place where it's not supported. Then the assumptions can easily be in a way that opens up for injection attacks or other issues.
@ building the sql dynamically is also out of the question!
@@svorskemattias is not this what EF does behind curtains? If generating sql in your application is a problem, move all the logic to the DB and call store procedures only, just like in the old days. Regardless, I agree the formatable route might be not the best way to go. I still prefer defining those parameters "the dapper" or cosmos sdk way (with a specific extension)
@@XXnickles what do you mean? building sql dynamically? In that case, yes of course it is. I don't think dynamic SQL is a problem. I'm just saying it's not easy to use while retaining the "magic" of the formattablestring-thing. Someone who has chosen to write rawsql might very well come across a situation where she needs dynamic SQL, and might in a hurry forget about the formattablestring, factor out the SQL, and then open up for sqlinjections.
So even though it might look nice, it is pretty impractical, and also dangerous imho. I think it is a risky thing for the ef core team to do. I hope they ship some really good analyzer with this, so that I can get a compiler error if I accidently misuse it, but I don't know how they would do that. :)
No idea about an analyzer, but that's a noticeable drawback 🤔
You don't know where to use Repository pattern. It is the best in its place.
Maybe.
then ppl can stop talk about sql injection on interpolated string
Because it isn't an interpolated string - it's a FormattableString
@@MilanJovanovicTechoh yea right, if only inside FormattableString like .SqlQuery*** 😅😅
@@enricoroselino7557 You can't pass a string to that method either way, so it's relatively safe. But if devs want to f-up something, there's always a way
I don't think it's a good thing for EF Core 8 to go to Raw SQL. ORM has a strong gender advantage for Entity, and Raw SQL can't report errors at compile time.
It's just an extra feature though
🧱