实体框架代码首先与SQL Server同义词 [英] Entity Framework Code First with SQL Server Synonyms

问题描述

我有这样的情况，我会有多个公司访问我的应用程序的一个实例，但是我需要隔离他们的数据，以避免意外加载另一个公司的数据，并缓解每个公司的数据库备份。

理想情况下，我想将数据分成不同的数据库，如下所示：

• 一个SQL Server数据库，用于列出所有用户，以及
  所有用户/公司之间共同的任何表




• N个公司特定数据库，每个公司具有相同的模式，但每个公司的数据不同。登录时，我会动态选择特定用户公司的数据库

我希望能够查询公司的具体数据表，但是在共享数据库上执行连接（例如，所以我可以在公司数据库表中存储一个外键，它连接到另一个数据库中的共享表的主键上）。

我发现SQL Server同义词看起来像他们可以做这个工作，似乎我可以通过在SQL Server Management Studio中使用查询来成功地加入到两个数据库之间。 / p>

是否可以在Entity Framework Code First中使用同义词表，如果是，我的POCO类/ DbContext需要什么样的？

感谢：）

解决方案

EXCUSE我关于我的可怕的英语！

我有同样的问题，并解决了这个问题。这是非常困难的，直到找到解决方案，但你会笑，如果我写的解决方案！因为这太简单了。

如果我理解正确，您有一个 SharedServer 和一些 LocalServers 公司具体），希望在一个单一的上下文中拥有所有的对象（一个共享的，一个公司的具体）。

我会给你两种情况：

1. 多对多：在这种情况下，表有关系在 sharedDB ，但加入他们的第三个表格是公司特定的DB 。


2. 单对多 ：表格中的哪一个位于 SharedDB ，另一个在公司特定的DB 中。

多对多

1。在SQL中创建您的同义词

首先您必须在本地（或公司特定）DB中创建同义词：

  CREATE SYNONYM [dbo]。[StudentCources] FOR [SharedServer]。[SharedDB]。[dbo]。[StudentCources] 
  pre> 
 
 
我们假设您的共享表有两列（不在乎）命名为 studentID 和 courseID 。
 
 
 
 2。创建POCO 
 
 
 
假设我们在本地DB上有两个表，它们之间具有多对多关系。让我们假设第三个joiner表（其中包含的键）位于共享DB中！ （我认为这是最糟糕的方式）。所以您的POCO将如下所示：
 公共课学生
公共学生studentID为整数
公共属性姓名为字符串
公共财产课程作为ICollection（当然）
结束类
  
和
 公共课程
公共属性courseID为整数
公共属性名称为String 
公共财产学生作为ICollection（学生）
结束类
  
和共享一个：
 公共类StudentCources 
公共属性courseID为整数
公共属性studentID as Integer 
结束类
  
，上下文如下所示：
 部分公共类LocalContext 
继承DbContext 
 
 Public Sub New（）
 MyBase.New（name = LocalContext）
 End Sub 
 
公开覆盖空闲的学生作为DbSet（学生）
公共可覆盖的属性课程作为DbSet（当然）
 
受保护的覆盖Sub OnModelCreating（ByVal modelBuilder As DbModelBuilder）
 modelBuilder.Entity（Of学生）.HasMany（函数（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 
结束类
  
  OnModelCreating 中的代码告诉模型构建器关系表是同义词（不直接）。我们知道同义词在 SharedDB 中。
 
 
 
一对五
 
 
 
没有步骤！只需将 OnModelCreating 修改为：
  modelBuilder.Entity（Of Student） .ToTable（学生，dbo）
  
并注意，在这种情况下学生是同义词。然后创建关系：）
 
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 :)
解决方案
EXCUSE ME ABOUT MY TERRIBLE ENGLISH!

I had the same issue and solved it. it was so hard until finding the solution, but you will laugh if I write the solution! because it's too simple.

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:

Many-to-Many: in this case, table to have relation are in sharedDB, but the third table joining them, is in company specific DB.
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-May

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 :)

                        
这篇关于实体框架代码首先与SQL Server同义词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！