Linq to Entities - Count elements of a query that references two contexts

-1

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 get the total of 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 sort of manipulation with the query, I get the following exception:

  

"The specified LINQ expression contains references to queries that are   associated with different contexts "

In SO I found the following answer ( link ) that fits in my case:

  

If they are 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 EDMX table and view.

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

    
asked by anonymous 16.03.2018 / 19:17

1 answer

0

I met with a more experienced developer found the following solution:

Firstly I was able to reduce one of the querys because there is already a bank view with the third data.

Working with two queries, from different contexts, to get the total of records (to page) and only one sample of records (one-page elements) queries were executed in the following order:

1 - I execute the first query, assigning it to an Iqueryable object.

2 - I apply the filters to the first query (such as dynamic filters, which may or may not be filled, I can not apply them in the query).

3 - I bring to a List (LINQ To Objects) the key that will do the join between the two contexts. ex: query1.select (x => x.addressOpenContext) .ToList ();

4 - I run the second query based on the list above.

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

6 - I update the list with 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 filtered the first query based on the second return, I reach the first goal, the total of records .. which is the count of the first.

9 - Collect the samples of the two separated queries by bringing them to memory.

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

    
16.03.2018 / 20:17