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?

Muhammad Hasan Khan · Accepted Answer · 2010-08-03 05:05:52Z

1

                        (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()
                       });

answered Aug 3, 2010 at 5:05

Muhammad Hasan Khan

35.2k16 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jacques Over a year ago

this seems to be mixing query expressions with extension methods. Is there any way to achieve this with just query expressions?

Brian Mains · Accepted Answer · 2010-08-03 11:35:01Z

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.

answered Aug 3, 2010 at 11:35

Brian Mains

50.8k35 gold badges156 silver badges261 bronze badges

1 Comment

sipsorcery Over a year ago

Looks like L2S has some smarts around the let statement. If I use two let statements in my linq expression it still ends up with two separate SQL queries the first of which uses a left outer join to handle the let.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related