Solved

Advanced LINQ Query

Posted on 2011-03-09
5
593 Views
Last Modified: 2013-11-11
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.

Mothers:
MotherId 1 FirstName "Mary" LastName "Thompson"
MotherId 2 FirstName "Mary" LastName "Jones"
MotherId 3 FirstName "Mary" LastName "Smith"
MotherId 4 FirstName "Red" LastName "Herring"
      
Daughters
DaughterId 1 MotherId 1 Age 21
DaughterId 2 MotherId 3 Age 15
DaughterId 3 MotherId 4 Age 21

Sons
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


0
Comment
Question by:jblindberg
  • 3
  • 2
5 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 35091841
Hi jblindberg;

The Following code snippet I believe will do what you want. Also the results you posted is missing one set of values, it should be:

  1 Mary  Thompson   1  21   1  72
  2 Mary         Jones              3  72
  3 Mary         Smith              5  72

Open in new window


DataClasses1DataContext db = new DataClasses1DataContext( );

var results = from m in db.Mothers
              where m.FirstName == "Mary"
              where m.Daughters.Any( d => d.Age == 21 ) || m.Sons.Any( s => s.Height == 72 )
              let did = m.Daughters.Where( d => d.Age == 21 ).First( ).DaughterId
              let age = m.Daughters.Where( d => d.Age == 21 ).First( ).Age
              let sid = m.Sons.Where( s => s.Height == 72 ).First( ).SonId
              let height = m.Sons.Where( s => s.Height == 72 ).First( ).Height
              select new
              {
                  MotherId = m.MotherId,
                  MotherFirstName = m.FirstName,
                  MotherLastName = m.LastName,
                  DaughterId = (did == null)? 0 : did,
                  Age = (age == null)? 0 : age,
                  SonId = (sid == null)? 0 : sid,
                  Height = (height == null)? 0 : height
              };

foreach( var r in results )
{
    string output = String.Format( "{0,3}{1,5}{2,10}{3,4}{4,4}{5,4}{6,4}", r.MotherId, r.MotherFirstName, r.MotherLastName,
        ( r.DaughterId == 0 ) ? " " : r.DaughterId.ToString( ), ( r.Age == 0 ) ? " " : r.Age.ToString( ),
        ( r.SonId == 0 ) ? " " : r.SonId.ToString( ), ( r.Height == 0 ) ? " " : r.Height.ToString( ) );
    Console.WriteLine( output );
}

Open in new window


Fernando
0
 

Author Comment

by:jblindberg
ID: 35091887
Fernando,

Thanks for the answer.  But actually I don't want the "3 Mary         Smith              5  72" in the result set.  Mother 3 has a Daughter which is the wrong Age, and no Daughter of the correct Age.  So that disqualifies Mother 3 from the desired results.

That's what I was trying to say with this part of the problem statement:
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.


And that's what makes this a tricky query to generate.

Jeff
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 35096708
Hi Jeff;

Seeming I misunderstood the question, try it like this then :

DataClasses1DataContext db = new DataClasses1DataContext( );

var results = from m in db.Mothers
              where m.FirstName == "Mary"
              where (m.Daughters.Count == 0  || m.Daughters.Any( d => d.Age == 21 )) && 
                    (m.Sons.Count == 0 || m.Sons.Any( s => s.Height == 72 ))
              let did = m.Daughters.Where( d => d.Age == 21 ).First( ).DaughterId
              let age = m.Daughters.Where( d => d.Age == 21 ).First( ).Age
              let sid = m.Sons.Where( s => s.Height == 72 ).First( ).SonId
              let height = m.Sons.Where( s => s.Height == 72 ).First( ).Height
              select new
              {
                  MotherId = m.MotherId,
                  MotherFirstName = m.FirstName,
                  MotherLastName = m.LastName,
                  DaughterId = (did == null)? 0 : did,
                  Age = (age == null)? 0 : age,
                  SonId = (sid == null)? 0 : sid,
                  Height = (height == null)? 0 : height
              };

foreach( var r in results )
{
    string output = String.Format( "{0,3}{1,5}{2,10}{3,4}{4,4}{5,4}{6,4}", r.MotherId, r.MotherFirstName, r.MotherLastName,
        ( r.DaughterId == 0 ) ? " " : r.DaughterId.ToString( ), ( r.Age == 0 ) ? " " : r.Age.ToString( ),
        ( r.SonId == 0 ) ? " " : r.SonId.ToString( ), ( r.Height == 0 ) ? " " : r.Height.ToString( ) );
    Console.WriteLine( output );
}

Open in new window


The results should then be the following:

  1 Mary  Thompson   1  21   1  72
  2 Mary     Jones           3  72

Open in new window


Fernando
0
 

Author Comment

by:jblindberg
ID: 35103061
Fernando,

Thanks, that solution worked.  I was missing the Any() methods, and the "let ... Where(...).First()" statements -- those were the key to making it work where my attempts failed.

Now I just need to partially turn this into extension method calls to fit in with my existing code (because of the way it builds the query dynamically).  I'll use LinqPad as a tool to help with this, by looking at the lambda pane to see how your query was converted into method calls.

Thanks!
Jeff
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 35103974
Not a problem Jeff; always glad to help.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now