help with LINQ join

Question

Hi - I'd like to get some help on how to formulate this query:

I Have a snapshot of the datamodel relevant to this question at: http://jdsign.dk/media/9221/dbdiagram.png

To the method that should be created the input is a guid userid in the following called inputuserid.

Please help me get: a list of all the subjects that have any note where (note => note.UserId == inputuserid) the subjectlist must also contain all the subjects that are associated with the inputuserid in the Schemas table.

Some subjects may meet both the requirements, but I only want one instance of a subject in the outputsubjectlist.

The outputsubjectlist that is returned to the user, should be loadedwith the notes where (note => note.UserId == inputuserid)

The first part of getting the subjectlist is not that hard I've done it like this: return this.DataContext.Subjects.Where(s => s.Notes.Any(n => n.UserId == userid) || s.Schemas.Any(sc => sc.UserId == userid)).Distinct();

but how do I get the related Notes loaded into the subject?

Answer 1

Assuming you have the default parent child relationships set up:

var outputsubjectlist = from sub in dc.Subjects
where sub.Notes.Any(note => note.UserId == inputuserid)
    || sub.Schemas.Any(schema => schema.UserId == inputuserid)
select sub

Answer 2

It sounds like "recursive" got almost 100%. The missing output is the list of Notes that match the inpuuserid.

Isn't then the answer this:

var outputsubjectlist = from sub in dc.Subjects
where sub.Notes.Any(note => note.UserId == inputuserid)
    || sub.Schemas.Any(schema => schema.UserId == inputuserid)
select new
{
    Subject = sub,
    Notes = sub.Notes.Where(note => note.UserId == inputuserid).ToArray(),
}

(I added to the .ToArray() to force evaluation.)