Convert sql left join to linq2sql code without nested queries

Question

I need to format this SQL into LINQ2SQL query. I have problem with second join (left). I don't want to use nested queries so I wrote SQL which works. Thank you in advance.

select * from 
Accounts a 
inner join
Addresses ea on a.GUID = ea.UID 
left join 
Addresses ea2 on a.GUID = ea2.GUID and AddressTypeID = 2    
where 
ba.AccountID = 100 and
ea.AddressTypeID = 1

My linq2SQL code is:

var data =
                from account in dc.Accounts                    
                join primaryAddress in dc.Addresses on account.GUID equals
                    primaryAddress.GUID
                join secondaryAddress in dc.Addresses on account.GUID equals
                    secondaryAddress.GUID
                    into leftSecondaryAddress
                from secondaryAddress in
                    leftSecondaryAddress.Where(
                        x =>
                        x.AddressTypeID == 2).DefaultIfEmpty()                    
                where
                    brokerAccount.AccountID == 100 &&
                    primaryAddress.AddressTypeID == 1

What should I change?

**Bring** the linq-to-sql code you want be corrected for non-nested query result **on**. — AgentFire, Feb 11 '13 at 15:30

score 0 · Answer 1 · edited May 23 '17 at 12:27

You could improve your query if you join by multiple columns by using an anonymous type for this:

var data =
    from account in dc.Accounts                    
    join primaryAddress in dc.Addresses on account.GUID equals primaryAddress.GUID
    join secondaryAddress in dc.Addresses on new { account.GUID, AddressTypeID = 2 } equals new { secondaryAddress.GUID, secondaryAddress.AddressTypeID }
        into leftSecondaryAddress
    from secondaryAddress in leftSecondaryAddress.DefaultIfEmpty()                    
    where
        brokerAccount.AccountID == 100 &&
        primaryAddress.AddressTypeID == 1

Gert Arnold · Answer 2 · 2013-02-12T19:02:24.377

When starting from SQL (with joins) it often seems natural to use linq joins as well. But on many occasions using navigation properties is much easier and shorter. Unfortunately you don't show the class model, so I have to guess which navigation properties are present. But it could be something like this:

var data = 
   from account in dc.Accounts
   where account.AccountID == 100 // where does brokerAccount come from??
   select new {
                Account = account,
                PrimaryAddress =  account.Addresses
                                  .FirstOrDefault(a => a.AddressTypeID == 1),
                SecondaryAddress = account.Addresses
                                  .FirstOrDefault(a => a.AddressTypeID == 2)
              };

It does not look much shorter than your query, but that's because your query does not contain a select. So I hope you can use navigation properties. It helps to focus more on what data you want in stead of how to obtain them.

Convert sql left join to linq2sql code without nested queries

2 Answers2