Entity Framework and multiple schemas

c#, ef-code-first, entity-framework, oracle

Solution

While doing some research about Entity Framework I came across the following post:

http://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-code-first/

It doesn't quite give me a single dbContext to work with but it does only use a single connection (which was my reasoning behind not wanting to use multiple dbContexts). After setting up the following code:

public class oraDbContext : DbContext
{
    static oraDbContext() {
        Database.SetInitializer<oraDbContext>(null);
    }

    private oraDbContext(DbConnection connection, DbCompiledModel model)
        : base(connection, model, contextOwnsConnection: false) { }

    public DbSet<SomeTable1> SomeTable1 { get; set; }
    public DbSet<SomeTable2> SomeTable2 { get; set; }

    private static ConcurrentDictionary<Tuple<string, string>, DbCompiledModel> modelCache = new ConcurrentDictionary<Tuple<string, string>, DbCompiledModel>();

    public static oraDbContext Create(string schemaName, DbConnection connection) {
        var compiledModel = modelCache.GetOrAdd(
            Tuple.Create(connection.ConnectionString, schemaName),
            t =>
            {
                var builder = new DbModelBuilder();
                builder.Configurations.Add<SomeTable1>(new SomeTable1Map(schemaName));
                builder.Configurations.Add<SomeTable2>(new SomeTable2Map(schemaName));

                var model = builder.Build(connection);
                return model.Compile();
            });

        return new oraDbContext(connection, compiledModel);
    }
}

This of course requires that my mapping files be set up like so:

public class DailyDependencyTableMap : EntityTypeConfiguration<DailyDependencyTable>
{
    public SomeTableMap(string schemaName) {
        this.ToTable("SOME_TABLE_1", schemaName.ToUpper());

        //Map other properties and stuff
    }
}

Writing queries that use multiple schemas is somewhat annoying but, for the moment, it does what I need it to do:

using (var connection = new OracleConnection("a connection string")) {
    using (var schema1 = oraDbContext.Create("SCHEMA1", connection))
    using (var schema2 = oraDbContext.Create("SCHEMA2", connection)) {

        var query = ((from a in schema1.SomeTable1 select new { a.Field1 }).ToList())
             .Concat((from b in schema2.SomeTable1 select new { b.Field1 }).ToList())
    }
}

Problem

I'm trying to set up my dbContext so that it can handle multiple schemas in a single Oracle database. I didn't want one monolithic dbContext file so I've come up with the following: ``` public class oraDbContext : DbContext { static oraDbContext() { Database.SetInitializer<oraDbContext>(null); } public oraDbContext(string connName) : base("Name=" + connName) { } public _schema1 schema1 = _schema1.Instance; public _schema2 schema2 = _schema2.Instance; protected override void OnModelCreating(DbModelBuilder modelBuilder) { schema1.OnModelCreating(modelBuilder); schema2.OnModelCreating(modelBuilder); } } ``` The schema file looks like this: ``` public sealed class _schema1 { private static readonly _schema1 instance = new _schema1(); static _schema1() { } private _schema1() { } public static _schema1 Instance { get { return instance; } } public DbSet<someTable> someTable { get; set; } internal void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new someTableMap()); } } ``` However, when I try to perform a query I get the error: `Value cannot be null`. The value it's referring to is the `someTable` property in _schema1. A. How can I fix this? B. Is there a better solution? Edit: What I want here is the ability to write code such as the following - ``` var query1 = from p in db.schema1.someTable select p; var query2 = from p in db.schema2.someTable select p; ``` Where someTable is the same in both schemas. In our database we have several schemas with the exact same tables that have identical or nearly identical columns. I don't want to create a seperate dbContext for each schema because that could potentially mean 5 different connections if I'm creating a query that pulls from 5 schemas. If I was writing this same query in straight SQL I could pull the data from 5 different schemas with a single connection and that's what I would like to accomplish here.

Original source

Related problems