Why do I receive The specified LINQ expression contains references to queries that are associated with different contexts?

Question

Exception:
System.NotSupportedException: 'The specified LINQ expression contains references to queries that are associated with different contexts.'

Code:

IEnumerable<EntityIdName> entitiesIDs = 
    (from a in afimDB.UNITES
     select new EntityIdName { entityId = (int)a.UNIT_ID, entityName = a.UNIT_NAME })
    .AsEnumerable();

var usersWithEntities = (
    from user in imdb.AspNetUsers
    select new
    {
        UserId = user.Id,
        Username = user.UserName,
        Email = user.Email,
        EntityNames = (
            from a in imdb.UserEntities
            join b in imdb.AspNetUsers on a.UserID equals b.Id
            where user.Id == a.UserID
            select new 
            { 
                entity = (
                    from q in entitiesIDs 
                    where q.entityId == 2 
                    select q.entityName)
                    .ToArray()
            })
            .ToList() 
        })
        .AsEnumerable()
        .Select(p => new Users_in_Entities_ViewModel()
        {
            UserId = p.UserId,
            Username = p.Username,
            Email = p.Email,
            Entity = string.Join(",", p.EntityNames)
        });

Is this your actual code? Because it doesn't look like this should even compile. What sort of results are you actually trying for? — Corey, Oct 03 '18 at 03:01

score 0 · Answer 1 · answered Oct 02 '18 at 23:03

0

This part of the query doesn't make any sense:

EntityNames = (
    from a in imdb.UserEntities
    join b in imdb.AspNetUsers on a.UserID equals b.Id
    where user.Id == a.UserID
    select new 
    { 
        entity = (
            from q in entitiesIDs 
            where q.entityId == 2 
            select q.entityName)
            .ToArray()
    })
    .ToList() 
})

Why are you selecting data from a table and then joining to another to discard it all completely?

This can be simplified a lot:

string unitNames = string.Join(", ", afimDB.UNITES
    .Where(a => a.UNIT_ID == 2)
    .Select(a => a.UNIT_NAME));

var usersWithEntities = (
    from user in imdb.AspNetUsers
    select new Users_in_Entities_ViewModel
    {
        UserId = user.Id,
        Username = user.UserName,
        Email = user.Email,
        Entity = unitNames 
    });

answered Oct 02 '18 at 23:03

Camilo Terevinto

31,141
6
88
120

But this part of code dos not meet what I'm trying to: – Sameer Oct 04 '18 at 23:31
string unitNames = string.Join(", ", afimDB.UNITES .Where(a => a.UNIT_ID == 2) .Select(a => a.UNIT_NAME)); – Sameer Oct 04 '18 at 23:32
where user.Id == a.UserID – Sameer Oct 04 '18 at 23:33
@Sameer What? Your code doesn't do that. I would suggest you to re-read what you posted – Camilo Terevinto Oct 04 '18 at 23:39

Steve Py · Answer 2 · 2018-10-04T23:55:20.543

0

The error you are getting is due to:

IEnumerable<EntityIdName> entitiesIDs = 
    (from a in afimDB.UNITES
     select new EntityIdName { entityId = (int)a.UNIT_ID, entityName = a.UNIT_NAME })
    .AsEnumerable();

AsEnumerable does not fully execute the EF expression. To do that, you need to use ToList()

IEnumerable<EntityIdName> entitiesIDs = 
    (from a in afimDB.UNITES
     select new EntityIdName { entityId = (int)a.UNIT_ID, entityName = a.UNIT_NAME })
    .ToList();

Now you will have a POCO list of EntityIdName that can be referenced within the second EF expression. EF will execute that second query against the imDB context which cannot join in an expression from the afimDB context.

Edit: To demonstrate the behaviour & adjustment. This is just an example using 2 DbContext instance scopes to demonstrate that a query from one context cannot be used within another.

    using (var context2 = new TestDbContext())
    {
        var ids = context2.Customers
            .Where(x => x.Name == "Fred")
            .Select(x => x.CustomerId)
            .AsEnumerable();
        using (var context = new TestDbContext())
        {
            var customers = context.Customers.Where(x => ids.Contains(x.CustomerId))
                .ToList();
        }
    }

Above fails with exeception on the retrieval of the Customers with "The specified LINQ expression contains references to queries that are associated with different contexts."

    using (var context2 = new TestDbContext())
    {
        var ids = context2.Customers
            .Where(x => x.Name == "Fred")
            .Select(x => x.CustomerId)
            .ToList(); // <- The .ToList you need
        using (var context = new TestDbContext())
        {
            var customers = context.Customers.Where(x => ids.Contains(x.CustomerId))
                .ToList();
        }
    }

Works as expected. .ToList() executes the first query against the DB into a POCO list which is safe in the scope of the second query. .AsEnumerable() does not.

Kindly keep comments constructive, and perhaps try it before down-voting.

edited Oct 04 '18 at 23:55

answered Oct 03 '18 at 04:26

Steve Py

26,149
3
25
43

I wonder from where did you get this information: "AsEnumerable does not execute the EF expression." as it's not correct. Please read https://stackoverflow.com/questions/31837028/why-is-entity-frameworks-asenumerable-downloading-all-data-from-the-server to see why it's incorrect – Camilo Terevinto Oct 03 '18 at 11:14
https://stackoverflow.com/questions/17968469/whats-the-differences-between-tolist-asenumerable-asqueryable "The advantage of using AsEnumerable vs. ToList is that AsEnumerable does not execute the query. *AsEnumerable preserves deferred execution* and does not build an often useless intermediate list." In this case, that deferred execution is resulting in cross-DbContext queries. – Steve Py Oct 03 '18 at 11:38
Oh my... please read the entire answer you linked to, which links to this other answer: https://stackoverflow.com/questions/5311034/what-is-the-effect-of-asenumerable-on-a-linq-entity which reads "It's usually used when you want to force part of a query to run as SQL (or similar), and the remainder to run using LINQ to Objects." AsEnumerable does not execute the LINQ to Objects query but it does excecute the LINQ to Entities Query – Camilo Terevinto Oct 03 '18 at 11:41
Except it doesn't.. Try it. `using (var context = new TestDbContext()) { var customers = context.Customers.Where(x => x.Name == "Steve") .AsEnumerable(); // SQL didn't fire.. var test = customers.ToList(); // Fired after this though... }` I had a trace running with a breakpoint on each line. The execution of the `.AsEnumerable()` *did not* execute the query.. the `.ToList()` did. If it doesn't touch the DB then the first DbContext is needed and the entities cannot join within the scope of the second. – Steve Py Oct 03 '18 at 21:29
Here both 'context2' and 'context' refer to the same 'TestDbContext', while mine 'afimDB' and 'imDB' does not. – Sameer Oct 04 '18 at 23:38
Yes, whether the 2 instances of a context are the same definition or not isn't relevant, the error is the same. I used the same definition as a minimum viable example. If they were the same definition then the issue would be resolved by just joining within 1 instance. In your case you need different DbContexts so you must `.ToList()` the first results to use them within the second. – Steve Py Oct 04 '18 at 23:52

Why do I receive The specified LINQ expression contains references to queries that are associated with different contexts?

2 Answers2