将实体框架与多个MS SQLSERVER数据库一起使用

I have searched back and forth but seemingly could not get a hold of what I need. I am sorry if this has been answered of late. A redirection to the discussion will do me good.

我来回搜索，但似乎无法掌握我需要的东西。如果最近得到回答，我很抱歉。重新定向讨论对我有好处。

This is the scenario. I have been instructed to move from Microsoft Visual Foxpro (MS is withdrawing support come 2015) to .Net C# by my boss. For the sake of good foundation and adoption of best practices, I have decided to first learn, piece pertinent information together, then start coding. This is the second year.

这就是场景。我被指示从我的老板转移到Microsoft Visual Foxpro（MS正在撤回2015年的支持）到.Net C＃。为了良好的基础和最佳实践的采用，我决定先学习，将相关信息拼凑起来，然后开始编码。这是第二年。

We are a bureau company that offer payroll processing outsource services to over 50 clients. Each client currently has their own database. The databases have tables with completely identical structures.

我们是一家上市公司，为50多个客户提供工资处理外包服务。每个客户端目前都有自己的数据库。数据库具有完全相同结构的表。

I am a newbie. Totally new to .net world.

我是新手。完全是.net世界的新东西。

I had started off with raw SQL using datatables, datareaders but in my research I got some discussions discouraging this. Many were of the view that Entity Framework should serve the purpose. But one is allowed to mix approaches especially when complex queries are involved.

我从使用数据表，数据加载器的原始SQL开始，但在我的研究中，我得到了一些讨论令人沮丧。许多人认为实体框架应该达到目的。但是，允许混合方法，特别是涉及复杂查询时。

Can someone point me to some 'good read' where I can implement Entity Framework with over 50 indentical databases. Each database is totally independent and has nothing to dowith any other. When the user logs in, they select which client they need to process payroll for, then EF points to that database.

有人能指出我的一些“好读”，我可以用50多个缩进数据库实现Entity Framework。每个数据库都是完全独立的，没有任何其他数据库。当用户登录时，他们选择处理工资单所需的客户端，然后EF指向该数据库。

3 个解决方案

#1

EF needs 2 different pieces of information to work with data from a database:

EF需要2条不同的信息来处理数据库中的数据：

1) The database schema: This is included as compiled code in your application and cannot normally be changed at runtime.

1）数据库模式：它作为编译代码包含在应用程序中，通常不能在运行时更改。

2) The connection string: This is provided at runtime, normally from a config file.

2）连接字符串：这是在运行时提供的，通常来自配置文件。

In your case, all the databases have the same schema, so you can just model one database and it will work for all the others.

在您的情况下，所有数据库都具有相同的模式，因此您可以只模拟一个数据库，它将适用于所有其他数据库。

The piece you want to change is the connection string. This tells EF how to find the database and can be provided at runtime.

您要更改的部分是连接字符串。这告诉EF如何查找数据库，并且可以在运行时提供。

There is an overload of the DbContext constructor which takes a connection string as a parameter: MSDN: DbContext Constructor (String)

DbContext构造函数有一个重载，它将连接字符串作为参数：MSDN：DbContext构造函数（String）

And there are even classes in the framework that help create connection strings for you:

框架中甚至有一些类可以帮助您创建连接字符串：

MSDN: EntityConnectionStringBuilder Class

MSDN：EntityConnectionStringBuilder类

MSDN: Connection String Builders

MSDN：连接字符串构建器

#2

It is very simple

这很简单

I had,

我有，

//WMSEntities is conection string name in web.config 
//also the name of Entitiframework
public WMSEntities() : base("name=WMSEntities") 
        {
        }

already in autogenerated Model.Context.cs of edmx folder

已经在自动生成的edmx文件夹的Model.Context.cs中

To connect to multiple database in runtime, I created another constructor that takes connection string as parameter like below in same file Model.Context.cs

为了在运行时连接到多个数据库，我创建了另一个构造函数，它在同一个文件Model.Context.cs中将连接字符串作为参数如下所示

   public WMSEntities(string connStringName)
            : base("name=" + connStringName)
        {
        }

Now, I added other connection string in Web.Config for example

现在，我在Web.Config中添加了其他连接字符串

  <add name="WMSEntities31" connectionString="data source=TESTDBSERVER_NAME;
       initial catalog=TESTDB;userid=TestUser;password=TestUserPW/>

<add name="WMSEntities" connectionString="data source=TESTDBSERVER_NAME12;
     initial catalog=TESTDB12;userid=TestUser12;password=TestUserPW12/>

Then, when connecting to database I call below method passing connetionString name as parameter

然后，当连接到数据库时，我调用下面的方法传递connetionString名称作为参数

  public static List<v_POVendor> GetPOVendorList(string connectionStringName)
  {    
      using (WMSEntities db = new WMSEntities(connectionStringName))
      {               
          vendorList = db.v_POVendor.ToList();                 

      }
  }

#3

Hrmmm I happen to really like EF Code First but I'm not certain it suits what you're doing. How often does your schema change?

Hrmmm我碰巧真的喜欢EF Code First，但我不确定它是否适合你正在做的事情。您的架构多久更改一次？

Should You Be Using EF?

Advantages of EF

If the schema changes somewhat regularly, the Migrations part of EF Code First might save you a lot of time and effort because you can often do away with SQL scripts for schema upgrades - schema changes end up in your source repository with the rest of your code instead. You'd start here:

如果架构有所变化，EF Code First的迁移部分可能会为您节省大量时间和精力，因为您可以经常使用SQL脚本进行架构升级 - 架构更改最终会在源存储库中与其余代码一起使用代替。你从这里开始：

https://*.com/a/8909092/176877

https://*.com/a/8909092/176877

I also happen to really like how easy EF is to setup, and how easy it is to write LINQ queries against it and return exactly the POCOs I built from the DB.

我也很喜欢EF设置的简单程度，以及编写LINQ查询并返回我从数据库构建的POCO是多么容易。

But EF might not be the best fit.

但EF可能不是最合适的。

Other ORMs to consider

Many other ORMs support LINQ and POCOs with better support for existing databases (there are things that can be pretty difficult to map in EF Code First), --and existing support for asynchronous operation (EF is on 5.0 right now; 6.0 has async)-- (update: EF6 is the latest and its async support is great. Its bulk delete is terrible though and should be avoided like plague, drop to plain SQL for that).

许多其他ORM支持LINQ和POCO，它们对现有数据库有更好的支持（有些东西在EF Code First中很难映射），并且现有支持异步操作（EF现在5.0; 6.0有异步） - （更新：EF6是最新的，它的异步支持很棒。虽然它的批量删除很糟糕，但应该像瘟疫一样避免，为此删除普通的SQL）。

In particular NHibernate is the beast on the scene for existing db support, but it's a bit of a configuration chore and what appears to be political infighting has caused the documentation to be conflicting for different versions and forks of it.

特别是NHibernate是现有数据库支持的现场野兽，但它有点像配置工作，看似政治内斗导致文档对于不同版本和分叉的冲突。

Much simpler are many "Micro ORMs" - that link is to a short list from 2011 but if you poke around you'll find 30 or so in .Net. Some generate better or less optimal queries, some none at all, some make you write SQL (don't use those) - you'll have to poke around to decide which is for you. This can be a bigger research task but I suspect the easy configuration and small learning curve for one of these best suits what you're trying to do.

更简单的是许多“微型ORM” - 这个链接是2011年的一个简短列表，但是如果你四处寻找，你会在.Net中找到30个左右。有些会生成更好或更不理想的查询，有些根本没有，有些会让你编写SQL（不要使用那些） - 你必须四处寻找决定哪一个适合你。这可能是一项更大的研究任务，但我怀疑这些最适合您的尝试之一的简单配置和小学习曲线。

Answer to your specific question

Talk to All client Dbs at once

If you're connecting to all 50 databases from one app at the same time you'll need to instantiate 50 DbContexts like:

如果您同时从一个应用程序连接到所有50个数据库，则需要实例化50个DbContexts，如：

var dbClient1 = new DbClient1();
var dbClient2 = new DbClient2();

Assuming you went around making little wrapper classes like:

假设你去做了一些小包装类，比如：

public class DbClient1 : CoreDbContext
{
    public DbClient1()
        : base("DbClient1") // Means use the connection string named "DbClient1" in Web.Config

Where CoreDbContext is the main EF class in your Project that extends DbContext (standard part of any EF project).

其中CoreDbContext是Project中扩展DbContext（任何EF项目的标准部分）的主要EF类。

Talk to just one at a time

If you're using just the one per app then any EF tutorial will do.

如果你只使用每个应用程序一个，那么任何EF教程都可以。

The only major trick will be migrating those Dbs when schema changes occur. Two basic approaches there. Either way you grab a backup and restore a copy of them locally so you can test your migrations against them (update-database -f -verbose). If you don't you risk data errors like changing a column to NOT NULL and finding your local test instance had no nulls, one client's did, kaboom. Once you get them working, you're onto deciding how you want to update Production. There are a lot of ways you might do this ranging from writing a custom roll-forward/back tool (or finding one) with SQL scripts checked into git, hiring a DBA, or much simpler:

唯一的主要技巧是在发生架构更改时迁移这些Dbs。有两种基本方法。无论哪种方式，您都可以获取备份并在本地恢复它们的副本，以便您可以针对它们测试迁移（update-database -f -verbose）。如果你不这样做，你冒着数据错误的风险，例如将列更改为NOT NULL，并且发现本地测试实例没有空值，一个客户端就这样做了，kaboom。一旦你让他们工作，你就决定如何更新生产。您可以通过多种方式执行此操作，包括编写自定义前滚/后退工具（或查找一个），将SQL脚本检入git，雇用DBA或更简单：

The Obvious - SQL Script

Dump the migration to SQL (update-database -script) and run it against the actual production database.

将迁移转储到SQL（update-database -script）并针对实际的生产数据库运行它。

My Crazy Way for Small Numbers of Dbs

Add entries for each db to Web.Config, and create a Project Configuration for each of them like "DbDeployClient1," "DbDeployClient2," etc. In each of those make a build define like DbDeployClient1, and then add this to your DbContext class:

将每个数据库的条目添加到Web.Config，并为每个数据库创建一个项目配置，如“DbDeployClient1”，“DbDeployClient2”等。在每个数据库中创建一个类似于DbDeployClient1的构建定义，然后将其添加到您的DbContext类中：

public CoreDbContext()
#if DbDeployClient1
    : base("DbDeployClient1")
#elseif DbDeployClient2
    : base("DbDeployClient2")
    // etc
#endif
{

That allows you to quickly switch to your DbDeploy config and run the migration directly from Visual Studio against the target database. Obviously if you're doing this you'll need to temporarily open a port, preferably only allowing in your IP, on the actual SQL Server instance you're migrating. One nicety is you get clear errors from your migration right there, and full rollback capability, without any real work - all that rollback support you're leveraging is just part of EF. And one dev can do it without a bunch of other bottlenecks. But it has a lot of opportunities to reduce risk and improve automation.

这使您可以快速切换到DbDeploy配置，并直接从Visual Studio对目标数据库运行迁移。显然，如果你这样做，你需要暂时打开一个端口，最好只允许你的IP，在你正在迁移的实际SQL Server实例上。一个很好的例子就是你可以从你的迁移中获得明确的错误，以及完全的回滚功能，而不需要任何实际工作 - 所有你正在利用的回滚支持只是EF的一部分。一个开发人员可以做到这一点，没有一堆其他瓶颈。但它有很多机会降低风险并提高自动化程度。