LINQ LEFT JOIN not working on NULL values

Question

I have two tables Student and Marks.

Student table have the following fields: StudentID,Name,MarkID(Nullable). Marks table have the following fields: MarkID,Mark

Student table

StudentID   Name    MarkID

1           Mark    1 

2           Mike    NULL

3           John    NULL

4           Paul    2

Mark table

MarkID  Mark

1       80

2       100

If I use the left join then i getting only mark and paul records. I want all the records in the left table(Student) My Query is:

   var query = (from s in Students  
               join m in Marks on s.MarkID equals m.MarkID 
               into mar from subMark in mar.DefaultIfEmpty()
               where(m.Mark > 80)
               Select s.Name)
               .ToList() 

Note: It is an Example only. While joining two tables using left join and applying where condition on the second table ,If joined column value is null in first table,it won't bring the record from first table.

Answer 1

NULL comparisons are always false. That's the way SQL's three-valued logic works. If you want to match rows where the values are both null you should use a statement that checks both of them for null.

In a SQL statement you would write:

ON S.MARKID=M.MARKID OR (S.MARKID IS NULL AND M.MARKID IS NULL)

In C# you can use the comparison operator and your LINQ provider will convert this to IS NULL, eg:

on s.MarkID == m.MarkID || (s.MarkID == null && m.MarkID==null)

Answer 2

The problem is we use the where clause in Left join.So it will discard the null value records.

var sampleQuery= (from f in food 
            join j in juice on f.ID equals j.ID into juiceDetails from juice in juiceDetails.DefaultIfEmpty()
            where(!j.deleted)
            join fr in fruit on f.ID equals fr.ID into fruitDetails from fruit in fruitDetails.DefaultIfEmpty()
            where(!fr.deleted)
            select new
            {
            // codes

            });

Instead of this we have to check the where clause in table itself.Like this

var sampleQuery= (from f in food 
            join j in juice.Table().where(x=>!x.deleted) on f.ID equals j.ID into juiceDetails from juice in juiceDetails.DefaultIfEmpty()              
            join fr in fruit.Table().where(x=>!x.deleted) on f.ID equals fr.ID into fruitDetails from fruit in fruitDetails.DefaultIfEmpty()                
            select new
            {
            // codes

            });

It will work fine. Thank you.

Answer 3

/EDIT: My first answer was using a FULL OUTER JOIN. this was way over the top and probably wrong or not compleltly correct.

The new answer uses a LEFT OUTER JOIN. I have created some sample data using LinqPad to get a working example. Ignore the .Dump() method if you are not using LinqPad.

var Students = new List<Student>() {
        new Student() {StudentId = 1, Name ="John", MarkId = 1},
        new Student() {StudentId = 1, Name ="Paul", MarkId = 1},
        new Student() {StudentId = 1, Name ="Steve", MarkId = 1},
        new Student() {StudentId = 1, Name ="John", MarkId = 2},
        new Student() {StudentId = 1, Name ="Paul", MarkId = 3},
        new Student() {StudentId = 1, Name ="Steve", MarkId = 1},
        new Student() {StudentId = 1, Name ="Paul", MarkId = 3},
        new Student() {StudentId = 1, Name ="John"  },
        new Student() {StudentId = 1, Name ="Steve"  },
        new Student() {StudentId = 1, Name ="John", MarkId = 1}
        
    };
    
    var Marks = new List<Mark>() {
        new Mark() {MarkId = 1, Value = 60},
        new Mark() {MarkId = 2, Value = 80},
        new Mark() {MarkId = 3, Value = 100}
    };
    
    var StudentMarks = Students
                        .GroupJoin(
                            Marks,
                            st => st.MarkId,
                            mk => mk.MarkId,
                            (x,y) => new {
                                            StudentId = x.StudentId, 
                                            Name = x.Name,
                                            Mark = y.Select (z => z.Value).SingleOrDefault()
                                          }
                        )
                        .Dump();

    
}

public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }
    public int MarkId { get; set; }
}

public class Mark
{
    public int MarkId { get; set; }
    public int Value { get; set; }
}

Output:

As you cann see in my Students list, there a 2 students without a MarkId. Those 2 get the default value assigned due to .SingleOrDefault(). I think this will solve your problem and gives you a good basis for further fiddeling.

references: How do you perform a left outer join using linq extension methods

Answer 4

In your query you have written From in your Join statement while joining it. Instead you should use in::

   from s in Students  
    join m in Marks on s.MarkID equals m.ID into mar
    from subMark in mar.DefaultIfEmpty()
    Select s.Name).ToList() 

Answer 5

I had the same problem. This solution only works if you have at least one row in subMark. The rows' ID doesn't matter.

var query = (from s in Students  
               join m in Marks on s.MarkID equals m.MarkID into fullM
               into mar from subMark in mar.DefaultIfEmpty()
               where(m.Mark > 80)
               Select s.Name)
               .ToList() 

the keyword into does the magic. Adding it shows all rows, also those, which have NULL-Values in mar.