MNW #033: EF Core Raw SQL Queries

Read time: 4 minutes

A big thank you to our sponsors who keep this newsletter free to the reader:

This week's issue is sponsored by the Why You Should Join Newsletter. Why You Should Join is a free, monthly newsletter doing deep dives on the most promising startups to join. They receive inside info from Sequoia, Index, and other top VCs about which companies are doing best and recommend just one each month. Prior suggestions Warp and Pinecone have since raised significant follow-on funding - subscribe for free so you don't miss the next Uber or Airbnb.

And by Hasura, an open-source engine that gives instant GraphQL and REST APIs on new or existing SQL Server, enabling teams to ship apps faster.

EF Core is getting many new and exciting features in the upcoming version.

EF7 introduced support for returning scalar types using SQL queries.

And now we're getting support for querying unmapped types with raw SQL queries in EF8.

This is exactly what Dapper offers out of the box, and it's good to see EF Core catching up.

In this week's newsletter, I'm going to cover how to use EF Core for:

Raw SQL queries
Composing SQL queries with LINQ
Executing data modification with SQL

Let's dive in.

EF Core And SQL Queries

EF7 added support for raw SQL queries returning scalar types. EF8 is taking this a step further with raw SQL queries that can return any mappable type, without having to include it in the EF model.

You can query unmapped types with the SqlQuery and SqlQueryRaw methods.

The SqlQuery method uses string interpolation to parameterize the query, protecting against SQL injection attacks.

Here's an example query returning an OrderSummary list:

This will be the SQL sent to the database:

The type used for the query result can have a parameterized constructor. The property names don't need to match the column names in the database, but they do have to match the names of the values in the result set.

You can also execute raw SQL queries and return results with:

Views
Functions
Stored procedures

Composing SQL Queries With LINQ

An interesting thing about SqlQuery is that it returns IQueryable, which can be further composed with LINQ.

You can add a Where statement after calling SqlQuery:

However, the generated SQL isn't optimal:

Another possibility is to combine an OrderBy statement with Skip and Take:

This would be the generated SQL for the previous query:

In case you're wondering, the performance is similar to LINQ queries using Select projections.

I ran some benchmarks, and didn't notice any significant performance improvement.

This feature will be very useful if you're more comfortable with writing SQL or you want to fetch data from views, functions, and stored procedures.

SQL Queries For Data Modifications

If you want to modify data in the database with SQL, you will typically write a query that doesn't return a result.

The SQL query can be an UPDATE or DELETE statement, or even a stored procedure call.

You can use the ExecuteSql method to execute this type of query with EF Core:

ExecuteSql also protects from SQL injection by parameterizing arguments, just like SqlQuery.

With EF7 you can write the above query with LINQ and the ExecuteUpdate method. There's also the ExecuteDelete method for deleting records.

In Summary

EF7 introduced support for raw SQL queries returning scalar values.

EF8 will add support for raw SQL queries returning unmapped types with SqlQuery and SqlQueryRaw.

I like the direction that EF is going, introducing more flexibility for querying the database.

The performance isn't as good as Dapper, unfortunately. But it's close enough that network costs will play the bigger factor.

I will probably be using only EF moving forward since it covers more use cases.

Thank you for reading, and have an awesome Saturday.

Whenever you're ready, there are 3 ways I can help you 🎁

To access all of the source code that I use in my YouTube videos support me on Patreon
For more practical & in-depth coding examples subscribe to my YouTube channel
Promote yourself to 14.000+ subscribers by sponsoring this newsletter (booked out 2 months)

Free .NET Core Conference 📢

Topics will be .NET Core and the open source ABP framework.

You can register here: ABP Conference

You received this email because you subscribed to our list. You can unsubscribe at any time.

Dragise Cvetkovica 2, Niš, 18000, Serbia

Milan Jovanović - MNW #033: EF Core Raw SQL Queries

MNW #033: EF Core Raw SQL Queries

EF Core And SQL Queries

Composing SQL Queries With LINQ

SQL Queries For Data Modifications

In Summary

Older messages

MNW #032: How To Use Rate Limiting In ASP .NET Core

MNW #031: Implementing The Saga Pattern With Rebus And RabbitMQ

MNW #030: How To Publish MediatR Notifications In Parallel

MNW #029: Creating Data-Driven Tests With xUnit

MNW #028: Using Multiple EF Core DbContexts In a Single Application

You Might Also Like

DeveloPassion's Newsletter #180 - Black Friday Week

Meet HackerNoon's Latest Features: Boost Stories with Translations, Speech-to-Text & More

The ultimate holiday gadget gift

Deduplication in Distributed Systems: Myths, Realities, and Practical Solutions

How to know if your data has been exposed

⚙️ Amazon and Anthropic

⚡ THN Recap: Top Cybersecurity Threats, Tools & Tips (Nov 18-24)

Researchers Uncover Malware Using BYOVD to Bypass Antivirus Protections

Post from Syncfusion Blogs on 11/25/2024

Is there more to your iPhone?