I need some help with an advanced LINQ query, to be executed as Linq to SQL (SQL Server), in C# using ASP.NET 4.0.
To boil it down to the essential problem, I'll use the following table structure:
3 tables: Mothers, Daughters, Sons
Columns in each table:
Mothers: MotherId, FirstName, LastName
Daughters: DaughterId, MotherId, int Age
Sons: SonId, MotherId, int Height
Each Daughter belongs to a Mother (using MotherId foreign key).
Each Son belongs to a Mother (using MotherId foreign key).
Each Mother can have zero or more Sons and zero or more Daughters.
I'm trying to write a query that will return the following:
Give me all Mothers with FirstName "Mary" who has:
- a Daughter of Age 21 (or no daughters),
- or a son with Height 72 inches (or no sons).
It's OK to have both matching Daughter(s) and Son(s).
In other words, if the Mother has any daughters, at least one of the daughters must be 21.
And if the Mother has any sons, at least one of the sons must be 72 inches.
In the result set, return all the matching Sons and Daughters along with the Mother, with each Mother/Daughter/Son triplet in a flattened record.
Also the query needs to be efficient, because we have millions of Mothers/Sons/Daughters in the database.
I have tried multiple approaches using LINQ expression syntax and lambda expressions / extension methods, but I either am only getting the Mothers that have both a matching son and daughter (which is not necessary), or I am getting too many Mothers.
So here's some sample data and the desired output.
MotherId 1 FirstName "Mary" LastName "Thompson"
MotherId 2 FirstName "Mary" LastName "Jones"
MotherId 3 FirstName "Mary" LastName "Smith"
MotherId 4 FirstName "Red" LastName "Herring"
DaughterId 1 MotherId 1 Age 21
DaughterId 2 MotherId 3 Age 15
DaughterId 3 MotherId 4 Age 21
SonId 1 MotherId 1 Height 72
SonId 2 MotherId 1 Height 66
SonId 3 MotherId 2 Height 72
SonId 4 MotherId 2 Height 66
SonId 5 MotherId 3 Height 72
Desired Results (format is not important, just need to return these objects):
MotherId 1 "Mary Thompson" / DaughterId 1 Age 21 / SonId 1 Height 72
MotherId 2 "Mary Jones" / nulls for Daughter / SonId 3 Height 72