错误：“指定的LINQ表达式包含对与不同上下文关联的查询的引用” [英] Error: "The specified LINQ expression contains references to queries that are associated with different contexts"

问题描述

我正在从包含两个不同edmx文件的两个表的LINQ查询中收到标题中显示的错误。这是查询：

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查询本身执行操作？

Is there any other way to make the LINQ query more optimized? That is, to perform the action in a single LINQ query itself?

为了让您的原始查询成功运行，它必须仅使用单个数据上下文，这意味着所有数据必须可以从单个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中。 li>
  
• 如果它们在不同的数据库上，但在同一个实例上，在从其他数据库的表中选择的数据库之一上创建一个视图，然后将本地表和视图添加到单个EDMX。


• 如果它们在不同的实例/服务器上，创建了一个链接服务器，然后在链接的服务器上创建一个表的视图，然后将本地表添加到一个EDMX。

这篇关于错误：“指定的LINQ表达式包含对与不同上下文关联的查询的引用”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！