In last few articles, we have seen how EF core can be used with .NET applications. We have seen the code first approach, where C# code was written and the database was generated or accessed using that code, without writing any SQL queries.

In this short article, we will have a look at how SQL queries can be used with EF core.

Why ?

When an application is written using .NET EF Core and LINQ queries, framework (EF core and the database provider) takes care of converting the C# code into SQL queries. There are certain DOs and DONTs which should be followed in order to make queries more performant.

But some applications might have very complex domain objects. A single unit of operation might involve talking to multiple entities via join and then apply some complex operations to get the result set. In such cases, sometimes, it may be desirable to write SQL queries directly to avoid multiple interactions with database and to reduce the size of resultset.

Another reason can be, if an application is getting migrated and the application was already using some SQL statements for complex operations, sometimes it may be a wise choice to tackle one problem at a time. First migration application but use the SQL queries as they were in legacy application. Once migrated application is stable, next step can to check how SQL queries can re-written in the form of LINQ queries – if that is absolutely required.

How ?

In this section, let’s have a look at the methods available to run raw SQL queries.

• FromSqlRaw – takes a SQL query (string) as parameter and creates a LINQ query from it
• ExecuteSqlRaw – is to execute a raw SQL directly (without creating LINQ)
• FromSqlInterpolated – creates a LINQ query based on an interpolated string representing a SQL query.
• ExecuteSqlInterpolated – is to execute a raw SQL directly (without creating LINQ)

Below are some code examples showing how these methods can be used.

Caution: SQL Injection

When app application uses raw SQL queries, it should be careful enough to not allow SQL injection attacks. SQL injection is not possible when a pure LINQ query is written. But when a raw sql is written and it takes some parameters, and if those parameters are coming from external services or other applications, application should always validate them to ensure safety.

As per documentation, it is always better to send a separate parameter and use FromSqlInterpolated or ExecuteSqlInterpolated than concatenating strings to form a query. Concatenation of strings to form a SQL query would increase the risk of SQL injection.

Some Observations

The column names returned by a raw SQL query should match column names of an EF core model. Also for loading related date, it is better to use FromRawSql or FromSqlInterpolated to form a LINQ query and then an INCLUDE should be added to load the related data.

Before using this feature, it is probably better to see if the techniques you are using for unit testing support stubbing or mocking the Raw sql queries somehow. For example, in memory database provider does not support raw SQL queries. So, if you want to use raw SQL, it may sometime mean changing the database provider for unit testing / integration testing.

Thank you for reading. Let me know your thoughts.