LINQ query help needed for Intersect

Question

Hi, LINQ gurus, I am looking for help to write a query...

I have a table with Person records, and it has a nullable ParentID column, so it is kind of self-referencing, where each record might have a Parent.

I am looking for unprocessed rows whose parent rows were processed. This SQL works fine:

SELECT *
  FROM Person
  where IsProcessed = 0 and
  ParentId in
  (
  select Id from Person 
  where IsProcessed = 1
  )

I tried a number of LINQ queries, but they failed. Now, I'm trying:

    var qParent = 
                from parent in db.Person
                where 
                parent.IsProcessed == true
                select parent.ID;

    var qChildren = from child in db.Person
                    where
                    child.IsProcessed == false
                    && child.ParentId.HasValue
                    select child.ParentId.Value;

    var q2 = qChildren.Intersect(qParent);

This yields SQL with a DISTINCT clause, for some reason, and I am baffled why DISTINCT is generated.

My main question is how to write LINQ for the SQL statement above?

Thanks in advance.

Answer 1

Intersect is a set operation - it is meant to return a set of distinct elements from the intersection. It seems reasonable to me that it would use DISTINCT in the SQL. There could be multiple children with the same parent, for example - Intersect should only return that ID once.

Is there any reason you don't want to use a join here?

var query = from parent in db.Person
            where parent.IsProcessed
            join child in db.Person.Where(child => !child.IsProcessed)
               on parent.ID equals child.ParentId.Value
            select child;

Answer 2

The query can be translated literally into :

var parentIds = db.Person.Where(x => x.IsProcessed)
                      .Select(x => x.Id)
                      .ToList();
var result = db.Person.Where(x => !x.IsProcessed && parentIds.Contains(x => x.Id))
                   .ToList();