LINQ to SQL and Self Related Table

Question

We have the following test model in the dbml file:

For the test case there are 4 records in the table, 1 parent, 3 children. We are looking for the siblings of a specific record, including the specific record.

using (var db = new TestDataContext())
{
    var query = 
        from f in db.Foos
        where f.Name == "Two"
        select f.Foo1.Foos;              // get the record's parent's children

    var foos = query.SelectMany(f => f); // project the EntitySet

    Assert.AreEqual(3, foos.Count());    // passes
}

This returns the correct items with the following SQL:

SELECT     [t2].[FooId], 
           [t2].[ParentFooId], 
           [t2].[Name]
FROM       [dbo].[Foos] AS [t0]
INNER JOIN [dbo].[Foos] AS [t1] ON [t1].[FooId] = [t0].[ParentFooId]
CROSS JOIN [dbo].[Foos] AS [t2]
WHERE      ([t0].[Name] = @p0) 
AND        ([t2].[ParentFooId] = [t1].[FooId])

We are wondering about the CROSS JOIN, this apparently is the result of the SelectMany?
Is there another way we should approach this in order to not have the CROSS JOIN?

Answer 1

You could alternatively do:

var query = from f in db.Foos
            where (from fo in db.Foos
                   where fo.Name == "Two"
                   select fo.ParentId).Contains(f.ParentId)
            select f;

This should result in something like:

SELECT     [t1].[FooId], 
           [t1].[ParentFooId], 
           [t1].[Name]
FROM       [dbo].[Foos] AS [t1]
WHERE      [t1].[ParentFooId] IN (SELECT [t0].[ParentFooId]
                                  FROM [dbo].[Foos] AS [t0]
                                  WHERE[t0].[Name] = @p0)

May differ a bit (possibly an Exists()depending on your model)...I don't have a profiler window handy.

Answer 2

You can stack from statements in a Linq query and that will probably help you out here.

var query = from f in db.Foos
            from f2 in f.Foos
            where f.Name == "Two"
            select f2;

Which produces.

SELECT [t1].[FooId],
       [t1].[Name],
       [t1].[ParentFooId]
FROM [dbo].[Foos] AS [t0], [dbo].[Foos] AS [t1]
WHERE ([t0].[Name] = @p0) AND ([t1].[ParentFooId] = [t0].[FooId])

Answer 3

Try this:

var siblings = DataContext.Foos.Where(a => a.FooID == 3)
    .Select(b => Foos.Where(b => Foos.ParentFooID == a.ParentFooID));

Assert.AreEqual(3, siblings.Count());