LINQ EF Join query from 2 different data context

Question

I am using LINQ to retrieve data from my EF context as well as from Asp .Net Identity 2.0 - both located in the same MS SQL Server database.

My problem is that LINQ sees them as 2 different cases of data context and is unable to process the query.

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

What I want to achieve is a simple return of 10 top items (I skip this in the code extract) from EF table, previously sorted by the UserName from ASP .NET Identity table.

I have seen a few cases of this problem on StackOverflow but I was unable to apply any solution in my case.

The preferred solution would be of course not to download all of the table data and do the sorting on the server.

The query in question:

var dbDataSorted = from entry in dbData
        join user in this.UserManager.Users
        on entry.UserId equals new Guid(user.Id)
        orderby user.UserName ascending
        select entry;
return dbDataSorted;

You can do a server based join with 2 different contexts, even if they are using the same database, each context needs to know about all the tables in the query. — Ben Robinson, Aug 12 '15 at 08:26
one ugly way of achieving here is write a stored procedure that would join both database tables other options are in [this](http://stackoverflow.com/a/7339159/1505865) answer — Jenish Rabadiya, Aug 12 '15 at 09:16

score 5 · Answer 1 · answered Sep 07 '17 at 15:05

5

I was able to get this to work in my case by using AsEnumerable(). YMMV.

In your case:

var dbDataSorted = from entry in dbData.AsEnumerable()
    join user in this.UserManager.Users
    on entry.UserId equals new Guid(user.Id)
    orderby user.UserName ascending
    select entry;
return dbDataSorted;

answered Sep 07 '17 at 15:05

Brian Davis

745
1
11
14

3

AsEnumerable() works because it forces that part of the query to be performed client-side. As long as dbData doesn't return a ton of records, it will work well enough for most cases. – Eric J. Sep 26 '17 at 15:39
1

This solved the problem for me. I was joining from two different dbcontexts, one inheriting IdentityDbContext, the other not, and needing to join over the userid. Thanks. – edtruant Sep 07 '18 at 10:30
1

To get this working you need to enable the "MultipleActiveResultSets=true" parameter in your connection string anyway; otherwise you get a "There is already an open DataReader associated with this Command which must be closed first" error. – edtruant Oct 22 '18 at 14:27

score 3 · Accepted Answer · answered Aug 12 '15 at 09:28

LINQ and EF are pretty cool. But sometimes, its abstractions don't offer what you need.

Just fall back to base, write the query by hand, put it in a string, run it against yourcontext.YourDbSet with the SqlQuery method, and be done with it.

var query = @"SELECT * FROM dbData as entry
              INNER JOIN Users
              ON entry.UserId = Users.Id
              ORDER BY Users.Username";

yourcontext.dbData.SqlQuery(query);

If the abstractions offered to you don't work with what you need, abusing the abstractions to do something weird is far less clear than using the lower level interface.

This is the solution that I decided to use. Your arguments were convincing and it works as intended. Thank you. — Quass1m, Aug 12 '15 at 11:00

Pixel · Answer 3 · 2015-08-12T09:17:21.617

1

You can't use Linq to Entities and Linq to object in one query. It's because when you make query to instance of IQueryable interface, the query will be transformed to SQL, and in SQL you can't use any IEnumerable collection. So you should get data from database (for example dbData.AsEnumerable()). But if you want do all job on the SQL Server side, the easiest way is to create sql procedure and pass the users table as a parameter. The easiest way to pass users table as xml and than parse it in the procedure on the server side.

edited Aug 12 '15 at 09:17

answered Aug 12 '15 at 08:47

Pixel

315
1
5

Adding .AsEnumerable() gives me conversion error from 'System.Collections.Generic.IEnumerable<...>' to 'System.Linq.IQueryable<...>'. That said, I do not want to download all of the data from db so it is not the solution I will implement. Thanks for the replay anyway. – Quass1m Aug 12 '15 at 09:16
I edited the answer, if you don't want to load all data, you should somehow upload your data to sql server and there you can do everything you want with it. I think it's imposible do only by linq. But you can use a procedure as entity – Pixel Aug 12 '15 at 09:22

score 0 · Answer 4 · answered Aug 12 '15 at 09:19

0

If you're using ASP.NET Identity 2 code-first, then your DbContext presumably inherits from IdentityDbContext<>, and so you can access the Users table directly from the context. You don't need to use UserManager to access the users.

answered Aug 12 '15 at 09:19

Gary McGill

26,400
25
118
202

LINQ EF Join query from 2 different data context

4 Answers4