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?)?

Possible duplicate of LEFT OUTER JOIN in LINQ

Ahmad Ibrahim
– Ahmad Ibrahim

2017-01-02 22:10:28 +00:00
Commented Jan 2, 2017 at 22:10 — Ahmad Ibrahim
– Ahmad Ibrahim, Commented Jan 2, 2017 at 22:10

CodingYoshi · Accepted Answer · 2017-01-03 12:33:36Z

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();

edited Jan 3, 2017 at 12:33

answered Jan 2, 2017 at 22:19

CodingYoshi

27.3k7 gold badges68 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Vinyl Over a year ago

(c.IdAttivazione.ToString() == null) => (ucc.IdAttivazione.ToString() == null)

CodingYoshi Over a year ago

@MikeVinyl Sorry I do not understand? Are you asking me a question?

marc_s · Accepted Answer · 2017-02-12 19:41:50Z

0

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.

edited Feb 12, 2017 at 19:41

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 2, 2017 at 22:16

Saqib Rokadia

6497 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related