在多个数据库的DataContext [英] DataContext across multiple databases

问题描述

我有一个需要连接来自多个数据库中的表成一个单一的LINQ到SQL查询的应用程序。不幸的是，我有一个单独的DataContext类设置为每一个数据库，因此该查询将无法工作。我得到这样一个错误：查询包含对不同数据上下文定义的项目引用的

理想的解决方案似乎是创建一个单一的DataContext所有三个数据库。它们都存在于同一台服务器上，这样他们就可以使用相同的连接字符串。我目前使用运行sqlmetal.exe生成DBML我和CS文件，这意味着我不需要当我改变了数据模型，以手工编辑任何文件的脚本。我想保持自动化的水平，但sqlmetal.exe似乎只支持每个DBML文件中的单个数据库。

这是可能与sqlmetal或其他工具？ ？或者，我需要考虑的另一个解决方案，就像使用一个单一的数据库，为整个应用程序

这里是我使用批处理文件脚本：

 C：\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal .EXE/服务器：MYSERVER /数据库：DatabaseOne /视图/函数/存储过程/dbml:DatabaseOne.dbml /namespace:Model.Domain.DatabaseOne /背景：DatabaseOneDataContext /复数
C：\Program Files\微软SDKs\Windows\v6.0A\bin\SqlMetal.exe/服务器：MYSERVER /数据库：DatabaseOne /视图/函数/存储过程/code:DatabaseOne.designer.cs /语言：C＃/命名空间：模型。 Domain.DatabaseOne /背景：DatabaseOneDataContext /复数
 
C：\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe/服务器：MYSERVER /数据库：DatabaseTwo /视图/函数/存储过程/dbml:DatabaseTwo.dbml /namespace:Model.Domain.DatabaseTwo /背景：DatabaseTwoDataContext /复数
C：\Program Files\Microsoft SDKs\Windows\v6 .0A\bin\SqlMetal.exe/服务器：MYSERVER /数据库：DatabaseTwo /视图/函数/存储过程/code:DatabaseTwo.designer.cs /语言：C＃/namespace:Model.Domain.DatabaseTwo /背景：DatabaseTwoDataContext /复数
 
C：\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe/服务器：MYSERVER /数据库：DatabaseThree /视图/函数/存储过程/dbml:DatabaseThree.dbml /namespace:Model.Domain.DatabaseThree /背景：DatabaseThreeDataContext /复数
C：\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal .EXE/服务器：MYSERVER /数据库：DatabaseThree /视图/函数/存储过程/code:DatabaseThree.designer.cs /语言：C＃/namespace:Model.Domain.DatabaseThree /背景：DatabaseThreeDataContext /复数
  

解决方案

一个同事发现的其他网站上的线程的[social.msdn.microsoft.com]认为讨论了这个相同的问题。其中讨论的解决方案是执行意见主数据库中的所有连接，并揭露那些意见，在应用程序中的对象。这可能会在我的情况下工作，因为大多数我的数据是在一个数据库中，而在其他数据库的小数量的表是只读

I have an application that needs to join tables from multiple databases into a single LINQ-to-SQL query. Unfortunately, I have a separate DataContext class setup for each database, so this query won't work. I get an error like this: "The query contains references to items defined on a different data context"

The ideal solution seems to be to create a single DataContext for all three databases. They all exist on the same server, so they can use the same connection string. I currently use a script that runs sqlmetal.exe to generate my DBML and CS files, meaning that I don't need to manually edit any files when I change the data model. I want to maintain that level of automation, but sqlmetal.exe only seems to support a single database per DBML file.

Is that possible with sqlmetal or another tool? Or, do I need to consider another solution like using a single database for the entire application?

Here's the batch file script I'm using:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseOne /views /functions /sprocs /dbml:DatabaseOne.dbml /namespace:Model.Domain.DatabaseOne /context:DatabaseOneDataContext /pluralize
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseOne /views /functions /sprocs /code:DatabaseOne.designer.cs /language:C# /namespace:Model.Domain.DatabaseOne /context:DatabaseOneDataContext /pluralize

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseTwo /views /functions /sprocs /dbml:DatabaseTwo.dbml /namespace:Model.Domain.DatabaseTwo /context:DatabaseTwoDataContext /pluralize
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseTwo /views /functions /sprocs /code:DatabaseTwo.designer.cs /language:C# /namespace:Model.Domain.DatabaseTwo /context:DatabaseTwoDataContext /pluralize

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseThree /views /functions /sprocs /dbml:DatabaseThree.dbml /namespace:Model.Domain.DatabaseThree /context:DatabaseThreeDataContext /pluralize
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseThree /views /functions /sprocs /code:DatabaseThree.designer.cs /language:C# /namespace:Model.Domain.DatabaseThree /context:DatabaseThreeDataContext /pluralize

解决方案

A co-worker found a thread on another site [social.msdn.microsoft.com] that discusses this same issue. One discussed solution was to perform all joins in views in the "primary" database, and expose those views as objects in the application. That will probably work in my situation, since the majority of my data is in one database, and the small number of tables in the other databases are read-only.

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