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; }
}
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.
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 asToList
,Load
, etc. TheQuery
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.
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; }
}
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)
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:
IsLoaded
property is set to true
;pupil.Books
and pupil.SchoolclassCodes
);context.Books.Local
and context.SchoolclassCodes.Local
).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?
IsLoaded
is now false
in both cases;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.
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()
.
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
.
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
.
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.
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.
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.