Linq C# : Integer dbnull value how can I check it?

Question

I must convert into linq this query sql:

SELECT DISTINCT COUNT(tab1.IdUtente) AS NumClientiSenzaAccessi 
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.IdUtente = tab2.IdAttivazione 
WHERE (tab1.Demo = 0) AND (tab1.idRivenditore = 0) AND (tab1.IdGruppo <> 29) AND (tab1.IdGruppo <> 130) AND (tab1.IdGruppo <> 117) AND (tab2.IdAttivazione IS NULL)

I've tried to do this query:

var numClienti = (from u in contestoDB.tab1 
join c in contestoDB.tab2 on u.IdUtente equals c.IdAttivazione 
where u.Demo == demo && u.idRivenditore == 0 && u.IdGruppo != 29 && u.IdGruppo != 130 && u.IdGruppo != 117 && (c.IdAttivazione.ToString() == null) 
select u.IdUtente 
).Count();

But it always returns 0 and not other value!!! How can I compare with DBNull value of c.IdAttivazione and its type is nullable integer (int?)?

Answer 1

You need to use into on your join and then select from that and use the DefaultIfEmpty() so it can do a left join and keep all records from the left which did not join to the right.

var numClienti = (from u in contestoDB.tab1 
join c in contestoDB.tab2 on u.IdUtente equals c.IdAttivazione into uc
from ucc in uc.DefaultIfEmpty()
where u.Demo == demo && u.idRivenditore == 0 && u.IdGruppo != 29 && u.IdGruppo != 130 && u.IdGruppo != 117 && (ucc.IdAttivazione.ToString() == null) 
select u.IdUtente 
).Distinct().Count();

Answer 2

You should be able to either directly compare to null if IdAttiviazone is a Nullable<Int32> or use the property HasValue.

&& c.IdAttivaZone == null

or

&& c.IdAttivaZone.HasValue

Depends on how well your linq to SQL query provider works.