模拟跨上下文连接--LINQ/C#
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/898363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simulating Cross Context Joins--LINQ/C#
提问by David Yancey
Here's the issue:
这是问题：
I have 2 data contexts that I would like to do a join on. Now I know that LINQ doesn't allow joins from one context to another, and I know that 2 possible solutions would be to either create a single datacontext or to have 2 seperate queries (which is what I'm doing for now). However what I would like to do is to "simulate" a join.
我有 2 个数据上下文，我想对其进行连接。现在我知道 LINQ 不允许从一个上下文连接到另一个上下文，并且我知道 2 种可能的解决方案是创建单个数据上下文或有 2 个单独的查询（这就是我现在正在做的）。但是我想做的是“模拟”一个连接。
Here's what I've tried.
这是我尝试过的。
using (var _baseDataContext = Instance)
{
var query = from a in _baseDataContext.Account.ACCOUNTs
where a.STR_ACCOUNT_NUMBER.ToString() == accountID
join app in _baseDataContext.Account.APPLICATIONs on a.GUID_ACCOUNT_ID equals
app.GUID_ACCOUNT
join l in GetLoans() on app.GUID_APPLICATION equals l.GUID_APPLICATION
select l.GUID_LOAN;
return query.Count() > 0 ? query.First() : Guid.Empty;
}
private static IQueryable<LOAN> GetLoans()
{
using (var _baseDataContext = Instance)
{
return (from l in _baseDataContext.Loan.LOANs
select l).AsQueryable();
}
}
In run time I get is
在运行时我得到的是
System.InvalidOperationException: The query contains references to items defined on a different data context
System.InvalidOperationException：查询包含对不同数据上下文中定义的项目的引用
EDIT:
编辑：
Working Solution:
工作解决方案：
using (var _baseDataContext = Instance)
{
var query = from a in _baseDataContext.Account.ACCOUNTs
where a.STR_ACCOUNT_NUMBER.ToString() == accountID
join app in _baseDataContext.Account.APPLICATIONs on a.GUID_ACCOUNT_ID equals
app.GUID_ACCOUNT
join l in GetLoans() on app.GUID_APPLICATION equals l.GUID_APPLICATION
select l.GUID_LOAN;
return (query.Count() > 0) ? query.First() : Guid.Empty;
}
private static IEnumerable<LOAN> GetLoans()
{
using (var _baseDataContext = Instance)
{
return (from l in _baseDataContext.Loan.LOANs
select l).AsQueryable();
}
}
采纳答案by CodeLikeBeaker
Maybe something like this can get you started in the right direction. I made a mock database with similar columns based on your column names and got some results.
也许这样的事情可以让你朝着正确的方向开始。我根据您的列名制作了一个具有类似列的模拟数据库，并得到了一些结果。
class Program
{
static AccountContextDataContext aContext = new AccountContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True");
static LoanContextDataContext lContext = new LoanContextDataContext(@"Data Source=;Initial Catalog=;Integrated Security=True");
static void Main()
{
var query = from a in aContext.ACCOUNTs
join app in aContext.APPLICATIONs on a.GUID_ACCOUNT_ID equals app.GUID_ACCOUNT
where app.GUID_APPLICATION.ToString() == "24551D72-D4C2-428B-84BA-5837A25D8CF6"
select GetLoans(app.GUID_APPLICATION);
IEnumerable<LOAN> loan = query.First();
foreach (LOAN enumerable in loan)
{
Console.WriteLine(enumerable.GUID_LOAN);
}
Console.ReadLine();
}
private static IEnumerable<LOAN> GetLoans(Guid applicationGuid)
{
return (from l in lContext.LOANs where l.GUID_APPLICATION == applicationGuid select l).AsQueryable();
}
}
Hope this helps!
希望这可以帮助！
回答by Robert Harvey
I favor creating a separate data context that contains just the two tables you want to join. But I suppose you could maintain a temporary table (containing data from the first context) in the second context, and then join the temporary table.
我倾向于创建一个单独的数据上下文，其中只包含您要连接的两个表。但我想你可以在第二个上下文中维护一个临时表（包含来自第一个上下文的数据），然后加入临时表。
回答by bytebender
This is the "work around" that we have found...
这是我们发现的“解决方法”......
We built our tables from the other database out manually and if it is on the same server then we prefixed the table name with:
我们手动从另一个数据库构建了我们的表，如果它在同一台服务器上，那么我们在表名前加上：
<DatabaseName>.<SchemaName>.<YourTableName>
if they are on a linked server then you have to prefix it with the server name as well:
如果它们在链接服务器上，那么您还必须使用服务器名称作为前缀：
<ServerName>.<DatabaseName>.<SchemaName>.<YourTableName>
This will allow you to do joins and still return an non executed IQueryable... which is what we wanted. The other 2 ways in involve joining in-memory IEnumerables which means your pull all records for each before doing the join (above) and doing an IQueryable join using a contains method which has limitations...
这将允许您进行连接并仍然返回一个未执行的 IQueryable ......这正是我们想要的。其他 2 种方法涉及加入内存中 IEnumerables，这意味着您在执行连接（上图）之前为每个记录提取所有记录，并使用具有限制的 contains 方法进行 IQueryable 连接...
Hopefully in the future the DataContext will be built smart enough to know that if the servers are linked then you can do joins between two different ones.
希望将来 DataContext 构建得足够聪明，可以知道如果服务器已链接，那么您可以在两个不同的服务器之间进行连接。
