Using a single Entity Framework Core DbContext to manage multiple database schemas with homonymous tables

Question

In a .NET Core 2.1 library I need to access to a MySQL database organized in multiple schemas with tables that can have the same name across those schemas. I can't make any changes to the DB since it comes from another company. For most of the tables I need a read-only access and I'd like to use a single EF Core DbContext.

Actually I get this error message during initialization:

InvalidOperationException: Cannot use table 'tbl_panel' for entity type 'Db2Panels' since it is being used for entity type 'Db1Panels' and there is no relationship between their primary keys.

I think that the crux of the matter mainly resides in the configuration methods, which should be called not just once but N times, one for each instance of the entity with different schema (db_machine_1.tbl_panel, db_machine_2.tbl_panel, etc.). How can I reach my goal?

This is my actual implementation.

Database schemas

// db_machine_1 schema
db_machine_1.tbl_panel
db_machine_1.tbl_basket
db_machine_1.tbl_unit

// db_machine_2 schema
db_machine_2.tbl_panel
db_machine_2.tbl_basket
db_machine_2.tbl_discard

// Other db_machine_X schemas with similar structure...

DbContext configuration

public class MyDbContext : DbContext
{
    // Schema: db_machine_1
    public DbSet<Panel> Db1Panels { get; set; }
    public DbSet<Basket> Db1Baskets { get; set; }
    public DbSet<Unit> Db1Units { get; set; }

    // Schema: db_machine_2
    public DbSet<Panel> Db2Panels { get; set; }
    public DbSet<Basket> Db2Baskets { get; set; }
    public DbSet<Discard> Db2Discards { get; set; }

    // Other schemas DbSet<X> objects...

    // Arrays to access the specific DbSet by using the schema number:
    // Panels[1] -> Db1Panels, Panels[2] -> Db2Panels, ...
    public DbSet<Panel>[] Panels { get; }
    public DbSet<Basket>[] Baskets { get; }
    // Other arrays for other DbSet<X> objects...

    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
        // Arrays initialization
        List<DbSet<Panel>> dbPanelList = new List<DbSet<Panel>>();
        dbPanelList.Add(Db1Panels);
        dbPanelList.Add(Db2Panels);
        Panels = dbPanelList.ToArray();

        List<DbSet<Basket>> dbBasketList = new List<DbSet<Basket>>();
        dbBasketList.Add(Db1Baskets);
        dbBasketList.Add(Db2Baskets);
        Baskets = dbBasketList.ToArray();

        // Initialization for other DbSet<X> objects...
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyAllConfigurations<MyDbContext>();
        modelBuilder.ApplyAllConversions();
    }
}

Objects

public class Panel
{
    public long Id { get; set; }
    public string SN { get; set; }
    // Other properties...
}

public class Basket
{
    public long Id { get; set; }
    public string Description { get; set; }
    // Other properties...
}

Configurations

public class PanelConfiguration : IEntityTypeConfiguration<Panel>
{
    public void Configure(EntityTypeBuilder<Panel> builder)
    {
        builder.ToTable("tbl_panel");

        builder.HasKey(e => e.Id);

        builder.Property(e => e.Id)
            .HasColumnName("ID_Record");

        builder.Property(e => e.SN)
            .HasColumnName("Serial")
            .HasMaxLength(20);

        // Other properties configuration...
    }
}

public class BasketConfiguration : IEntityTypeConfiguration<Basket>
{
    public void Configure(EntityTypeBuilder<Basket> builder)
    {
        builder.ToTable("tbl_basket");

        builder.HasKey(e => e.Id);

        builder.Property(e => e.Id)
            .HasColumnName("ID_Record");

        builder.Property(e => e.Description)
            .HasColumnName("Desc")
            .HasMaxLength(100);

        // Other properties configuration...
    }
}

// Other IEntityTypeConfiguration implementations for other tables...

// This extension method is used to automatically load all Configurations
// of the various entities
public static class ModelBuilderExtensions
{
    public static void ApplyAllConfigurations(this ModelBuilder modelBuilder)
    {
        var applyConfigurationMethodInfo = modelBuilder
            .GetType()
            .GetMethods(BindingFlags.Instance | BindingFlags.Public)
            .First(m => m.Name.Equals("ApplyConfiguration", StringComparison.OrdinalIgnoreCase));

        var ret = typeof(T).Assembly
            .GetTypes()
            .Select(t => (t, i: t.GetInterfaces().FirstOrDefault(i => i.Name.Equals(typeof(IEntityTypeConfiguration<>).Name, StringComparison.Ordinal))))
            .Where(it => it.i != null)
            .Select(it => (et: it.i.GetGenericArguments()[0], cfgObj: Activator.CreateInstance(it.t)))
            .Select(it => applyConfigurationMethodInfo.MakeGenericMethod(it.et).Invoke(modelBuilder, new[] { it.cfgObj }))
            .ToList();
    }
}

UPDATE about base class arrays

After creating base abstract classes and derived ones, I'd like to merge all the derived class objects into a single array to be able to access the specific DbSet by using the schema number. See also above code of DbContext constructor. I'm having problems with casting...

List<DbSet<Panel>> dbPanelList = new List<DbSet<Panel>>();
dbPanelList.Add((DbSet<Panel>)Db1Panels.Select(g => g as Panel)); // NOT WORKING! Cast Exception
dbPanelList.Add((DbSet<Panel>)Db2Panels.Cast<DbSet<Panel>>()); // NOT WORKING! Cast Exception
Panels = dbPanelList.ToArray();

Is this possible somehow?

Maybe this will help: stackoverflow.com/questions/14902245/… — Isma
– Isma, Commented Aug 28, 2018 at 9:38
@KiranJoshi Single MySQL database instance with multiple schemas. The db_machine_X prefix is the schema name. I've a single user that has access to all of them. — Cheshire Cat
– Cheshire Cat, Commented Aug 28, 2018 at 9:41
@Isma The problem is the same. He's using a single DbContext for different schemas, and up to here everything is ok for me too. But I also want to avoid entities object duplication and I like instead to re-use them multiple times in different DbSet<> objects, where each of them should refers to the same entity that should be configured to map a different schema (and so a different table). — Cheshire Cat
– Cheshire Cat, Commented Aug 28, 2018 at 9:49

ste-fu · Accepted Answer · 2018-08-28 09:38:37Z

3

I think you can't get away from having two different EF objects for the different tables, and you probably shouldn't as they may diverge at some point in the future.

At a minimum you need two classes Db1Panel and Db2Panel . I assume that actually the "Db" prefix is meant to meant a different schema, not actually a different database.

However that shouldn't be a big problem as there are other ways within C# of making them behave in similar fashions. Two options that spring to mind are having them inherit from the same base class, or have them implement an interface:

public abstract class PanelBase
{
    public long Id { get; set; }
    // other properties
}

[Table("tbl_panel", Schema = "Db1")]
public class Db1Panel : PanelBase{}

[Table("tbl_panel", Schema = "Db2")]
public class Db2Panel : PanelBase{}

If you chose to implement the interface you would need to repeat the properties in each class, but refactoring tools make this quite easy.

public interface IPanel
{
    public long Id { get; set; }
}

[Table("tbl_panel", Schema = "Db1")]
public class Db1Panel : IPanel
{
    public long Id { get; set; }
}

[Table("tbl_panel", Schema = "Db2")]
public class Db2Panel : IPanel
{
    public long Id { get; set; }
}

Or depending on the size of your application you could consider having another namespace of domain objects and just map the database objects into it:

edited Aug 28, 2018 at 9:38

answered Aug 28, 2018 at 9:00

ste-fu

7,5273 gold badges31 silver badges51 bronze badges

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

3 Comments

Ivan Stoev Over a year ago

I believe this is the only way to go, since single entity class cannot be associated with more than one DbSet (table).

Cheshire Cat Over a year ago

@ste-fu Yes, the db prefix identifies MySQL schemas. I wanted to avoid duplication of entities since my schemas are many and the similar tables inside them are many too...

Cheshire Cat Over a year ago

@ste-fu I made some experiments but as @IvanStoev says, there's no chance to associate a single entity with more than one DbSet<> property... :-( However, now that I used the base abstract class and derived classes solution, I'm still wondering if it is possible at least, to create the arrays (public DbSet<Panel>[] Panels { get; }) of base class that includes all my derived classes. Please see the question update.

Hintham · Accepted Answer · 2018-08-28 09:13:31Z

1

You should be able to use the Table attribute. There's a parameter Schema that allows you to set the schema name. See here for documentation. In your case you'd get something like

[Table("Table1", Schema="Schema1")]
public class Entity1Schema1
{
    public string Property1 {get;set;}
}

[Table("Table1", Schema="Schema2")]
public class Entity1Schema2
{
    public string Property1 {get;set;}
}

And then of course you can use interfaces or base classes to refactor your code as @ste-fu already mentioned.

answered Aug 28, 2018 at 9:13

Hintham

1,08610 silver badges31 bronze badges

Collectives™ on Stack Overflow

Using a single Entity Framework Core DbContext to manage multiple database schemas with homonymous tables

UPDATE about base class arrays

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE about base class arrays

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related