Query() with LoadAsync() does not return an entity although it should

Question

context.Pupils.Attach(pupil);

The pupil.SchoolclassCodes collection is empty but there must be a schoolclassCode because in the bottom LoadAsync method is the SchoolclassCode with the Id I query here

await context.Entry(pupil).Collection(p => p.SchoolclassCodes).Query().Where(s => s.Id == schoolclassCodeId).LoadAsync();

Fill the pupil with ONE schoolclassCode in the pupil.SchoolclassCodes collection

await context.Entry(pupil).Collection(p => p.SchoolclassCodes).LoadAsync();

Why does the bottom LoadAsync work but not the top LoadAsync?

UPDATE

Sorry for the confusion about the pupil.SchoolclassCodeId which is a [NotMappedAttribute]

This is the relationship.

Pupil N has M SchoolclassCodes.

Each entity has a collection of the other entity.

The last LoadAsync works as I said, there is no problem in the relationship setup.

The problem is the first LoadAsync does NOT worka as described above!

LAZY Loading is completely disabled! I use no virtual props anywhere!

UPDATE 2

public class SchoolclassCode
{
    public SchoolclassCode()
    {
        Pupils = new HashSet<Pupil>();
    }

    public int Id { get; set; }
    public ISet<Pupil> Pupils { get; set; }

}

public class Pupil
{
    public Pupil()
    {
        SchoolclassCodes = new HashSet<SchoolclassCode>();
    }

    public int Id { get; set; }
    public ISet<SchoolclassCode> SchoolclassCodes { get; set; }

    [NotMapped]
    public int SchoolclassCodeId { get; set; }
}

Answer 1

The field Pupil.SchoolclassCodeId is apparently unused for the purpose of this question, so let's forget it.

Your second query:

await context.Entry(pupil).Collection(p => p.SchoolclassCodes).LoadAsync();

works as expected. We can verify it with the following code:

await context.Entry(pupil).Collection(p => p.SchoolclassCodes).LoadAsync();
Console.WriteLine("IsLoaded = " + context.Entry(pupil).Collection(p => p.SchoolclassCodes).IsLoaded);
foreach (var code in pupil.SchoolclassCodes)
  Console.WriteLine("  " + code.Id);

Suppose the pupil has three elements in its SchoolclassCodes, then IsLoaded will be true, and the foreach loop will show three ids.

Then comes your first query:

await context.Entry(pupil).Collection(p => p.SchoolclassCodes).Query().Where(s => s.Id == schoolclassCodeId).LoadAsync();

and let's test it:

var pupil = context.Pupils.First();
var schoolclassCodeId = 1;
await context.Entry(pupil).Collection(p => p.SchoolclassCodes).Query().Where(s => s.Id == schoolclassCodeId).LoadAsync();
Console.WriteLine("IsLoaded = " + context.Entry(pupil).Collection(p => p.SchoolclassCodes).IsLoaded);
foreach (var code in pupil.SchoolclassCodes)
  Console.WriteLine("  " + code.Id);

Suppose there indeed is a SchoolclassCode which Id is 1, the AsyncLoad should load exactly one SchoolclassCode into memory. Yet in the output you can see IsLoaded = false, and the foreach gives nothing at all! Why?

Well, first the AsyncLoad is not applied to Collection(p => p.SchoolclassCodes), but an IQueryable derived from it, so IsLoaded should be false, this is understandable.

But one SchoolclassCode is indeed loaded into the context:

foreach (var code in context.SchoolclassCodes.Local)
  Console.WriteLine("  " + code.Id);

this foreach outputs a single 1. So why we can't find that SchoolclassCode in pupil.SchoolclassCodes?

The answer is: the relationship between SchoolclassCode and Pupil is many-to-many. In such circumstances Entity Framework does NOT do relationship fixup, i.e. automatically adding a SchoolclassCode to Pupil.SchoolclassCodes, so you'll not see it there. If you really want to fix up the relationship, you'll have to do it manually.

Update 1

Quote from MSDN:

The Query method provides access to the underlying query that the Entity Framework will use when loading related entities. You can then use LINQ to apply filters to the query before executing it with a call to a LINQ extension method such as ToList, Load, etc. The Query method can be used with both reference and collection navigation properties but is most useful for collections where it can be used to load only part of the collection.

It is a little confusing. It seems to be contradicting to my argument, but it's not. Actually, in the above quote the word "load" means "load into the context", not "load into the navigation property", so both MSDN and my answer are correct. To prove my claim, let's begin with a few experiments, then we'll dive into the source code.

The Model

For demonstration purposes, we add another class into the model:

public class Pupil
{
  public Pupil()
  {
    Book = new HashSet<Book>();
    SchoolclassCodes = new HashSet<SchoolclassCode>();
  }

  public int Id { get; set; }
  public ISet<Book> Books { get; set; }
  public ISet<SchoolclassCode> SchoolclassCodes { get; set; }
}

public class Book
{
  public int Id { get; set; }
  public Pupil Pupil { get; set; }
}

public class SchoolclassCode
{
  public SchoolclassCode()
  {
    Pupils = new HashSet<Pupil>();
  }

  public int Id { get; set; }
  public ISet<Pupil> Pupils { get; set; }
}

The relationship between Pupil and SchoolclassCode is many-to-many, as before, and the relationship between Pupil and the newly added Book is one-to-many. The context class is:

public class SchoolEntities: DbContext
{
  public SchoolEntities()
    : base("name=SchoolEntities")
  {
  }

  public DbSet<Pupil> Pupils { get; set; }
  public DbSet<Book> Books { get; set; }
  public DbSet<SchoolclassCode> SchoolclassCodes { get; set; }
}

The Data

We have the following entries in the database:

Pupil  (Id = 1)
  the Books property contains:
    Book  (Id = 1)
    Book  (Id = 2)
  the SchoolclassCodes property contains:
    SchoolclassCode  (Id = 1)
    SchoolclassCode  (Id = 2)
    SchoolclassCode  (Id = 3)

Experiment 1: Direct Load

We load related data directly into the navigation property. For simplicity, we use the Load method instead of LoadAsync. They do exactly the same, except that the former is synchronous and the latter is asynchronous. The code:

using (var context = new SchoolEntities()) {
  Console.WriteLine("Books direct load");
  var pupil = context.Pupils.First();
  context.Entry(pupil).Collection(p => p.Books).Load();
  Console.WriteLine("  IsLoaded = " + context.Entry(pupil).Collection(p => p.Books).IsLoaded);
  Console.WriteLine("  Items in the pupil:");
  foreach (var item in pupil.Books)
    Console.WriteLine("    " + item.Id);
  Console.WriteLine("  Items in the context:");
  foreach (var item in context.Books.Local)
    Console.WriteLine("    " + item.Id);
}
using (var context = new SchoolEntities()) {
  Console.WriteLine("SchoolclassCodes direct load");
  var pupil = context.Pupils.First();
  context.Entry(pupil).Collection(p => p.SchoolclassCodes).Load();
  Console.WriteLine("  IsLoaded = " + context.Entry(pupil).Collection(p => p.SchoolclassCodes).IsLoaded);
  Console.WriteLine("  Items in the pupil:");
  foreach (var item in pupil.SchoolclassCodes)
    Console.WriteLine("    " + item.Id);
  Console.WriteLine("  Items in the context:");
  foreach (var item in context.SchoolclassCodes.Local)
    Console.WriteLine("    " + item.Id);
}

and the output:

Books direct load
  IsLoaded = True
  Items in the pupil:
    1
    2
  Items in the context:
    1
    2
SchoolclassCodes direct load
  IsLoaded = True
  Items in the pupil:
    1
    2
    3
  Items in the context:
    1
    2
    3

The experiment is divided into two parts, one for Books and one for SchoolclassCodes. Two contexts are used to make sure the two parts do not interfere with each other. We use the collection's Load method to load related data directly into the navigation property. The results show that:

1. the collection's IsLoaded property is set to true;
2. the loaded data can be found in the navigation properties (i.e. pupil.Books and pupil.SchoolclassCodes);
3. the loaded data can also be found in the context (i.e. context.Books.Local and context.SchoolclassCodes.Local).

Experiment 2: Partial Load with Query

We load part of the related data using the Query method followed by a Where:

using (var context = new SchoolEntities()) {
  Console.WriteLine("Books partial query load");
  var pupil = context.Pupils.First();
  context.Entry(pupil).Collection(p => p.Books).Query().Where(s => s.Id == 1).Load();
  Console.WriteLine("  IsLoaded = " + context.Entry(pupil).Collection(p => p.Books).IsLoaded);
  Console.WriteLine("  Items in the pupil:");
  foreach (var item in pupil.Books)
    Console.WriteLine("    " + item.Id);
  Console.WriteLine("  Items in the context:");
  foreach (var item in context.Books.Local)
    Console.WriteLine("    " + item.Id);
}
using (var context = new SchoolEntities()) {
  Console.WriteLine("SchoolclassCodes partial query load");
  var pupil = context.Pupils.First();
  context.Entry(pupil).Collection(p => p.SchoolclassCodes).Query().Where(s => s.Id == 1).Load();
  Console.WriteLine("  IsLoaded = " + context.Entry(pupil).Collection(p => p.SchoolclassCodes).IsLoaded);
  Console.WriteLine("  Items in the pupil:");
  foreach (var item in pupil.SchoolclassCodes)
    Console.WriteLine("    " + item.Id);
  Console.WriteLine("  Items in the context:");
  foreach (var item in context.SchoolclassCodes.Local)
    Console.WriteLine("    " + item.Id);
}

Most of the code is the same as in Experiment 1; please pay attention to the lines beginning with context.Entry(pupil).... The output:

Books partial query load
  IsLoaded = False
  Items in the pupil:
    1
  Items in the context:
    1
SchoolclassCodes partial query load
  IsLoaded = False
  Items in the pupil:
  Items in the context:
    1

See the difference?

1. IsLoaded is now false in both cases;
2. the loaded data still go into the context;
3. however, the loaded data do not go into the navigation property in the SchoolclassCodes case, while it do in the Books case.

The difference is caused by the types of relationship: Books is one-to-many, while SchoolclassCodes is many-to-many. Entity Framework treats those two types differently.

Experiment 3: Full Load with Query

So what if we use Query without a Where? Let's see:

using (var context = new SchoolEntities()) {
  Console.WriteLine("Books full query load");
  var pupil = context.Pupils.First();
  context.Entry(pupil).Collection(p => p.Books).Query().Load();
  // output statements omitted...
}
using (var context = new SchoolEntities()) {
  Console.WriteLine("SchoolclassCodes full query load");
  var pupil = context.Pupils.First();
  context.Entry(pupil).Collection(p => p.SchoolclassCodes).Query().Load();
  // output statements omitted...
}

The output:

Books full query load
  IsLoaded = False
  Items in the pupil:
    1
    2
  Items in the context:
    1
    2
SchoolclassCodes full query load
  IsLoaded = False
  Items in the pupil:
  Items in the context:
    1
    2
    3

Even though we load all the related data, IsLoaded is still false, and the loaded data still do not go into SchoolclassCodes. Apparently Load() is not the same as Query().Load().

Source Code of the Query Method

So what's happening under the hood? The source code of EF6 can be found on CodePlex. The following Query call:

context.Entry(pupil).Collection(p => p.Books).Query()

can be traced to the following code fragment, which I have edited for clarity:

string sourceQuery = GenerateQueryText();
var query = new ObjectQuery<TEntity>(sourceQuery, _context, mergeOption);
AddQueryParameters(query);
return query;

Here TEntity is Book, _context is the ObjectContext behind our DbContext, and sourceQuery is the following Entity SQL statement:

SELECT VALUE [TargetEntity]
FROM (SELECT VALUE x
      FROM [SchoolEntities].[Pupil_Books] AS x
      WHERE Key(x.[Pupil_Books_Source]) = ROW(@EntityKeyValue1 AS EntityKeyValue1)) AS [AssociationEntry]
INNER JOIN [SchoolEntities].[Books] AS [TargetEntity]
  ON Key([AssociationEntry].[Pupil_Books_Target]) = Key(Ref([TargetEntity]))

After AddQueryParameters the parameter @EntityKeyValue1 is bound to the value 1, which is the Id of the pupil. So the above query is basically the same as:

context.Books.Where(s => s.Pupil.Id == pupil.Id)

That is, the Query method just constructs a query that retrieves Books with Pupil.Id matching Id of the given pupil. It has nothing to do with loading data into pupil.Books. This also holds in the case of pupil.SchoolclassCodes.

Source Code of the Collection's Load Method

Next we check the following method call:

context.Entry(pupil).Collection(p => p.Book).Load()

This Load call leads to the following (edited again for clarity):

var sourceQuery = CreateSourceQuery<TEntity>(mergeOption, out hasResults);
IEnumerable<TEntity> refreshedValues;
refreshedValues = sourceQuery.Execute(sourceQuery.MergeOption);
Merge(refreshedValues, mergeOption, true /*setIsLoaded*/);

As you can see, it constructs a query, which is exactly the same query we've seen above, then it executes the query and receives the data in refreshedValues, and finally it merges the data into the navigation property, i.e. pupil.Books.

Source Code of the Load Method Following Query

What if we do Load after Query?

context.Entry(pupil).Collection(p => p.Book).Query().Load()

This Load is defined as an extension method in the QueryableExtensions class, and it's quite straightforward:

public static void Load(this IQueryable source)
{
    Check.NotNull(source, "source");

    var enumerator = source.GetEnumerator();
    try
    {
        while (enumerator.MoveNext())
        {
        }
    }
    finally
    {
        var asDisposable = enumerator as IDisposable;
        if (asDisposable != null)
        {
            asDisposable.Dispose();
        }
    }
}

Yes, this time the full source code is shown; I didn't edit anything. And that's right, it is effectively an empty foreach, looping through all the loaded items and do absolutely nothing with them. Except something has been done: those items are added into the context, and if the relationship is one-to-many, relationship fix-up kicks in and fixes the associations up. This is part of the enumerator's job.

One More Experiment: Side Load

In the above we see that the collection's Query method simply constructs an ordinary query (an IQueryable). There are, of course, more than one way to construct such a query. We don't have to begin with context.Entry(...).Collection(...). We can begin right from the top:

using (var context = new SchoolEntities()) {
  Console.WriteLine("Books side load");
  var pupil = context.Pupils.First();
  context.Books.Where(s => s.Pupil.Id == pupil.Id).Load();
  // output statements omitted...
}
using (var context = new SchoolEntities()) {
  Console.WriteLine("SchoolclassCodes side load");
  var pupil = context.Pupils.First();
  context.SchoolclassCodes.Where(s => s.Pupils.Select(t => t.Id).Contains(pupil.Id)).Load();
  // output statements omitted...
}

The output:

Books side load
  IsLoaded = False
  Items in the pupil:
    1
    2
  Items in the context:
    1
    2
SchoolclassCodes side load
  IsLoaded = False
  Items in the pupil:
  Items in the context:
    1
    2
    3

Exactly the same as in Experiment 3.

Update 2

To delete part of the associations in a many-to-many relationship, the officially recommended way is to Load all the related objects first and then remove the associations. For example:

context.Entry(pupil).Collection(p => p.SchoolclassCodes).Load();
var code = pupil.SchoolclassCodes.Where(...).First();
pupil.SchoolclassCodes.Remove(code);
context.SaveChanges();

This might, of course, load unneeded related objects from the database. If that's undesirable, we can drop down to ObjectContext and use ObjectStateManager:

var code = context.Entry(pupil).Collection(p => p.SchoolclassCodes).Query().Where(...).First();
var objectStateManager = ((IObjectContextAdapter)context).ObjectContext.ObjectStateManager;
objectStateManager.ChangeRelationshipState(pupil, code, p => p.SchoolclassCodes, EntityState.Deleted);
context.SaveChanges();

This way only the relevant related object is loaded. In fact, if we already know the primary key of the related object, even that one can be eliminated:

var code = new SchoolclassCode { Id = 1 };
context.SchoolclassCodes.Attach(code);
var objectStateManager = ((IObjectContextAdapter)context).ObjectContext.ObjectStateManager;
objectStateManager.ChangeRelationshipState(pupil, code, p => p.SchoolclassCodes, EntityState.Deleted);
context.SaveChanges();

Note, however, that EF7 will remove ObjectContext, so the above code will have to be modified if we want to migrate to EF7 in the future.