Subquery with joins in LINQ

Question

I am new to LINQ. I am confusing to query it. Please any one tell me how to convert the below query in LINQ

select * from tbldev 
where iddevice not in(select a.iddevice from 
tblUDMap a join tbldev d 
on a.iddevice=d.iddevice )

http://stackoverflow.com/questions/183791/how-would-you-do-a-not-in-query-with-linq — Lasse Edsvik, Sep 24 '13 at 13:15

Alireza · Answer 1 · 2013-09-25T07:19:13.630

A literal and naive word to word translation would be something like this:

    var result = from dev in context.tbldev
                 where (from udmap in context.tblUDMap join dev2 in context.tbldev on udmap.iddevice equals dev2.iddevice select udmap.iddevice)
                       .Contains(dev.iddevice) == false
                 select dev.iddevice;

But your question is strange! You jon the two tables and then filterout those rows in tbldev that were successfully participated in join! It means you want those rows in tbldev which cannot be joint to tblUDMap. So it is more simple to write this:

var result = from dev in context.tbldev 
             where (from udmap in context.tblUDMap 
                    where udmap.iddevice == dev.iddevice
                    ).Count() == 0
             select dev.iddevice;

@Kalyan Sorry! Corrected the mistake. – Alireza Sep 25 '13 at 07:19 — Alireza, Sep 25 '13 at 07:19

score 2 · Answer 2 · answered Sep 24 '13 at 14:00

2

There's no need for nested queries here:

from x in tbldev
join y in tblUDMap
    on x.iddevice equals y.iddevice
    into grp
where !grp.Any()
select x

This will select all records from tbldev for which there are no corresponding records in tblUDMap.

answered Sep 24 '13 at 14:00

Thom Smith

13,916
6
45
91

score 2 · Answer 3 · answered Sep 24 '13 at 14:32

2

You could simplify your query to something like this

var query = tbldev.Where(e => !tblUDMap.Any(a => a.iddevice == e.iddevice))

answered Sep 24 '13 at 14:32

Esteban Elverdin

3,552
1
17
21

score 1 · Answer 4 · answered Sep 24 '13 at 13:19

try this:

from res in tbldev
where !(from a in tblUDMap
        join b in tbldev on a.Iddevice equals b.iddvice
        into c
        select c)
select res

The important part is the negated where-clause. the join syntax in this might not be correct, from the top of my head.

Subquery with joins in LINQ

4 Answers4