---
title: "FormattableStringFactory - Creating dynamic FormattableString instances"
description: "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."
updated: 2024-08-19
tags: ["C#", ".NET", "EF Core", "FormattableString", "SQL"]
canonical: "https://steven-giesel.com/blogPost/f824cfce-2da4-4037-9f4e-ff652f0ee124/formattablestringfactory-creating-dynamic-formattablestring-instances"
---

# 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`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.sqlquery?view=efcore-8.0) 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. 

```csharp
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. 

```csharp
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`](https://learn.microsoft.com/en-us/dotnet/api/system.runtime.compilerservices.formattablestringfactory?view=net-8.0) to create dynamic `FormattableString` instances from a given string!

```csharp
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:

```csharp
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.