Linq SelectMany include parent

Question

I have the following three Linq To Sql entities: Location, Institution, and Building.

The Location table is simply a LocationId, and LocationTypeId.

Institution and Building both get their Ids from the Location table in a one to one relationship, and the Institution table has a one to many relationship with the Building table.

I am trying to return all location records for an institution and all of its children. The following query returns what I need except that it is missing the parent Institution location record.

var query = dc.Locations.SelectMany(l => l.Institutions, (loc, inst) => new { loc, inst })
                        .SelectMany(i => i.inst.Buildings, (locInst, bld) => bld.Location );

How can I include the parent along with the children?

UPDATE:

I can get the records I want if I use a union on the original location query, but I am still wondering if this is the best solution. Here is the code with the Union.

IQueryable<Location> locations = dc.Locations;

locations.SelectMany(l => l.Institutions, (loc, inst) => new { loc, inst })
         .SelectMany(i => i.inst.Buildings, (locInst, bld) => bld.Location)
         .Union(locations);

Answer 1

Well that's a tricky question, I don't think it can get better then that, although if you want some sort of ordering like 1st the institution location then the Institutions Buildings Locations you can do this:

locations.SelectMany(l => l.Institutions, (loc, inst) => new { loc, inst })
         .SelectMany(i => i.inst.Buildings.Union(new List<Building> { new Building { Location = i.loc } }), (locInst, bld) => bld.Location);

But the code doesn't get any cleaner from here, your solutions is pretty straight forward and it gets the job done.

This will affect performance and will consume more memory (if it matters) however if this is what you're looking for, I hope it helped. (Because of the order that the Locations are stored)