Join and where not exist in Linq c#

Question

How I can convert this to linq c#,

select * from boliviaextremocontabilidad.subrecurso as sr where not exists 
(select * from boliviaextremocontabilidad.recursocliente as rc 
join  boliviaextremocontabilidad.eventocliente as ec on rc.idVenta=ec.idVenta
 where rc.idSubrecurso=sr.idSubrecurso and ec.idEvento = 9 );

only : select * from boliviaextremocontabilidad.subrecurso

what i want is this:

select * from boliviaextremocontabilidad.subrecurso as sr where not exists 
    (select * from boliviaextremocontabilidad.recursocliente as rc 
    join  boliviaextremocontabilidad.eventocliente as ec on rc.idVenta=ec.idVenta
     where rc.idSubrecurso=sr.idSubrecurso and ec.idEvento = 9 );

I am trying with everything but i dont figure, how to combine "join" with "where not exit"

these are my trying code in linq but anyone work:

Attemp 1:

(from sr in db.subrecurso 
join rc in db.recursocliente on sr.idSubrecurso equals rc.idSubrecurso
join ec in db.eventocliente on rc.idVenta equals ec.idVenta
where ec.idEvento == idEvento && !db.recursocliente.Any(m => m.idSubrecurso == sr.idSubrecurso)
select sr).ToList();

Attemp 2:

List<subrecurso> srall=(from sr in db.subrecurso
                                             where !db.recursocliente.Any(m => (m.idSubrecurso == sr.idSubrecurso))
                                             select sr).ToList();
                List<subrecurso> srcs = (from sr in  srall
                                        join rc in db.recursocliente on sr.idSubrecurso equals rc.idSubrecurso
                                        join ec in db.eventocliente on rc.idVenta equals ec.idVenta
                                        where ec.idEvento == idEvento
                                        select sr).ToList();

Attemp 3:

List<subrecurso> srcs = (List<subrecurso>)(from sr in db.subrecurso
                                                       where !db.recursocliente.Any(m => (m.idSubrecurso == sr.idSubrecurso))
                                                       select sr)
                                     .Join(db.recursocliente.Join(db.eventocliente.Where(ec => ec.idEvento == idEvento), rc => rc.idVenta, ec => ec.idVenta, (ec, rc) => new { ec, rc })
                                     , sr => sr.idSubrecurso, rc => rc.ec.idSubrecurso, (sr, rc) => new List<subrecurso> { sr });

Thanks a lot!!

are you sure? try refer here http://stackoverflow.com/questions/6359980/proper-linq-where-clauses — Kasnady, Dec 21 '16 at 08:05
The problem is combine "where not exist" with "join" i dont figure how to do in linq :/ — Roberto Massimo Patara Brianco, Dec 21 '16 at 08:09
Possible duplicate of [How to SELECT WHERE NOT EXIST using LINQ?](http://stackoverflow.com/questions/9031008/how-to-select-where-not-exist-using-linq) — Mikhail Tulubaev, Dec 21 '16 at 08:16
is similar but not duplicate here is more complex for the joins — Roberto Massimo Patara Brianco, Dec 21 '16 at 08:21
If you need to JOIN in LINQ, there is a bug with your entities and mappings. You should define relations in the entities themselves, not joins in the queries. `Shift` should have a collection called `Employees` — Panagiotis Kanavos, Dec 21 '16 at 16:37
There is not error in the entities and mapping, i dont figure how to add the joing with not exist, only i want to repodruce mysql code, i udpate the question — Roberto Massimo Patara Brianco, Dec 21 '16 at 18:42

Join and where not exist in Linq c#

0 Answers0