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.