Ado.Net Entity : Object doesn't display linked members (foreign keys)

Question

I have a simple databasescheme: User, Account. User has 1-to-many relationship with Account.

I have generated a ado.net entity data model, and I can create users and accounts, and even link them together. In the database the account.user_id is correctly filled, so theoretically I should be able to acces User.Account.ToList() in C# through entity.

However, When I try to acces User.Account.ToList() I get zero results.

User user = db.User.First(U => U.id == 1);
List<Account> accounts = user.Account.ToList(); ##count = 0...

When I add the following code before the previous code it suddenly gives me the correct count 2.

 Account account1 = db.Account.First(A => A.id == 1);
 Account account2 = db.Account.First(A => A.id == 2);
 User user = db.User.First(U => U.id == 1);
 List<Account> accounts = user.Account.ToList(); ##count = 2...??

What am I missing here??

Answer 1

I guess my knowledge is a bit small of the framework. :)

You need to explicitly load the related accounts first.

user.Account.Load();

Now It does display correctly.

Answer 2

You should use the ObjectQuery.Include method for this. Your method works also but results in an additional query.

In your example you would get

User user = db.User.Include("Account").First(u => u.id == 1);

You have to figure out whether the string "Account" is correct. Usually it should be prefixed with something like MyEntities. This depends on the namespace of your entities but with a little trial and error you should be able to figure this out.

Answer 3

Yes, that's a common problem when starting to use the Entity framework - neither parent nor child relationships are lazy loaded so you have to load them explicitly. If you are going to share the object context around between classes / methods you might want to make a check to see if the relationship is already loaded:

e.g.

    if(!user.Account.IsLoaded)
        user.Account.Load();

You can make this easier with a simple extension method:

public static class EntityExtensions
{
 public static void EnsureLoaded(this RelatedEnd relatedEnd)
 {
  if (!relatedEnd.IsLoaded)
   relatedEnd.Load();
 }
}

using this makes your load call shorter again:

user.Account.EnsureLoaded();

And as it uses a RelatedEnd, which is common to parent and child relationships in the entity framework, you can use this for parent reference relationships too - e.g.

account.UserReference.EnsureLoaded();

As rwwilden says, if you are always going to load the child objects with the parent in this case, you might want to use an Include to make the call more efficient and avoid an extra roundtrip to the database.