How to use Inner Join and Except in Linq EntityFramework 4

Question

I have two tables with a one (Parent) to many (Details) relationship.

I would like to write this query (which is written in SQL) in Linq. As you see; it is an inner join on a subquery, which contains Except:

select pa.* from dbo.Parent pa
inner join
(
      select    p.ID  from dbo.Parent p
          except
               (
                  select  d.ID from dbo.Details d  where (d.ParentID = 371)
               )
) p
on pa.ID = p.ID
where pa.ID <> 371

this is a really bad sql query, and you've asked us to make a really bad linq query... the sub query is very dubious, try to refactor it using just joins and where clauses. at that point, your linq query should make more sense. — priehl, Aug 21 '13 at 21:05

score 0 · Answer 1 · answered Aug 21 '13 at 18:48

0

var result = Parent.Where(p=>Details.Where(d=>d.ParentID!=371)
                                    .Select(d=>d.ID)
                                    .Contains(p.ID));

answered Aug 21 '13 at 18:48

King King

61,710
16
105
130

score 0 · Answer 2 · answered Aug 21 '13 at 18:56

king king is probably on the right path but here's one using a join,

parent.Join(Details , 
        parent => parent.Id,
        detail => detail.Id,
        (parent, detail) => new { P = parent, D = detail)
        .Where(e => e.D.ParentId != 371)
        .Select(e => e.P);

How to use Inner Join and Except in Linq EntityFramework 4

2 Answers2