跨多个数据库的 LINQ [英] LINQ across multiple databases

问题描述

我有两个需要通过 LINQ 连接的表，但它们位于不同的数据库中.现在我正在返回一个表的结果，然后循环并检索另一个表的结果，你可以猜到这不是非常有效.有没有办法让它们变成一个单一的 LINQ 语句?有没有其他方法可以构造它来避免循环?我只是在寻找想法，以防我忽略某些东西.

I've got two tables that need to be joined via LINQ, but they live in different databases. Right now I'm returning the results of one table, then looping through and retrieving the results of the other, which as you can guess isn't terribly efficient. Is there any way to get them into a single LINQ statement? Is there any other way to construct this to avoid the looping? I'm just looking for ideas, in case I'm overlooking something.

请注意，我无法更改数据库，即我无法在一个视图中创建一个引用另一个的视图.我还没有尝试过在引用两个表的第三个数据库中创建视图.欢迎提出任何想法.

Note that I can't alter the databases, i.e. I can't create a view in one that references the other. Something I haven't tried yet is creating views in a third database that references both tables. Any ideas welcome.

推荐答案

您可以做到这一点，即使是跨服务器，只要您可以从另一个数据库访问另一个数据库.也就是说，如果可以针对访问 ServerB.DatabaseB.ServerA.DatabaseA 的 SQL 语句编写 SQL 语句em>schema.TableWhatever，那么你可以在 LINQ 中做同样的事情.

You can do this, even across servers, as long as you can access one database from the other. That is, if it's possible to write a SQL statement against ServerA.DatabaseA that accesses ServerB.DatabaseB.schema.TableWhatever, then you can do the same thing in LINQ.

为此，您需要手动编辑 .dbml 文件.您可以像这样在 VS 2008 中轻松完成此操作:右键单击，选择打开方式...，然后选择 XML 编辑器.

To do it, you'll need to edit the .dbml file by hand. You can do this in VS 2008 easily like this: Right-click, choose Open With..., and select XML Editor.

查看 Connection 元素，它应该位于文件的顶部.您需要做的是为不在该连接字符串指向的数据库中的表提供一个明确的数据库名称(和服务器名称，如果不同).

Look at the Connection element, which should be at the top of the file. What you need to do is provide an explicit database name (and server name, if different) for tables not in the database pointed to by that connection string.

.dbml 中 Table 元素的开始标记如下所示:

The opening tag for a Table element in your .dbml looks like this:

<Table Name="dbo.Customers" Member="Customers">

您需要做的是，对于不在连接字符串数据库中的任何表，将名称属性更改为以下内容之一:

What you need to do is, for any table not in the connection string's database, change that Name attribute to something like one of these:

<Table Name="SomeOtherDatabase.dbo.Customers" Member="Customers">
<Table Name="SomeOtherServer.SomeOtherDatabase.dbo.Customers" Member="Customers">

如果遇到问题，请确保其他数据库(或服务器)确实可以从您的原始数据库(或服务器)访问.在 SQL Server Management Studio 中，尝试编写一个针对原始数据库运行的小型 SQL 语句，执行如下操作:

If you run into problems, make sure the other database (or server) is really accessible from your original database (or server). In SQL Server Management Studio, try writing a small SQL statement running against your original database that does something like this:

SELECT SomeColumn
FROM OtherServer.OtherDatabase.dbo.SomeTable

如果那个不起作用，请确保您的用户或登录名可以使用相同的密码访问两个数据库.当然，它应该与 .dbml 连接字符串中使用的相同.

If that doesn't work, make sure you have a user or login with access to both databases with the same password. It should, of course, be the same as the one used in your .dbml's connection string.

这篇关于跨多个数据库的 LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！