c# - Sql Query with not in clause of subquery to LINQ query

Question

   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?

question from:https://stackoverflow.com/questions/65890430/sql-query-with-not-in-clause-of-subquery-to-linq-query

Answer 1

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;