How to eager load sibling data using LINQ to SQL?

Question

The goal is to issue the fewest queries to SQL Server using LINQ to SQL without using anonymous types. The return type for the method will need to be IList<Child1>. The relationships are as follows:

            Parent
    Child1          Child2
Grandchild1

Parent > Child1 is a one-to-many relationship

Child1 > Grandchild1 is a one-to-n relationship (where n is zero to infinity)

Parent > Child2 is a one-to-n relationship (where n is zero to infinity)

I am able to eager load the Parent, Child1 and Grandchild1 data resulting in one query to SQL Server.

This query with load options eager loads all of the data, except the sibling data (Child2):

DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Child1>(o => o.GrandChild1List);
loadOptions.LoadWith<Child1>(o => o.Parent);

dataContext.LoadOptions = loadOptions;

IQueryable<Child1> children = from child in dataContext.Child1
                                select child;

I need to load the sibling data as well. One approach I have tried is splitting the query into two LINQ to SQL queries and merging the result sets together (not pretty), however upon accessing the sibling data it is lazy loaded anyway.

Adding the sibling load option will issue a query to SQL Server for each Grandchild1 and Child2 record (which is exactly what I am trying to avoid):

DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Child1>(o => o.GrandChild1List);
loadOptions.LoadWith<Child1>(o => o.Parent);
loadOptions.LoadWith<Parent>(o => o.Child2List);

dataContext.LoadOptions = loadOptions;

IQueryable<Child1> children = from child in dataContext.Child1
                                select child;


exec sp_executesql N'SELECT * FROM [dbo].[Child2] AS [t0]
WHERE [t0].[ForeignKeyToParent] = @p0',N'@p0 int',@p0=1

exec sp_executesql N'SELECT * FROM [dbo].[Child2] AS [t0]
WHERE [t0].[ForeignKeyToParent] = @p0',N'@p0 int',@p0=2

exec sp_executesql N'SELECT * FROM [dbo].[Child2] AS [t0]
WHERE [t0].[ForeignKeyToParent] = @p0',N'@p0 int',@p0=3

exec sp_executesql N'SELECT * FROM [dbo].[Child2] AS [t0]
WHERE [t0].[ForeignKeyToParent] = @p0',N'@p0 int',@p0=4

I've also written LINQ to SQL queries to join in all of the data in hopes that it would eager load the data, however when the LINQ to SQL EntitySet of Child2 or Grandchild1 are accessed it lazy loads the data.

The reason for returning the IList<Child1> is to hydrate business objects.

My thoughts are I am either:

1. Approaching this problem the wrong way.
2. Have the option of calling a stored procedure?
3. My organization should not be using LINQ to SQL as an ORM?

Any help is greatly appreciated.

Thank you,

-Scott

Answer 1

What you have should be correct, you need to add this dataContext.DeferredLoadingEnabled = false; in addition to the LoadOptions you are already setting.

Answer 2

var children2 = from child2 in dataContext.Child2
                where children.Any(c1 => c1.Parent == child2.Parent)
                select child2;

Should result in a single exists query, so it will end up being two queries.