Linq从两个edmx（两个数据库）中选取数据进行查找出现错误解决 （指定的 LINQ 表达式包含对与不同上下文关联的查询）

总思想：不让两个链接（connection）同时发生。尽量使用一个EDMX（在一个EDMX中创建另一个的视图），如果不可以，需要分别进行查询不能放在一个语句中

1.http://www.cnblogs.com/Joetao/articles/2106426.html

class Program
    {
        static void Main(string[] args)
        {
            using(UserDBEntities context=new UserDBEntities() )
            {
                using (WaterManageEntities client = new WaterManageEntities())
                {         
                    int ID=client.Layers.FirstOrDefault().LayerID ;
                    var query = from s in context.NewsTypes where s.ID ==ID select s;                          
                    foreach (NewsType type in query)
                    {
                        
                        Console.WriteLine(type.NAME);
                        Console.ReadKey();
                    }
                }
            }
        }
    }

2.http://stackoverflow.com/questions/7332920/the-specified-linq-expression-contains-references-to-queries-that-are-associated

 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 and db2 are instances to two different edmx files.

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 };

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.

EDIT: 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:

• If both tables are on the same database, add them both to a single EDMX.
• If they're on different databases but on the same instance, create a view on one of the databases that selects from the table on the other database, then add the local table and view to a single EDMX.
• If they're on different instances/servers, created a linked server, then create a view of the table on the linked server, then add the local table and view to a single EDMX.

.ToArray()是至关重要的。 它阻止EF试图执行联合查询(这将会失败,因为它使用了两个不同的上下文中)。 您可以使用.AsEnumerable()如果你宁愿保持延迟加载。
编辑:为了您的原始查询成功运行,它必须只使用单个数据上下文中,这意味着所有可用的数据必须从单一EDMX,反过来意味着一个连接字符串。 有几种方法可以实现:
如果两个表是相同的数据库,将它们添加到一个EDMX。
如果他们不同的数据库,而是同一实例,创建一个视图的一个数据库,从表中选择其他数据库,然后添加单个EDMX当地表和视图。
如果他们在不同的实例/服务器上,创建了一个链接服务器,然后创建一个链接的服务器上的表视图,然后添加单个EDMX当地表和视图。