I would like to know how to write something like this in Linq :
SELECT third_party.*
FROM third_party
LEFT JOIN third_party_address ON (third_party.Id = third_party_address.FK_third_party_Id AND third_party_address.Number == 1)
In fact, I have 2 tables corresponding to third parties and addresses of the third parties.
I would like to retrieve a list of the third parties with their MAIN address (the one with the Number = 1).
I try to write it like this :
var query =
from thirdParty in metaData.ThirdParty
join thirdPartyAddress in metaData.ThirdPartyAddress on thirdParty.Id equals thirdPartyAddress.FkThirdPartyId into leftJoinedThirdPartyAddresses
from leftJoinedThirdPartyAddress in leftJoinedThirdPartyAddresses.DefaultIfEmpty()
where leftJoinedThirdPartyAddress == null || leftJoinedThirdPartyAddress.Number == 1
select new ThirdPartyProjection
{
Id = thirdParty.Id,
Name = thirdParty.Name,
MainThirdPartyAddressId =
leftJoinedThirdPartyAddress.Id,
MainThirdPartyAddressTown = leftJoinedThirdPartyAddress.Town
};
but this results in the following SQL
SELECT third_party.*
FROM third_party
LEFT JOIN third_party_address ON third_party.Id = third_party_address.FK_third_party_Id
WHERE third_party_address.Id is null third_party_address.Number == 1
which is different because it doesn't return any third party if he has an address with Number = 2.
Thanks in advance