MySQL and Microsoft Entity Framework 8 attaching default database name

Question

I'm struggling with EF8 and MySQL because EF keeps prepending the default database name specified in connection string to every table name, even if the table is in the different db. Below is the detailed description: I have a MySQL server with multiple databases (db1... db10). The default database is db1 and it is specified in the connection string. The 2 tables participating in the join are in db2 and db3 databases. No matter what I do the sql generated from LINQ ends up as:

SELECT ... FROM db1.db2.Table1 INNER JOIN db1.db3.Table2 or
SELECT ... FROM db1.Table1 INNER JOIN db1.Table2.

I use EF8 and Pomelo EF Core. I tried setting SchemaBehavior(MySqlSchemaBehavior.Ignore) and MySqlSchemaBehavior.Translate but EF keeps prepending the database name specified in the connection string. If I ommit specifying the default database in the connection string, then an exception will ensue. Thanks !

Code:

builder.Services.AddDbContext<SubscriberDbContext>(options =>
    options.UseMySql(mySQL, new MySqlServerVersion(new Version(8, 0, 0)),
        b =>b.SchemaBehavior(MySqlSchemaBehavior.Ignore)
    )
    .LogTo(Console.WriteLine, LogLevel.Debug)

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented May 22 at 19:42
What code/configuration are you trying to use to tell EF to use a different database? If you want to do something like DB per-Tenant with EF you cannot use a stored connection string, but rather need to build and pass it to EF on DbContext initialization. From your description you seem to want 1 DbContext to query across multiple databases. AFAIK this isn't possible, and is a very expensive proposed schema when each database is responsible for its own locking etc. Each database should have its own DbContext or consider a single DB /w schemas for separation. — Steve Py
– Steve Py, Commented May 22 at 20:47
the default database name that's not the default database, it's the explicitly specified database that EF connects to. EF isn't a database driver. It's an ORM and deals with entities, not tables. You can't map entities to tables in different databases. After all, some databases don't allow cross-database queries (eg PostgreSQL). You can map to different schemas though. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 23 at 14:25
If you use a database-per-tenant model you'll have to use a different connection string for each database. You can configure multiple connection strings in your settings or you could construct the connection string on the fly in the AddDbContext lambda. At this point there may not be enough information to decide what database to use. You'll have to explain what the actual problem is, not how you thought it would be solved — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 23 at 14:33
If you do need to use different tables from different databases in the same model, you could create views in db1 that map to the db2 tables, and map your entities to the views. Those views should be updatable as long as they return the primary keys. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 23 at 14:47

SpicyCatGames · Accepted Answer · 2025-05-25 16:48:38Z

0

Must use custom ISqlGeneratorHelper:

builder.Services.AddEntityFrameworkMySql();

builder.Services.AddSingleton\<ISqlGenerationHelper, CustomMySqlSqlGenerationHelper\>();

then

public sealed class CustomMySqlSqlGenerationHelper : MySqlSqlGenerationHelper
{
    public CustomMySqlSqlGenerationHelper(
        RelationalSqlGenerationHelperDependencies dependencies,
        IMySqlOptions options)
        : base(dependencies, options)
    {
    }

    public override string GetSchemaName(string name, string schema)
        => schema; // <-- this is the first part that is needed to map schemas to databases
}

edited May 25 at 16:48

SpicyCatGames

2,0121 gold badge21 silver badges39 bronze badges

answered May 23 at 21:40

Sergey Or

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL and Microsoft Entity Framework 8 attaching default database name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related