String interpolation inside a formattable string in C#

Question

I am in the process of replacing Dapper with EF Core in a project. Specifically using the new Database.SqlQuery and Database.ExecuteSql functionality with EF Core. I like how they use FormattableString to handle SQL parameters.

However I am confused on what to do when I would like to use both string interpolation and the curly braces for parameters with the FormattableStrings? Se example below of a Dapper version and a non-working EF Core version. I would not like the "subSql" to be passed as a parameter but just to create the string. How would I approach this?

public IEnumerable<Something> GetSomethingWithDapper(string id, bool enabled, string type)
{
    var subSql = switch (type) {
        case "A":
            "TableA";
            break;
        case "B":
            "TableB";
            break;
    };
    
    var sql = $"SELECT * FROM {subSql} WHERE Id = @id";
    
    // My dapper request here
}

public IEnumerable<Something> GetSomethingWithEFCore(string id, string type)
{
    var subSql = switch (type) {
        case "A":
            "TableA";
            break;
        case "B":
            "TableB";
            break;
    };
    
    FormattableString sql = $"SELECT * FROM {subSql} WHERE Id = {id}";
    
    // My EF Core request here
}

Answer 1

The equivalent to Dapper's QueryAsync<Something>(sql,id) is SqlQueryRaw, not SqlQuery or FromSql. SqlQuery and FromSql will treat all parameters in a FormattableString as query parameters. There's no way to tell that some of those parameters should be used for string interpolation and some for other things.

With SqlQueryRaw, the equivalent to Dapper's con.Query<int>(sql,new {id}) is context.Database.SqlQueryRaw<int>(sql,new{id}) eg:

var id= new SqlParameter("id", 45);
var ids = context.Database
    .SqlQueryRaw<int>("SELECT Id from TableX where id=@id",@id)
    .ToList();

Or

var sql=$"SELECT Id from {table} where id=@id";
var ids = context.Database
    .SqlQueryRaw<int>("SELECT Id from TableX where id=@id",id)
    .ToList();

This is also shown in the EF SQL query docs, in the Dynamic SQL and Parameters section:

If you've decided you do want to dynamically construct your SQL, you'll have to use FromSqlRaw, which allows interpolating variable data directly into the SQL string, instead of using a database parameter:

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();