We have an application that uses multiple databases sharing the same schema.

Until now, it was possible to specify the name of the connectionString to use. But since the version 6 of Entity Framework, there is only one default constructor to instantiate our “entities” object.

Goals

  • Create an Entity Data Model which must be used by many databases.
  • Use dynamic connection strings

In order to illustrate we will create a simplify example.

Databases

We have 2 databases named “SampleDB_1” and “SampleDB_2” with a single table “People”.

databases

“Entity Data Model” creation.

In Visual Studio, we create a new project named “EntityFrameworkTests”, then we add a new item of type “ADO.NET Entity Data Model”.

creation_1

Our entity data model will be based on the exiting database “SampleDB_1” then we will modify the code to allow many databases. I select a model using the EF Designer.

creation_2

We have to select a connection string of the database. I use my local sqlexpress db instance then select the database “SampleDB_1” as database template.


 creation_3

Select “Entity Framework 6.x” :

creation_4

Select required database objects you want to manage (in this example there is only a table named “People”)

creation_5

Rename the model namespace “SampleDB1_Model” to “SampleDB_Model” then click on Finish button.

Our model is created, a class named “SampleDB_Entities” inherits of DbContext.

creation_6

The generated DbContext doesn’t allow to specify the connection string to use.

Allowing to specify database name to use

The class SampleDB_Entities was auto generated and all manual changes on this file will be overwritten if the code is regenerated. C# allow us to define partial class to split a class code into multiples physical files.

Let’s create a new file for the partial class “SampleDB_Entities” ! We add a constructor which taking a DbConnection in parameter.

1
2
3
4
5
6
7
8
9
10

/// <summary>
/// Allow constructor with DbConnection in parameter
/// </summary>
/// <param name="connectionString"></param>
/// <param name="contextOwnsConnection"></param>
private SampleDB_Entities(DbConnection connectionString, bool contextOwnsConnection = true)
: base(connectionString, contextOwnsConnection)
{
 
}

To help creation of a context for a specific database name we have a static method “CreateEntitiesForSpecificDatabaseName” that will create entity connection string using the db name.

I create an EntityConnection programmatically by taking connection information generated in the app.config.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

/// <summary>
/// Instanciates a new DbContext for the specicied database
/// </summary>
/// <param name="databaseName"></param>
/// <param name="contextOwnsConnection"></param>
/// <returns>DbContext instance</returns>
public static SampleDB_Entities CreateEntitiesForSpecificDatabaseName(string databaseName, bool contextOwnsConnection = true)
{
    //Initialize the SqlConnectionStringBuilder
    SqlConnectionStringBuilder sqlConnectionBuilder = new SqlConnectionStringBuilder();
    sqlConnectionBuilder.DataSource = @"localhost\sqlexpress";
    sqlConnectionBuilder.InitialCatalog = databaseName;
    sqlConnectionBuilder.IntegratedSecurity = true;
    sqlConnectionBuilder.MultipleActiveResultSets = true;
    string sqlConnectionString = sqlConnectionBuilder.ConnectionString;
     
    //Initialize the EntityConnectionStringBuilder
    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
    entityBuilder.Provider = "System.Data.SqlClient";
    entityBuilder.ProviderConnectionString = sqlConnectionString;
     
    //Set the Metadata location.
    entityBuilder.Metadata = @"res://*/DataAccess.EncounterModel.EncounterModel.csdl|res://*/DataAccess.EncounterModel.EncounterModel.ssdl|res://*/DataAccess.EncounterModel.EncounterModel.msl";
     
    //Create entity connection
    EntityConnection connection = new EntityConnection(entityBuilder.ConnectionString);
     
    return new SampleDB_Entities(connection);
}
 

We use this method in our Program.cs main method:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

static void Main(string[] args)
{
    // Display "people" content for db "SampleDB_1"
    using (SampleDB_Entities entities = SampleDB_Entities.CreateEntitiesForSpecificDatabaseName("SampleDB_1"))
    {
        Console.WriteLine("======= SampleDB_1 =======");
        var people = entities.People.ToList();
        people.ForEach(p => Console.WriteLine(p.Name));
    }
 
 
    // Display "people" content for db "SampleDB_"
    using (SampleDB_Entities entities = SampleDB_Entities.CreateEntitiesForSpecificDatabaseName("SampleDB_2"))
    {
        Console.WriteLine("======= SampleDB_2 =======");
        var people = entities.People.ToList();
        people.ForEach(p => Console.WriteLine(p.Name));
    }
 
    Console.ReadLine();
}

Result:

result

Go further by listing available databases on the server

The dynamic connection string build allow us to get all databases by naming convention. In our example we consider that all the databases whose names begin with “SampleDB_” match our database model.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

using (var con = new SqlConnection(@"Data Source=localhost\sqlexpress; Integrated Security=True;"))
{
    con.Open();
    DataTable databases = con.GetSchema("Databases");
    foreach (DataRow database in databases.Rows)
    {
        string databaseName = (database.ItemArray.FirstOrDefault() == null) ? string.Empty : database.ItemArray.First().ToString();
        if (databaseName.StartsWith("SampleDB_"))
        {
            using (SampleDB_Entities entities = SampleDB_Entities.CreateEntitiesForSpecificDatabaseName(databaseName))
            {
                Console.WriteLine("======= " + databaseName + " =======");
                var people = entities.People.ToList();
                people.ForEach(p => Console.WriteLine(p.Name));
            }
        }
    }
}

In the real world, it is recommended to move general db connection information to config file and move static method CreateEntitiesForSpecificDatabaseName in a SampleDb_EntityFactory if needed.

Last modified: 14 May 2019

Author

Comments

Rocky 

How would this approach work if using code-first (reverse engineering from existing database by selecting “Code First from database” instead of “EF Designer from database”) ?

Ralph Florent 

Hello,

I have to admit you’ve done a very great work with this approach. I wonder how it’d work under the following condition:
Imagine that I’d want to use one EDM for multiple providers (Microsoft, Oracle for example) by focusing on EF Database First.

I’ve been looking for examples like this for a while, and apparently nobody’s tried it before. I hope you could help.

Thank you!

Archana 

Hello,

I tried to implement the above approach. I have a table called Users in 2 databases with same schema.
Fetching the data from 1st database works fine, but while I try to fetch the content of table Users from 2nd database, I’m getting the below error:
“An exception of type ‘System.Data.Entity.Core.MetadataException’ occurred in EntityFramework.dll but was not handled in user code

Additional information: Unable to load the specified metadata resource.”

Can you please suggest what is going wrong here?

Best Regards,
Archana

bella 

Hi,
Your tuto is very nice.

Have you an example with code first please ?

Thanks.

Mason Zhou 

Hey Olivier, just wanna thank you for this detailed blog. It really helped me a lot!

zarco 

Excelente tutorial, muchas gracias.

Dipak Kumar 

Hi,
Thanks for given this solution.
In my case I have 2 databases. Tables are linked by id of different databases. In sql query I can simply join these tables. But here how can I join 2 tables of different databases. As we are not adding tables of different databases in the model, is it possible to join among them?
…
Dipak

sgeorge 

I can see you generated one EDMX file for sample1 database.
I was not able to get how you add sample2 database in EDMX file Or
do you create a seperate EDMX file for sample2 database.

Ravikumar Gunge 

It helped me a lot, with little changes.
Thanks a lot.

Zafor 

Nice article. Thank you for sharing.

Write a Reply or Comment

Your email address will not be published.