select * from user_user_connection
where userid_from = 3464 and
userid_to not in
(select userid_from from
user_user_connection
where userid_to = 3464);
Hi helpies I am new to entity framework. I am trying to convert this query into LINQ query not able to understand how can I write the subquery with not in clause. Also which one should be better join or Include?
This
from a in user_user_connection
join b in user_user_connection on
new {From=a.userid_to, To=a.userid_from} equals new {From=b.userid_from, To=b.userid_to} into c
from d in c.DefaultIfEmpty()
where d == null
select a;
is similar to
select a.*
from user_user_connection a
left join user_user_connection b on a.userid_to = b.userid_from and a.userid_from = b.userid_to
where b.userid_from is null
which should match your not in query.
if you want a specific userid_from you can add another where
from a in user_user_connection
where a.userid_from == 3464
join b in user_user_connection on
new {From=a.userid_to, To=a.userid_from} equals new {From=b.userid_from, To=b.userid_to} into c
from d in c.DefaultIfEmpty()
where d == null
select a;
Somthing like this maybe:
var list = userConnection.Where(user => user.useridto == 3464).Select(user => user.userid_from).ToList();
var list2 = userConnection.Where(user => !list.Contains(user.userid_to));
Probably can be improved since Contains take quite some time (maybe put the list in a hashset?)
This should work:
var query =
from uc in ctx.user_user_connection
where uc.userid_from = 3464 &&
!(from uc2 in ctx.user_user_connection
where uc2.userid_to = 3464
select uc2.userid_from).Contains(uc.userid_to)
select uc;
Or via EXISTS
var query =
from uc in ctx.user_user_connection
where uc.userid_from = 3464 &&
!(from uc2 in ctx.user_user_connection
where uc2.userid_to = 3464 && uc.userid_to == uc2.userid_from
select uc2).Any()
select uc;
Ov via LEFT JOIN
var query =
from uc in ctx.user_user_connection
from uc2 in ctx.user_user_connection
.Where(uc2 => uc2.userid_from == 3464 && uc.userid_to == uc2.userid_from)
.DefaultIfEmpty()
where uc.userid_from = 3464 && uc2 == null
select uc;
