Pomelo.entityframeworkcore.mysql: querying across multiple databases

[chris-kruining]

Hi,

I'm trying to modernize our code and choose to use EFCore as our DAL, since we have a mariadb server I choose your lib to connect to it. I however have arrived to an issue. Due to the structure of our data I need to be able to query across not just tables, but also servers. This doesn't need to be something super complicated. All I wish to achieve is that the mysql string outputted from this lib would also include the databases.

Am I just an idiot and is this possible with some configuration? Or is this impossible? Or would this a new feature?

Thanks!

[lauxjpn]

I need to be able to query across not just tables, but also servers.

All I wish to achieve is that the mysql string outputted from this lib would also include the databases.

@chris-kruining Those two statements are not the same, so I am not sure yet, what the actual scenario is you are trying to accomplish:

Do you need to connect to multiple databases on different MariaDB instances, or to multiple databases on the same MariaDB instance?

---

Generally with EF Core (doesn't matter which provider) one DbContext represents only objects from one database. If you want to use multiple databases (that can also be on different MariaDB instances), you would need to use multiple DbContexts (at least one for each involved database structure).
That is the officially supported way to implement this.

However, I demonstrated an (unsupported) workaround for Pomelo, that enables users to misuse the schema mechanism of EF Core (that is unsupported by MySQL and therefore Pomelo) as a means to access multiple databases on the same MariaDB instance.

That's the reason for my question about your exact scenario.

[mguinness]

It's also possible to use FEDERATED Storage Engine, but that's beyond the scope of this library. You should consider asking this question on Stack Overflow where you'll get a broader discussion on how to achieve what you're looking to do.

[chris-kruining]

I need to be able to query across not just tables, but also servers.

All I wish to achieve is that the mysql string outputted from this lib would also include the databases.

@chris-kruining Those two statements are not the same, so I am not sure yet, what the actual scenario is you are trying to accomplish:

Do you need to connect to different multiple on different MariaDB instances, or to multiple databases on the same MariaDB instance?

Sorry, typo, meant to say "but also databases". So yes, multiple databases int the same instance

I tried to implement the example you provided me. And I must say that on moments like these I wonder if I'm either just that dumb or totally blind, because I can't seem to get it working. even with such a clear example.

I have this extension to prevent duplicate code
```C#

public static class Database
{
    public static DbContextOptionsBuilder UseBasicConfiguration(this DbContextOptionsBuilder builder, String connectionString)
    {
        builder
            .UseMySql(
                connectionString,
                ServerVersion.FromString("10.5.8"),
                mysqlOptions =>
                {
                    mysqlOptions
                        .SchemaBehavior(MySqlSchemaBehavior.Translate, (schema, obj) =>
                        {
                            return "";
                        })
                        .EnableIndexOptimizedBooleanColumns()
                        .EnableRetryOnFailure(15, TimeSpan.FromSeconds(30), null)
                        .CharSet(CharSet.Utf8)
                        .CharSetBehavior(CharSetBehavior.NeverAppend)
                        .UseQuerySplittingBehavior(QuerySplittingBehavior.SingleQuery);
                }
            )
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();

        return builder;
    }
}

and this is my `Startup.ConfigureServices`
```C#
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddGrpc();
            services.AddGrpcReflection();
            services.AddApplicationInsightsTelemetry(_configuration);
            services.AddDatabaseDeveloperPageExceptionFilter();

            services.AddCors(options => options.AddPolicy("AllowAll", builder =>
            {
                builder
                    .AllowAnyOrigin()
                    .AllowAnyMethod()
                    .AllowAnyHeader()
                    .WithExposedHeaders("Grpc-Status", "Grpc-Message", "Grpc-Encoding", "Grpc-Accept-Encoding");
            }));

            services
                .AddSingleton<ISqlGenerationHelper, CustomMySqlSqlGenerationHelper>()
                .AddIdentity<Relation, IdentityRole>()
                .AddEntityFrameworkStores<IdentityDbContext>()
                .AddDefaultTokenProviders();

            services.Configure<ServiceSettings>(_configuration);

            if (_configuration.GetValue<String>("IsClusterEnv") == Boolean.TrueString)
            {
                services
                    .AddDataProtection(options =>
                    {
                        options.ApplicationDiscriminator = "unifyned.identity";
                    })
                    .PersistKeysToStackExchangeRedis(ConnectionMultiplexer.Connect(_configuration["DPConnectionString"]), "DataProtection-Keys");
            }

            String connectionString = $"Server={_configuration["DB:Host"]};Port={_configuration["DB:Port"]};Uid={_configuration["DB:User"]};Pwd={_configuration["DB:Pass"]};Database=FYN_1005_General";

            services
                .AddHealthChecks()
                .AddCheck("self", () => HealthCheckResult.Healthy())
                .AddMySql(
                    connectionString,
                    name: "Database connection check", 
                    tags: new[] { "IdentityDB" }
                );

            // I'm very doubtful of these two, this is already handled by IdentityServer4 as far as I know
            services.AddTransient<ILoginService<Relation>, EFLoginService>();
            services.AddTransient<IRedirectService, RedirectService>();

            services
                .AddIdentityServer(options =>
                {
                    options.IssuerUri = "null";
                    options.Authentication.CookieLifetime = TimeSpan.FromHours(2);
                    options.UserInteraction = new UserInteractionOptions
                    {
                        LoginUrl = "/v1/security/authentication",
                        LoginReturnUrlParameter = "returnUrl",
                        LogoutUrl = "/v1/security/authentication/logout",
                    };
                    options.Cors = new CorsOptions
                    {
                        CorsPolicyName = "AllowAll",
                    };
                })
                .AddDeveloperSigningCredential()
                .AddAspNetIdentity<Relation>()
                .AddConfigurationStore(options =>
                {
                    options.ConfigureDbContext = builder => builder.UseBasicConfiguration(connectionString);
                })
                .AddOperationalStore(options =>
                {
                    options.ConfigureDbContext = builder => builder.UseBasicConfiguration(connectionString);
                })
                .Services.AddTransient<IProfileService, ProfileService>();
        }

and lastly the dbcontext (I used IdentityDbContext here, is that the issue?)
```C#
public class IdentityDbContext : IdentityDbContext
{
public DbSet Relations { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.HasDefaultSchema("Location");
        builder.Entity<Relation>().ToTable("Customer", "Location");

        base.OnModelCreating(builder);

    }
}

```

I've set breakpoints on both the "schema resolve" functions, but neither ever get hit.

Am I missing something obvious?

[chris-kruining]

Wait a second, my context's OnConfiguring and OnModelCreating never get hit either, So I guess I am missing some configuration. I'll try to figure this out or I'll put a question on stack-overflow.

[lauxjpn]

@chris-kruining So it looks like the following comment I made in the original code is incorrect:

// In an ASP.NET Core application, the AddSingleton call can just be added to the general service
// configuration method.

You need to add the service to the internal service provider as well when using ASP.NET Core:

```c#
public void ConfigureServices(IServiceCollection services)
{
services.AddRazorPages();

// Create custom internal service provider.
var efCoreServiceProvider = new ServiceCollection()
    .AddEntityFrameworkMySql()
    .AddSingleton<ISqlGenerationHelper, CustomMySqlSqlGenerationHelper>()
    .BuildServiceProvider();

var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1264_IceCreamParlor";
services.AddDbContext<Context>(b => b
    .UseInternalServiceProvider(efCoreServiceProvider) // <-- add our custom internal service provider
    .UseMySql(
        connectionString,
        ServerVersion.AutoDetect(connectionString),
        b => b.SchemaBehavior(
                MySqlSchemaBehavior.Translate,
                (schemaName, objectName) => objectName) // <-- this is the second part that is needed to map
                                                        //     schemas to databases
            .CharSetBehavior(CharSetBehavior.NeverAppend))
    .EnableSensitiveDataLogging()
    .EnableDetailedErrors());

}
```

I uploaded a fully working sample as PomeloIssue1264.

---

In addition to that, just by looking at your source code, I don't see an AddDbContext<>() call.
Also, if you want to specify the MariaDB version as a string, use the -mariadb suffix (e.g. 10.5.8-mariadb) otherwise this is being interpreted as Oracle's MySQL version 10.5.8.

[chris-kruining]

Thank you so much dude!

You've truly gone above and beyond to help me! so awesome!!!

it works like a charm now, initially I didn't even get to the point where the generator wasn't called because I was indeed missing the AddDbContext<>() call. I double checked and the .UseInternalServiceProvider(efCoreServiceProvider) is indeed needed.

Again, so many thanks, you made my day :D