none
How to include null values in join? RRS feed

  • Question

  • Sorry if this is a bit messy.

    I have a records in my Employee table which have a null city value. I need these to be included. At the moment I am only getting Employees without a null city value. I have added a null row into my City table but nulls are not comparable so this does not solve the problem. MYSQL Datebase btw.


    var empDets = from emp in db.Employee
                                  where emp.surname == lastname
                                  join c in db.City on emp.city equals c.id
                                  select new { name = emp.given_names + " " + emp.surname, position = emp.rank, date = emp.date_appointed, cityname = c.name, cityslogan = c.slogan };

    Thursday, September 24, 2015 1:03 AM

Answers

  • Hi,

    Try to use left outer join that would include the null reference value. So the following is the equivalent code for left outer join in linq:

    var empDets = from emp in db.Employee
                                  where emp.surname == lastname
                                  join c in db.City on emp.city equals c.id into x
                                  from c in x.DefaultIfEmpty()
                                  select new { name = emp.given_names + " " + emp.surname, 
                                      position = emp.rank, date = emp.date_appointed, cityname = c.name, cityslogan = c.slogan };
    Hope it helps.

    • Marked as answer by tvuj123454 Thursday, September 24, 2015 2:06 AM
    Thursday, September 24, 2015 1:51 AM