i have got two table
- usermaster
- AreaMaster
my sql query is like this:
select * from tblArea where areaid
not in (select areaid from
tblUserMaster)
please tell me how to write such nested query in linq
Add a comment
|
3 Answers
var result = tblArea.Where(x => !tblUserMaster.Any(m => m.areaid == x));
3 Comments
Saeed Amiri
It doesn't work in linq to sql, just primitive types can be done
Dr. Rajesh Rolen
i have checked and its not working. its generating exception when i try to use foreach on it: Could not format node 'New' for execution as SQL.
Dr. Rajesh Rolen
thanks! its work when i have written: var result = tblArea.Where(x => !tblUserMaster.Any(m => m.areaid == x.areaid));
I can't find any good way you can do something like:
var tb1 = tblUserMaster.ToList();
var result = tblArea.AsEnumerable().Where(x => !tb1.Any(m => m.areaid == x));
but it's not a good way, It's better to write store procedure instead loading all data to client. may be someone can do this better but I think it can not be improved.
AsEnumerable() keyword moves from linq->sql to pure linq but it loads all data.
2 Comments
Saeed Amiri
@Rajesh Rolen- DotNet Developer, yes with tblUserMaster.ToList().Any(m => m.areaid == x) works
Dr. Rajesh Rolen
Now its working when i have written: var tb1 = tblUserMaster.ToList(); var result = tblArea.AsEnumerable().Where(x => !tb1.Any(m => m.areaid == x.areaid));
try this one
var result=from tblarea in db.TblArea
where
!
(from tblusermaster in db.TblUserMaster
select new {
tblusermaster.Areaid
}).Contains(new { tblarea.Areaid })
select new {
tblarea.Areaid,
tblarea.Column1,
tblarea.Column2
}
1 Comment
Raymund
What error are you getting, I tried a working version of this one in my machine and it worked fine.