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.

Answer 2

Another hack that works, merging the two EDMXs into a third one, then creating one ObjectContext out of it that accesses both parts.

All 3 EDMXs will need to use the same namespace.

Merging can be performed programmatically, result of merge should be: EDMX, SSDL, CSDL, MSL files; EDMX for T4s and the others for embedding as resources.

Any entities and associations that are in both source EDMXs must have exact same definition (conceptual and mapping).

You will be able to run queries on merged EDMX's context that run across both source EDMXs, joining by IDs or any other criteria.

See this link for more info on wiring up the results:
How can I create an ObjectContext from separate ssdl + csdl + msl files and no edmx?