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

In this case, where you are putting a custom predicate to the join correlation you should use a subquery in the join. See this: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=19044

You also could use LLBLGen API and CustomFilter on EntityRelations

Thanks !!!