Linq to Sql - Loading Child Entities Without Using DataLoadOptions?

Question

Is it possible to load child entities in a single query without using DataLoadOptions?

I am using one data context per request in an asp.net web application and trying to get around the linq to sql limitation of not being able to change dataloadoptions once a query has been executed.

Thanks.

Answer 1

The child entities will be loaded when you first access them, so you can force Linq to Sql to load them by accessing them... :-) But I don't think that is what you were thinking of.

What is your situation? Do you have child entities stored in the same table as the parent entity and want to fetch them all using one query?

Answer 2

I have turned off lazy loading to avoid potential querying disasters. I want to be able to load an entity and all child entities in a single query without using DataLoadOptions. DataLoadOptions would be great if it could be set more than once per DataContext.

For example, If I have an Employee entity with a child Department entity, I want to fill both the Employee and Department in one database trip.

Answer 3

What about joins? E.g.:

from a in Albums join  o in Users on a.Owner equals o select new {a, o}

Answer 4

bh213: Joins would work great here if they didn't return an anonymous type. Is there any way to easily get a List<T> using a join?

Thanks.

Answer 5

If your collection is an EntitySet then you can load it by the .Load() method:

Person p = ctx.Persons.First();
p.Addresses.Load();

Answer 6

If you don't mind the link to the data context, as you say you don't, you could write a stored procedure that returns multiple results that map to your objects. Read more about it here.

Answer 7

I found the following vb.net example which manually populates child entities from an IMultipleResults type:

   Public Function GetSubjectsWithBooks() As List(Of Subject)
        Dim results As IMultipleResults = Me.GetSubjectAndBooks
        Dim Subjects = results.GetResult(Of Subject).ToList
        Dim Books = results.GetResult(Of Book).ToList
        For Each s In Subjects
            Dim thisId As Guid = s.ID
            s.FetchedBooks = (From b In Books Where b.SubjectId = thisId).ToList
        Next
        Return Subjects
    End Function

This was taken from a sample project written by Jim Wooley (one of the Link in Action authors) which can be found at:http://www.thinqlinq.com/Downloads/LinqToSqlBeyondTheBasics.zip

Omer, is this the technique you were referring to?

Answer 8

Rob Conery's blog has a way to do using a helper class he has, LazyList<T>. Also he uses custom objects to avoid the join anonymous type issue. I've used this successfully to get parent child relationships from sql without DataLoadOptions.

I think he covers it in either Pt2 or Pt3 of his MVC Storefront videos:

http://www.asp.net/learn/mvc-videos/video-351.aspx

http://www.asp.net/learn/mvc-videos/video-352.aspx

This assumes you have POCO called Category (not linq entity) and a LazyList class:

var categories = (from c in _db.Categories
                  select new Category
                  {
                  CategoryID = c.CategoryID,
                  CategoryName = c.CategoryName,
                  ParentCategoryID = c.ParentCategoryID,
                  SubCategories = new LazyList<Category>(
                       from sc in _db.Categories
                       where sc.ParentCategoryID == c.CategoryID
                       select new Category
                       {
                            CategoryID = sc.CategoryID,
                            CategoryName = sc.CategoryName,
                            ParentCategoryID = sc.ParentCategoryID
                        })
                    });

Answer 9

Use Include...

var q = from u in context.Users.Include("address")...

Would cause the address child object to be populated as well.