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?WT.mc_id=DT-MVP-5004591

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:

var username = "george";//coming from UI

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs WHERE username = '" +username+"'") // <- vulnerable to sql injection attacks
    .ToList();

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

Using SqlParameter

var username = new SqlParameter("username ", "george");

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs WHERE username = @username", username )
    .ToList();

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

var username = "george";

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs WHERE username = {0}", username )
    .ToList();

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

var username = "george";

var blogs = context.Blogs
    .FromSql(@"SELECT * FROM dbo.Blogs WHERE username = {username}")
    .ToList();

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.SqlParameter for your running app, Sqlite.SqliteParameter for your tests!