FormattableStringFactory - Creating dynamic FormattableString instances

I recently came across the FormattableStringFactory to create dynamic FormattableString instances. This is a very useful feature in combination with safe SQL queries and the SqlQuery method of Entity Framework Core.

SqlQuery method

Short recap: The SqlQuery method of Entity Framework Core allows you to execute raw SQL queries and map the results to entities. This is useful if you want to execute complex queries that are not possible with LINQ.

var ids = await context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToListAsync();

It wants a FormattableString as input so it can safely interpolate the values into the SQL query and prevent SQL injection attacks. The "problem" is that you can't concatenate FormattableString instances directly. I needed this for a dynamic SQL query. Imagine in the given example above sometimes you want to filter for specific ID's from a given list.

var ids = GetIds();
// If ids is empty, the SQL query isn't valid
var query = $"SELECT [BlogId] FROM [Blogs] WHERE [BlogId] IN ({string.Join(", ", ids)})";

FormattableStringFactory

But we can utilize the FormattableStringFactory to create dynamic FormattableString instances from a given string!

var ids = GetIds();

var stringBuilder = new StringBuilder("SELECT [BlogId] FROM [Blogs]");
if (ids.Any())
{
    stringBuilder.Append(" WHERE [BlogId] IN ({0})", string.Join(", ", ids));
}

var query = FormattableStringFactory.Create(stringBuilder.ToString());
var result = await context.Database
    .SqlQuery<int>(query)
    .ToListAsync();

If your FormattableString contains placeholders, you can pass them in like this:

var query = "Hello {0}!";
var formattableString = FormattableStringFactory.Create(query, "World");

These two mechanisms combined allow you to create dynamic SQL queries with Entity Framework Core in a safe way.