问题描述
我收到来自 LINQ 查询的标题中显示的错误,该查询包括来自两个不同 edmx 文件的两个表.这是查询:
I am receiving the error shown in the title from a LINQ query that includes two tables from two different edmx files. Here is the query:
var query = (from a in db1.Table1
join b in db1.Table2 on a.Id equals b.Id
orderby a.Status
where b.Id == 1 && a.Status == "new"
select new
{
Id = a.Id,
CompanyId = (from c in db2.Company
where s.Id == a.Id
select
new { c.CompanyId })
});
db1
和 db2
是与两个不同的 edmx 文件相关联的上下文.我怎样才能克服这个错误?
db1
and db2
are contexts that are associated with two different edmx files. How can I overcome this error?
推荐答案
您必须执行两个数据库查询:
You'll have to perform two database queries:
var IDs = (from a in db1.Table1
join b in db1.Table2 on a.Id equals b.Id
orderby a.Status
where b.Id == 1 && a.Status == "new"
select new a.Id).ToArray();
var query = from c in db2.Company
join a in IDs on c.Id equals a.Id
select new { Id = a.Id, CompanyId = c.CompanyId };
.ToArray()
至关重要.它可以防止 EF 尝试执行组合查询(这将失败,因为它使用两个不同的上下文).如果您希望保持延迟加载,可以使用 .AsEnumerable()
.
The .ToArray()
is crucial. It prevents EF from trying to execute the combined query (which will fail since it uses two different contexts). You can use .AsEnumerable()
if you'd rather keep lazy loading.
以及您的后续问题:
有没有其他方法可以使 LINQ 查询更加优化?那是,在单个 LINQ 查询本身中执行操作?
为了使您的原始查询成功运行,它必须仅使用单个数据上下文,这意味着所有数据必须可从单个 EDMX 获得,而这又意味着单个连接字符串.您可以通过多种方式实现这一目标:
In order for your original query to successfully run, it must use only a single data context, which means all the data must be available from a single EDMX, which in turn means a single connection string. There are several ways you can achieve that:
- 如果两个表都在同一个数据库中,请将它们添加到一个 EDMX 中.
- 如果它们位于不同的数据库但位于同一实例上,请在其中一个数据库上创建一个视图,该视图从另一个数据库上的表中选择,然后将本地表和视图添加到单个 EDMX.
- 如果它们位于不同的实例/服务器上,则创建链接服务器,然后在链接服务器上创建表的视图,然后将本地表和视图添加到单个 EDMX.
这篇关于错误:“指定的 LINQ 表达式包含对与不同上下文关联的查询的引用"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!