troubleshooting Question

Advanced LINQ Query

Avatar of jblindberg
jblindberg asked on
.NET ProgrammingC#
5 Comments1 Solution626 ViewsLast Modified:
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

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros