Parameterized Raw SQL Queries with Entity Framework: How to Unit Test with SQLite

Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. This can be useful, because raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model, but it could also be a problem when it comes down to Unit Testing.

You can read more about Raw SQL Queries here: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

By using raw SQL queries though, in any case, it is important to remember to parameterize user input to protect your system from SQL injection attacks.

Read more about SQL Injections in this Microsoft article and in this OWASP page

For example, the following gist file contains a basic raw SQL Query that is using user input the wrong way, and thus, it’s vulnerable to SQL Injection attacks:

To solve this, Microsoft suggests one of the 3 following approaches:

Using SqlParameter

This of course works perfectly against injection attacks, but if we try to unit test the above code using SQLite, the test will fail with the error Message: System.InvalidCastException : Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'Microsoft.Data.Sqlite.SqliteParameter'.
which is self explanatory! An SqlClient.SqlParmeter is not an Sqlite.SqliteParameter!

Using String.Format syntax

Although this may look like String.Format syntax, the supplied value is wrapped in a parameter and the generated parameter name is inserted where the {0} placeholder was specified.

Using interpolation syntax

Same as the previous approach, the supplied value is wrapped in a parameter and the generated parameter name inserted where the {0} placeholder was specified.

The last two approaches, actually allow .NET to wrap the supplied value with the appropriate parameter for each dbContext: SqlClient.SqlParmeter for your running app, Sqlite.SqliteParameter for your tests!