Linq to Entities - Count elements of a query referencing two contexts

Question

I have the following query:

var query = (from q1 in query1
            join q2 in query1 on q1.idGenerico equals q2.idGenerico into q2Left
            from q2 in q2Left.DefaultIfEmpty()
            join q3 in query3 on q2.idGenerico equals q3.idGenerico  into q3Left
            from q3 in q3Left.DefaultIfEmpty()
            select new { q1, q2, q3});

Basically it is a query to group others, which are of different contexts, from it I have to take the total records resulting from this grouping, and popular a list of objects that will be returned by the query method.

my problem is: When trying to do any kind of manipulation with the query, I get the following exception:

"The specified LINQ Expression contains Ferences to queries that are Associated with Different contexts"

in SO I found the following answer (https://stackoverflow.com/questions/7332920/error-the-specified-linq-expression-contains-references-to-queries-that-are-as) that fits my case:

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.

my question is: what other solutions do I have for this problem? I am required to create a view?

Answer 1

I meeting with a more experienced developer has found the following solution:

First I managed to reduce one of the querys by already existing a database view with the data from the third.

Working with two querys, from different contexts, to take the total records (for paging) and only a sample of records (elements of a page) the queries were executed in the following order:

1 - Execute the first query, assigning it to a Iqueryable object.

2 - I apply the filters to the first query (since they are dynamic filters, which may or may not be filled, I cannot apply them to the query).

3 - Bring to a List (LINQ To Objects) the key that will make Join between the two contexts. ex: Query1.select(x => x.idTabelOther Text). Tolist();

4 - Execute the second query based on the list above.

5 - I apply the filters, also dynamic to the second query.

6 - Update the list of keys based on the records of the second filtered query.

7 - Filter the first query based on the updated list.

8 - Now that I have completed the first query based on the return of the second, I reach the first goal, the total of records.. which is the Count of the first.

9 - I collect the samples of the two separate querys bringing to memory.

10 - I make a third query grouping the lists created from the query of the first two, and reach the second objective, which was to collect the data sample.