Optional condition in join clause - Linq

Question

I have below linq query

 var resultGuardian = from s in _db.Students
     join sg in _db.StudentGuardians on s.StudentID equals sg.StudentID
     join g in _db.Guardians on sg.GuardianId equals g.GuardianId
     join lr in _db.luRelationTypes on sg.RelationTypeID equals lr.RelationTypeID
     join ga in _db.GuardianAddresses on g.GuardianId equals ga.GuardianId
     join ad in _db.Addresses on ga.AddressID equals ad.AddressID
     join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID
     join lg in _db.luGenders on g.GenderID equals (int?)lg.GenderID into ssts
     from gdnr in ssts.DefaultIfEmpty()
     where
         s.TenantID == tenantid && sg.TenantID == tenantid && g.TenantID == tenantid &&
         s.StatusId == (int?)Extension.StatusType.Active //1
         && g.StatusID == (int?)Extension.StatusType.Active &&
         lr.StatusID == (int?)Extension.StatusType.Active && lr.TenantID == tenantid &&
         s.StudentID == sid
     select new
     {
         g.FirstName,
         g.LastName,
         IsPrimary = sg.IsPrimaryGuardian,
         g.Education,
         g.Email,
         g.Phone,
         lr.RelationCD,
         ga.IsStudentAddress,
         gdnr.GenderCD,
         lt.AddressName,
         ad.Address1,
         ad.Address2,
         ad.City,
         ad.State,
         ad.Zipcode

     };

In above query when ad.AddressTypeID is null, it is not returning any result.

I have requirement if ad.AddressTypeID is null,than from LuAddressTypes fetch default record where AddressTypeCd=1. If I try this way

join lt in LuAddressTypes on ad.AddressTypeID equals lt.AddressTypeID into v1
from v2 in v1.DefaultIfEmpty()
select new
     {      
         v2.AddressName,
         g.Phone,..

     });

in result v2.AddressName always returning null. I am unable to specify AddressTypeCd=1 where condition as well. AddressTypeCd=1 is not ad.AddressTypeID.

I need v2.AddressName where AddressTypeCd=1. How can I do that? Find related entities all related entities

Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Access Denied, Nov 22 '18 at 10:22
Please post the involved entity classes. Also fix the sample query - currently it's using some not existing aliases - `ga`, `g`, `lr`. — Ivan Stoev, Nov 22 '18 at 11:24
Btw, if the `AddressTypeCd` here *"I have requirement if `ad.AddressTypeID` is `null`,than from `LuAddressTypes` fetch default record where `AddressTypeCd=1`"* actually means `AddressTypeID`, then simply use `join lt in LuAddressTypes on ad.AddressTypeID ?? 1 equals lt.AddressTypeID` — Ivan Stoev, Nov 22 '18 at 11:34
@IvanStoev -Wow! It's working. You are amazing. Feel free to write as an answer. Please mention how it is taking `AddressTypeCd=1` on `LuAddressTypes` table only. Thanks a lot. — R15, Nov 22 '18 at 11:50
@IvanStoev - I notice above solution considering `ad.AddressTypeID ?? 1` to `AddressTypeID` column only not `AddressTypeCd`. It worked me coincidentally as I have matching data. You said - `AddressTypeCd=1` actually means `AddressTypeID`, No. — R15, Nov 22 '18 at 12:22
You want a conditional join, @AccessDenied already gave you a big hint : go with outer join :) — Florian, Nov 22 '18 at 12:44
@Florian - As for as I know in `linq to entities` there is no outer join. — R15, Nov 22 '18 at 12:45
@CGPA6.4 Linq will create a SQL request at the end, even if you can't write "outer join" with linq syntax, the easy way is to use `.DefaultIfEmpty()`, by doing so Linq will produce an outer join. Do you need help to write it ? — Florian, Nov 22 '18 at 12:48
If you check my question properly :) I have done that, but not getting expected output. — R15, Nov 22 '18 at 12:50

score 4 · Accepted Answer · answered Nov 22 '18 at 13:08

You can't use the standard LINQ join, but in LINQ to Entities you could use the alternative join syntax based on correlated Where - EF is smart enough to translate it to JOIN.

In your case, instead of

join lt in _db.luAddressTypes on ad.AddressTypeID equals lt.AddressTypeID

you could use

from lt in _db.luAddressTypes.Where(lt => ad.AddressTypeID == lt.AddressTypeID
    || (ad.AddressTypeID == null && lt.AddressTypeCd == 1))

which is translated to something like this

INNER JOIN [dbo].[LuAddressTypes] AS [Extent3]
    ON ([Extent2].[AddressTypeID] = [Extent3].[AddressTypeID])
        OR (([Extent2].[AddressTypeID] IS NULL) AND (1 = [Extent3].[AddressTypeCd]))

If you want LEFT OUTER JOIN, simply add .DefaultIfEmpty() at the end of the above line.

Thank you for your answer. It is returning multiple records. Can you please suggest me how to avoid this. — R15, Nov 22 '18 at 13:13
Add `.Take(1)` :) It would change the `JOIN` to `CROSS APPLY`, but the effect should be the same. — Ivan Stoev, Nov 22 '18 at 13:31

Optional condition in join clause - Linq

1 Answers1