How to change a DbSet table name during runtime?

[DefinitelyADev]

Due to business and the need to support a legacy database, which other legacy systems are dependent so it cannot be altered. There is a convention we use.

For example we have a table called Things which has an Id of type varchar(10) and Alias of type varchar(whatever)

Id	Alias
ABC1234567	Some Name
ABC7654321	Some Other Name

And those entries each have a corresponding table with the name Thing_{id}
For example:

• Thing_ABC1234567
• Thing_ABC7654321

The only thing that works is if I do myContext.MyDbSet.FromSqlRaw("SELECT * FROM myschema.Thing_{id}").
When using myContext.MyDbSet.FromSqlInterpolated($"SELECT * FROM myschema.Thing_{id}") it throws an error

Executing DbCommand [Parameters=[p0='ABC1234567' (Size = 4000), @__p_1='10'], CommandType='Text', CommandTimeout='30']
SELECT `m`.`Id`
FROM (
    SELECT * FROM myschema.Thing_@p0
) AS `m`
LIMIT @__p_1

MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ABC1234567'
) AS `m`
LIMIT 10' at line 3
 ---> MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ABC1234567'
) AS `m`
LIMIT 10' at line 3
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 816
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 49
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 133
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 437
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 60
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 310
   at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 304
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

EF Core version: 5.0.2
Database provider: Pomelo.EntityFrameworkCore.MySql
Target framework: .Net Core 3.1
Operating system: Windows 10 Pro
IDE: Visual Studio 2019 16.9.2

[roji]

The only thing that works is if I do myContext.MyDbSet.FromSqlRaw("SELECT * FROM myschema.Thing_{id}").
When using myContext.MyDbSet.FromSqlInterpolated($"SELECT * FROM myschema.Thing_{id}") it throws an error

The point of FromSqlInterpolated is to perform parameterization; anything specified as {id} or similar will eventually be sent as a parameter in your SQL statement. However, database don't support parameterization of names of tables or columns as you are attempting to do; so you have to perform actual string interpolation/concatenation in order to generate your SQL, using FromSqlRaw.

Note that working this way has various drawbacks. For one thing, you must make absolutely sure that id is a completely safe string, and not something coming from user input; otherwise, users may be able to perform SQL injection attacks. Aside from that, sending queries with different tables in this way defeats both SQL Server's and EF Core's caching mechanis, since the SQL is different in both cases. I'd strongly suggest reviewing your schema strategy around this, if possible.

[DefinitelyADev]

Yeah I know it's a bumme...
Any chance of DbSet.ToTable(string) returning? Even as an extension method?
Or maybe make it available if the user called AsNoTracking() first?

[roji]

If what you need is to get the table name for a given DbSet, it may be sufficient to just look into the model and get it with the entity's CLR type:

var tableName = context.Model.FindEntityType(typeof(Blog)).GetTableName();

Where Blog is the CLR type whose table you're looking for. You can easily make this into an extension method.

Whether the query is tracking or not doesn't really have any bearing on this.