跨多个数据库的LINQ [英] LINQ across multiple databases
问题描述
我有两个需要通过LINQ连接的表，但是它们存在于不同的数据库中.现在，我要返回一个表的结果，然后遍历并检索另一个表的结果，正如您所猜测的那样，这并不是非常有效.有什么方法可以将它们合并为一个LINQ语句吗?还有其他方法可以构造此结构以避免循环吗?我只是在寻找想法，以防万一.
请注意，我无法更改数据库，即无法在一个引用另一个的视图中创建视图.我还没有尝试过的是在引用两个表的第三个数据库中创建视图.任何想法都欢迎.
即使您跨服务器访问，只要您可以从另一个数据库访问一个数据库，您也可以执行此操作.也就是说，是否有可能针对访问 ServerB . DatabaseB 的 ServerA . DatabaseA 编写SQL语句. em> schema . TableWhat ，那么您可以在LINQ中做同样的事情.
要执行此操作，您需要手工编辑.dbml文件.您可以像这样在VS 2008中轻松地做到这一点:右键单击，选择打开方式... ，然后选择 XML编辑器.
查看 Connection 元素，该元素应位于文件顶部.您需要为不在该连接字符串所指向的数据库中的表提供一个显式的数据库名称(和服务器名称，如果不同).
.dbml中 Table 元素的开始标记如下:
<Table Name="dbo.Customers" Member="Customers">
您需要做的是，对于连接字符串数据库中的任何 not 表，将 Name 属性更改为以下其中一项:
<Table Name="SomeOtherDatabase.dbo.Customers" Member="Customers">
<Table Name="SomeOtherServer.SomeOtherDatabase.dbo.Customers" Member="Customers">
如果遇到问题，请确保可以从原始数据库(或服务器)真正访问另一个数据库(或服务器).在SQL Server Management Studio中，尝试编写一个针对原始数据库运行的小型SQL语句，该语句执行以下操作:
SELECT SomeColumn
FROM OtherServer.OtherDatabase.dbo.SomeTable
如果那个不起作用，请确保您具有一个用户或登录名，并且可以使用相同的密码访问这两个数据库.当然，它应该与.dbml的连接字符串中使用的相同.
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.
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.
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.
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.
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">
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
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屋！
