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 ofcourse works perfectly against injection attacks, but if we try to unit test the above code using SQLite, the
test will fail with the following
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!