Construct nested linq-to-sql expression so as to generate a single SQL query

Question

I'm trying to construct my linq-to-sql expression so that it only generates a single SQL database query. The query involves two nested selects, a simplifed verion is:

var query = from person in People 
            where person.ID == 1234
            select new PersonDetails() 
            {
               ID = person.ID,
               FirstName = person.FirstName,
               LastName = person.LastName,
               Addresses = from address in Addresses 
                           where address.PersonID == person.ID
                           select address
               PhoneNumbers = from number in PhoneNumbers
                              where number.PersonID == person.ID
                              select number
            }

The expression will result in two SQL queries being executed. The first one joins the Person and PhoneNumbers tables and pulls the required information from them. The second query is a simple select from the Addresses table. If I comment out the phone numbers property then only a single query which joins Person and Addresses gets executed so the L2S engine does recognise both the relationships.

Does anyone have any idea how I could construct the LINQ expression so that linq-to-sql would only generate a single SQL query?

Answer 1

You can use the "let" keyword:

from person in context.Persons
.
.
let Add = from address in Addresses 
                           where address.PersonID == person.ID
                           select address

This does a subquery.

Answer 2

                        (from person in Persons
                        join address in Addresses
                        on person.id equals address.personid
                        join phone in PhoneNumbers
                        on person.id equals phone.personid
                        select new
                        {
                            Person = person,
                            Address = address,
                            Phone = phone
                        })
                       .GroupBy(p => p.Person.id)
                       .Select(g => new
                       {
                           Person = g.FirstOrDefault().Person,
                           Addresses = g.Select(k=>k.Address).Distinct(),
                           PhoneNumbers = g.Select(k=>k.Phone).Distinct()
                       });