Entity Framework Code First with SQL Server Synonyms

Question

I have the situation where I will have multiple companies accessing a single instance of my application, but I need to segregate their data to avoid accidentally loading data for another company, and to ease per-company database backups.

Ideally, I'd like to split the data up into different databases as follows:

• One SQL Server database for a list of all the users, together with any tables that are common across all users / companies

• N number of company specific databases, each with the same schema but different data for each company. On logging in, I would dynamically select the database for the specific user's company

I'd like to be able to query the company specific data tables, but perform joins onto the shared database (for example, so I could store a foreign key in the company database table, which joins onto the primary key of a shared table in the other database).

I've discovered SQL Server Synonyms which look like they could do the job, and it seems I can successfully join between the two databases by using a query in SQL Server Management Studio.

Is it possible to use a Synonym table in Entity Framework Code First, and if so what would my POCO classes / DbContext need to look like?

Thanks :)

Answer 1

If I understood properly, you have a SharedServer and some LocalServers (company specific) which want to have all the objects of both (one shared, one the company specific) in a single context.

I'll give you two scenarios:

1. Many-to-Many: in this case, table to have relation are in sharedDB, but the third table joining them, is in company specific DB.
2. Single-to-Many: which one of tables are in SharedDB and the other one in company specific DB.

Many-to-Many

1. Create Your Synonym in SQL side

First you have to create the synonym in your local (or company specific) DB:

CREATE SYNONYM [dbo].[StudentCources] FOR [SharedServer].[SharedDB].[dbo].[StudentCources]

let's suppose that your shared table has two columns (doesn't care) named studentID and courseID.

2. Create the POCOs

Let's suppose we have two tables on local DB which have Many-to-Many relationship between each other. and let's suppose the third joiner table (which contains the keys) is located in shared DB!! (I think it's the worst way). so your POCOs will look like this:

Public Class Student
    Public Property studentID as Integer
    Public Property Name as String
    Public Property Courses as ICollection(Of Course)
End Class

and

Public Class Course
    Public Property courseID as Integer
    Public Property Name as String
    Public Property Students as ICollection(Of Student)
End Class

and the Shared one:

Public Class StudentCources
    Public Property courseID as Integer
    Public Property studentID as Integer
End Class

and the context look like:

Partial Public Class LocalContext
    Inherits DbContext

    Public Sub New()
        MyBase.New("name=LocalContext")        
    End Sub

    Public Overridable Property Students As DbSet(Of Student)
    Public Overridable Property Courses As DbSet(Of Course)

    Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)
        modelBuilder.Entity(Of Student).HasMany(Function(e) e.Courses).WithMany(Function(e) e.Students).Map(Sub(e)
            e.MapLeftKey("studentID")
            e.MapRightKey("courseID")
            e.ToTable("StudentCources", "dbo")
    End Sub)

    End Sub
End Class

the code in the OnModelCreating tells the model builder that the relation table is a synonym (not directly). and we know that the synonym is in SharedDB.

One-to-Many

No steps! Just modify the OnModelCreating to:

modelBuilder.Entity(Of Student).ToTable("Students", "dbo")

and note that in this case Students is a Synonym. then create the relationship :)