When using the entity objects generated by the Entity Framework, it’s tempting to utilize the drill-through properties available.  For instance, suppose you had this code sample (ctx is a reference to the ObjectContext).

var users = from u in ctx.Users

where u.IsActive == true

select u;

foreach (var u in users)

{

var recentPassword = u.UserPasswords.FirstOrDefault(i => i.IsActive == true);

if (recentPassword != null)

{

//Do something

}

}

It’s important to break this down to determine what’s happening with this code, minus the query (which is the simple part).

foreach (var u in users)

Upon looping, this first iteration actually executes the database query behind the scenes.  Simple enough.

var recentPassword = u.UserPasswords.FirstOrDefault(i => i.IsActive == true);

Here is where we have to be careful.  A reference to User.UserPasswords, which UserPasswords is a collection of children of the User entity, loads up all UserPassword objects related to that user, and then after all objects are loaded, the first object is retrieved that’s active.  So for each active user, we are loading all passwords from the database.  If we were to do:

var users = from u in ctx.Users

let rp = u.UserPasswords.FirstOrDefault(j => j.IsActive == true)

where u.IsActive == true

&& rp.IsActive == true

select u;

This statement translates to a SQL query with a subquery, and in this subquery, the passwords related to the user are queried.  All of the filtering has been done before us, and as long as we don’t need to access the UserPasswords directly, then this can suffice.  If we need the most recent record, we could also create an anonymous record:

var users = from u in ctx.Users

let rp = u.UserPasswords.FirstOrDefault(j => j.IsActive == true)

where u.IsActive == true

&& rp.IsActive == true

select { User = u, RecentPassword = rp };

In this scenario, the let statement performs a database subquery.  Each active user is represents by a property in the anonymous class, and we don’t need any future query loading.