EF Core - Cannot convert from 'string' to 'System.FormattableString'

While doing a pair programming session with a new developer in one of my teams, we ended up with a compiler error after writing the following code:

	var products = context.Products
	.FromSql("SELECT * FROM dbo.Products")
	.ToList();

view raw GetProducts.cs hosted with ❤ by GitHub

This is the error message we got:

Argument 2: cannot convert from 'string' to 'System.FormattableString' 

The fix was easy just add a ‘$’ before the query:

	var products = context.Products
	.FromSql($"SELECT * FROM dbo.Products")
	.ToList();

view raw GetProductsFixed.cs hosted with ❤ by GitHub

However it would not have been a good pair programming session if we didn’t drill down further into this.

What is a FormattableString?

A FormattableString in C# is a type introduced in .NET 4.6. It represents a composite format string, which consists of fixed text intermixed with indexed placeholders (format items). These placeholders correspond to the objects in a list. The key features of FormattableString are:

1. Capturing Information Before Formatting:

   • A FormattableString captures both the format string (similar to what you’d pass to string.Format, e.g., "Hello, {0}") and the arguments that would be used to format it.
   • Crucially, this information is captured before actual formatting occurs.

2. Adjustable Formatting:

   • You can adjust the formatting behavior of a FormattableString, such as performing formatting in the invariant culture.
   • This is useful when you want to control how the string is formatted without actually performing the formatting immediately.

3. Usage with Interpolated Strings:

   • When you use an interpolated string (e.g., $"Hello, {name}"), the compiler will create a FormattableString if you assign it to an IFormattable variable.
   • The IFormattable.ToString(string, CultureInfo) implementation of the interpolated string will use FormattableString

The last key feature explains why adding a ‘$’ sign and creating an interpolated string fixes the compiler error.

FormattableString and EF Core

But that doesn’t explain yet why the EF Core team decided to expect a FormattableString instead of a ‘normal’ string when calling the FromSql() method.

The reason a FormattableString is expected is because it helps you to safe guard against SQL injection, as they integrate parameter data as separate SQL parameters.

Let's have a look at the following example:

	var categoryId=1;
	var products = context.Products
	.FromSql($"SELECT * FROM dbo.Products where CategoryId={categoryId}")
	.ToList();

view raw GetProductsByCategoryID.cs hosted with ❤ by GitHub

Although the query above looks like regular C# string interpolation, the categoryId value is wrapped in a DbParameter and the generated parameter name inserted where the placeholder was specified.

This is the query that got executed in the database:

	DECLARE p0 INTEGER = 1;
	SELECT * FROM Products where CategoryID=@p0

view raw GetProducts.sql hosted with ❤ by GitHub

This makes FromSql safe from SQL injection attacks, and sends the value efficiently and correctly to the database.