Help with this Linq query (many-to-many join)

I have three domain objects:

Child, Classroom and ChildClassroom. Here are lists of each:

var childrens = new List<Child>() {
    new Child() { ChildId = 1, FirstName = "Chris" },
    new Child() { ChildId = 2, FirstName = "Jenny" },
    new Child() { ChildId = 3, FirstName = "Dave"  },
};

var classrooms = new List<Classroom>() {
   new Classroom() { ClassroomId = 1, FullName = "Kindergarten" },
   new Classroom() { Classr开发者_如何学JAVAoomId = 2, FullName = "Elementary"   },
   new Classroom() { ClassroomId = 3, FullName = "Secondary"    },
};

var childclassrooms = new List<ChildClassroom>() {
   new ChildClassroom() { ClassroomId = 1, ChildId = 1 },
   new ChildClassroom() { ClassroomId = 2, ChildId = 1 },
   new ChildClassroom() { ClassroomId = 3, ChildId = 2 },
};

What I want is:

 var childClassroomRelationships = new object[] {
     new {
         childid = 1,
         classrooms = new object[] {
            new { classroomId = 1, occupied = true  },
            new { classroomId = 2, occupied = true  },
            new { classroomId = 3, occupied = false }
     },
     ...
 };

What's the way to go about this in Linq?

You could do this:

var childClassroomRelationships = (
    from child in children
    select {
        childid = child.ChildId,
        classrooms = (
            from classroom in classrooms
            select new {
                classroomId = classroom.ClassroomId,
                occupied = childclassrooms.Any(
                    cc => cc.ChildId == child.ChildId),
            // Since you wanted an array.
            }).ToArray()
    // Since you wanted an array.
    }).ToArray();

What's very important here is that a join should not be used here, if it was, you would get inner join semantics, which would cause children who are not in any classrooms to not show up (which it seems you don't want from the example you gave).

Note that this will materialize all sequences because of the calls to ToArray.

Also, it is slightly inefficient, in that to check the occupancy, it has to reiterate the entire childclassroms sequence every time.

This can be improved by "indexing" the childclassrooms map for efficient lookup, like so:

IDictionary<int, HashSet<int>> classroommap = (
    from mapping in childclassrooms
    group mapping.ClassroomId by mapping.ChildId into g
    select g).ToDictionary(g => g.Key, g => new HashSet<int>(g));

This will give you a map of HashSet<int> instances which you can look up the child in once you know the classroom. With that, the first query becomes:

var childClassroomRelationships = (
    from child in children
    select {
        childid = child.ChildId,
        classrooms = (
            from classroom in classrooms
            select new {
                classroomId = classroom.ClassroomId,
                occupied = classroommap.ContainsKey(child.ChildId) &&
                    classroommap[child.ChildId].
                        Contains(classroom.ClassroomId),
            // Since you wanted an array.
            }).ToArray()
    // Since you wanted an array.
    }).ToArray();

var kidsInClass = (
    from kid in childrens
    from c in classrooms
    select new {
        ChildID = kid.ChildId,
        classrooms = (
            from cc in childclassrooms
            select new {
                ClassroomID = c.ClassroomId,
                Occupied = cc.ChildId == kid.ChildId
            }).ToArray()
    }).ToArray();