Graeme Hill's Dev Blog

By default, LINQ to SQL uses deferred loading. When you want to eager load an entity's associated data you need to set DataLoadOptions using the LoadOptions property on the DataContext. If you have a one-to-many relationship between Users and Articles you can force LINQ to SQL to eager load Articles with Users like this:

Using testData As New TestDataContext

    ' Log SQL queries to the console
    testData.Log = Console.Out

    ' Set LoadOptions
    Dim options As New DataLoadOptions
    options.LoadWith(Function(user As User) user.Articles)
    testData.LoadOptions = options

    ' Load users with their articles
    Dim users = testData.Users.ToList
    For Each user In users
        Dim articles = user.Articles.ToList
    Next

End Using

This will generate a single SELECT statement with a JOIN on the Articles table. The same goes for for one-to-one relationships. You can also use LoadWith as many times as you want. For one-to-one relationships and no more than a single one-to-many relationship this will still generate one query with JOINs to all the LoadWith tables. However, if you want to eager load multiple one-to-many relationships you will get into a select N + 1 situation (or worse). For example, this code eager loads Articles and UserGroups with each User entity:

Using testData As New TestDataContext

    ' Log SQL queries to the console
    testData.Log = Console.Out

    ' Set LoadOptions
    Dim options As New DataLoadOptions
    options.LoadWith(Function(user As User) user.Articles)
    options.LoadWith(Function(user As User) user.UserGroups)
    testData.LoadOptions = options

    ' Load users with their articles
    Dim users = testData.Users.ToList
    For Each user In users
        Dim articles = user.Articles.ToList
        Dim userGroups = user.UserGroups.ToList
    Next

End Using

Technically, the behaviour here is correct. It will successfully eager load both the Articles and UserGroups collections for each User, but it will not do it in a single query. When I ran this I got one query that fetched the Users and Articles like last time, but then a separate SELECT for each UserGroup rather than another JOIN. Even though this won't alter the behaviour of the code, it will definitely make a major impact on performance, especially if there are a lot of users in the database.

Scott Guthrie confirmed this behaviour in a post on David Hayden's blog. This is what he said:

In the case of a 1:n associations, LINQ to SQL only supports joining-in one 1:n association per query.

Lame.