EF: Cross edmx (same DB) linq-to-entites query

Question

How can I use two EDMXs, in separate assemblies, yet above the SAME database,
to create a linq-to-entities query that uses them both?

E.g.

This is what I am trying to do:

using (var context1 = new Entities1())
{
    using (var context2 = new Entities2())
    {
        var items2 = context2.Items.Where(item2 => item2.Color == "Red");

        var query = context1.Items.Where(item =>
            items2.Any(item2 => item2.PainterId == item.PainterId));
    }
}

 > This results in NotSupportedException.
   Message: "The specified LINQ expression contains references to queries that are associated with different contexts."

 > This exception is throw even if Entities2 is replaced with Entities1
   (even if both contexts are from same EDMX) and both using the same connection string.

---

For comparison, this on the other hand works and results in a single SQL statement:

using (var context1 = new Entities1())
{
    var items2 = context2.Items.Where(item2 => item2.Color == "Red");

    var query = context1.Items.Where(item =>
        items2.Any(item2 => item2.PainterId == item.PainterId));
}

Constraints:

My intent is to use two EDMXs WITH designer support - no hacking EDMX in a way that breaks designer or that gets overwritten when updating from database.

EDMX #1 can not know about EDMX #2 (however #2 can know about #1).

I want the result to translate to a single SQL query, not to read results from first part to memory, then return them to database as an input for second part of query.

---

Related, but not what I am looking for:

• Cross database joins in Entity Framework4
  
• Linq to Entities / Entity Framework cross edmx "join"
  
• multiple edmx in the same .net solution
  

Answer 1

You constrained your requirements in the way that answers your question: No it is not possible. The best and only recommended solution is in the second link which references ADO.NET team blog's article about working with large models.

I wrote about a hack (and I successfully used in one project) which works as well but it has another disadvantage - you must use single context for both EDMXs. Even it worked I don't recommend using that way because it can have unexplored disadvantages because it internally omits container name which is used in many other places in EF.