How to select an object through a foreign key

Question

If I have a table with a primary key (AccLinkID) and a foreign key (aspnet_Users UserID), how can I select the object that the foreign key points to using Linq to Entities.

User myUser = _myDB.AccLinkSet.Where(user => user.LinkID == linkId).FirstOrDefault().aspnet_Users;

did not work...

anyone have any ideas?

Answer 1

Try this:

User myUser = _myDB.AccLinkSet.Include("aspnet_Users")
    .Where(user => user.LinkID == linkId).FirstOrDefault().aspnet_Users;

Answer 2

For now, with Entity Framework 1, you don't get automatic delayed loading, e.g. if you want to traverse from one entity to the next, you need to either do an .Include("OtherEntity") on your select to include those entities in the query, or you need to explicitly call .Load("OtherEntity") on your EntityContext to load that entity.

This was a design decision by the EF team not to support automagic deferred loading, since they considered it to be too dangerous; they wanted to make it clear and obvious to the user that he is also including / loading a second set of entities.

Due to high popular demand, the upcoming EF v4 (to be released with .NET 4.0 sometime towards the end of 2009) will support the automatic delayed loading - if you wish to use it. You need to explicitly enable it since it's off by default:

context.ContextOptions.DeferredLoadingEnabled = true;

See some articles on that new feature:

• A Look at Lazy Loading in EF4
• POCO Lazy Loading

Answer 3

Although you can solve this with Include, as others have suggested, that's not how I'd do it. Instead, I'd project, which never requires Include:

var q = from al in yDB.AccLinkSet
        where al.LinkID == linkId
        select al.aspnet_Users;

In this case, the users are loaded automatically.