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.

you could try `a.ID ?? 0`, but I don't know if EF knows how to translate that into SQL. That said, you'd be better off not treating 0 and null the same in your DB. Convert all zeros to null or all nulls to zero (unless they really do have different meaning) so that you don't *need* to do this. — Servy, Aug 26 '13 at 15:03
lazyberezovsky thanks for reply,i also think so,is there any process or technique or any thing which give me desired output — shamim, Aug 26 '13 at 15:04
@shamim after investigation I fond that it's not possible to generate TSQL ISNULL function, but it's possible to generate ANSI SQL COALESCE function — Sergey Berezovskiy, Aug 26 '13 at 15:16

score 2 · Answer 1 · answered Aug 26 '13 at 15:08

2

this might help you

Var r=from a in DepartmentMaster
Join b in DepartmentDetail  on a.(x => (int?)a.ID) ?? 0 equals b.(y => (int?)b.ID) ?? 0
Select a.Code,b.Name

answered Aug 26 '13 at 15:08

Dhaval

2,801
20
39

score 2 · Accepted Answer · answered Aug 26 '13 at 15:10

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.

score 1 · Answer 3 · answered Aug 26 '13 at 15:14

You can use DefaultIfEmpty, It replaces an empty collection with a collection of one default value. as the default value of int is 0

var r=from a in DepartmentMaster
join b in DepartmentDetail  on a.ID.DefaultIfEmpty() equals b.ID.DefaultIfEmpty()
Select a.Code,b.Name

score 0 · Answer 4 · answered Aug 26 '13 at 15:09

0

You should be able to do something like this:

Join b in DepartmentDetail  on (a.ID == null ? 0 : a.ID) equals (b.ID == null ? 0 : b.ID)

answered Aug 26 '13 at 15:09

CaseyR

1

score 0 · Answer 5 · answered May 25 '23 at 15:14

I did some testes and found out that on .NET 6.0 with Microsoft.EntityFrameworkCore 6 when we are comparing nullable strings there is a big difference between:

var queriableResult = from valueCount in queriableValueCount
join valueIds in queriableValueIds
on valueCount.Value equals valueIds.Value
select new { ... }

that will give the following ON clause:

ON [t].[Value] = [t0].[Value]

and

var queriableResult = from valueCount in queriableValueCount
join valueIds in queriableValueIds
on new { valueCount.Value } equals new { valueIds.Value }
select new { ... }

that will give the desired ON clause:

ON ([t].[Value] = [t0].[Value]) OR (([t].[Value] IS NULL) AND ([t0].[Value] IS NULL))

Not sure if this is intended behavior or a side effect of the query generation engine. Either way, I'd always make this null check very explicit in the code. On the surface, the second join doesn't make sense and others may come and "fix" it. — Gert Arnold, May 26 '23 at 08:13

how to compare null in join linq syntax

5 Answers5

Linked