how to compare null in join linq syntax

Question

Work on EF 4 C#.Face problem in join . sql syntax

Select a.Code, b.Name from DepartmentMaster a
Join DepartmentDetail  b on isnull( a.ID,0) =isnull( b.ID,0)

Note: a.ID ,b.ID Both are nullable

Want above syntax out put in Linq syntax .Bellow syntax is not working for me

Var r=from a in DepartmentMaster
Join b in DepartmentDetail  on a.ID equals b.ID
Select a.Code,b.Name

Need help to write sql syntax isnull() comparable process in linq ef.

If have any query please ask.

Answer 1

You can use null-coalescing operator to provide default value if id is null:

from c in DepartmentMaster
join b in DepartmentDetail
    on (a.ID ?? 0) equals (b.ID ?? 0)
select new {
    a.Code, b.Name
}

That will generated query

SELECT [t0].[Code], [t1].[Name] AS [ID1]
FROM [DepartmentMaster] AS [t0]
INNER JOIN [DepartmentDetail] AS [t1] 
    ON (COALESCE([t0].[ID],@p0)) = (COALESCE([t1].[ID],@p1))

That is not exactly same as ISNULL but result should be same.