LINQ to SQL Deferred Loading

Question

DBML

I have the following entities in my dbml: (made up for an example)

Book <- Media -> AuthorsForMedia <- Author

The arrows are the relationships in my dbml.

A book is a type of media, and an instance of a book has a media property with the values common to all media in it. AuthorsForMedia is an intersecting table with an AuthorId, a MediaId, and an AuthorsForMediaId.

Query

My get query in my repository is:

public Book Get(int id)
{
    var query = from b in db.Books
                where b.BookId == id
                select b;

    return query.Single();
}

The resulting object has the book properties set, and media property with all of its values set.

When I look at AuthorsForMedia in the Watch dialog while debugging, the following values are set:

Count = 0 
HasLoadedOrAssignedValues = false
IsDeferred = false

Question 1

Why can't the values for AuthorsForMedia (and then its corresponding Author property) be evaluated with lazy loading?

Things I Tried

After reading this question:

• LINQ to SQL eager loading with conditions

I tried the DataLoadOptions with LoadWith/AssoicateWith and it didn't work. I ended up with errors like

• Unable to create instance of class Foo Error: System.NotSupportedException: Subquery is not supported on Media of type Book
• ... System.InvalidOperationException: The expression specified must be of the form p.A, where p is the parameter and A is a property or field member
• Or the value just wasn't there

I can supply code snippets for all of this if it helps, but I think its a conceptual issue not a syntatic one.

Summary

How should I be retrieving these values, should it be a left join, or something else I haven't found so far?

Answer 1

This is how the LoadWith should look.

DataLoadOptions dlOptions = new DataLoadOptions();
dlOptions.LoadWith<Books>(book => book.AuthorsForMedia);
db.LoadOptions = dlOptions;