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;

Solution 1

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.

Solution 2

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;

Solution 3

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.