How to return both parent and child using LINQ against 1 table

Question

Been looking for a solution for this but haven't been able to find one so far.

I'm fairly sure its possible with one linq call but having trouble working it out.

I have the following data structure

Id      ParentId     Name       ValidFlag

1       NULL         parent 1   1
2       NULL         parent 2   1
3       NULL         parent 3   0
4       1            child 1    1
5       1            child 2    1
6       2            child 3    1
7       2            child 4    1
8       3            child 5    1
9       3            child 6    1

Now what i want to do is return all valid parents and their children so this case i would return everything except Id = 3 (parent 3).

Is there a simple way to do this with LINQ? Im guessing there is but my LinqIQ is very limited, i know the basics but beyond that i need much help.

Is this a case for ToLookup() or Union or neither?

Update:

To be more specific as I've failed to do so, both types of records are in the same table, i want to return all records whether its a parent or child in the 1 query if possible.

Its not as simple as just selecting all records where ValidFlag = 1. The source database is a mess and the only way to get all records is to find the "valid" parents then find all children for the "valid" parents. I know i can just do a simple query to return all valid parent records then do a separate query to find all child of those parents, but combining into 1 LINQ query is where i fail, i was hoping that is possible. In this case it is possible that there are valid child entries of invalid parents, hence need for the question

Answer 1

Thinking about this the "wrong" way around, the SQL you want is:

SELECT * FROM MyTable
WHERE IsValid = 1 AND
 (ParentID IS NULL -- Parents
 OR ParentID IN (SELECT ID FROM MyTable WHERE IsValid = 1 AND ParentID IS NULL))
     -- Children

so the LINQ you want is:

var result = from d in MyTable
             where d.ValidFlag == 1
              && (d.ParentId == null
               || (from p in MyTable where p.ValidFlag == 1 && p.ParentId == null
                   && p.Id == d.ParentId select p.Id).Any())
             select d;

(Not quite the same SQL, but effectively so.)

Answer 2

This should do the trick, (edit: see below for version that doesn't use Distinct.)

(from parents in collection
from all in collection
where
    parents.ValidFlag == 1 &&
    parents.ParentId == null &&
    all.ValidFlag == 1 &&
    (all.ParentId == null || all.ParentId == parents.Id)
select all).Distinct();

The above code should hopefully generate something quite similar to what it itself looks like in SQL, maybe with the exception of the distinct which might cause it to return more data that is actually needed to be filtered on the client. Something that might become an issue if there's a lot data, chiefly if there's a lot of parents because it will return duplicates of those)

Here is the query reworked without the distinct call

from parents in collection // parents is only used to decide which children to get
from all in collection // this is where we will actually grab our data from
where
    parents.ValidFlag == 1 &&  // only include parents that are valid
    parents.ParentId == null && // and that are parents
    all.ValidFlag == 1 &&  // only include entries that are valid
    (
        (all.ParentId == null && all.Id == parents.Id) ||  // If entry is a parent, match with itself to limit returns to 1
        all.ParentId == parents.Id // otherwise, parentid should match one of the valid parents.
    )
select all

Answer 3

If you're using Entity Framework and have Navigation Properties, you could do the following. It's not clear from the question whether this is the case though.

var query = db.YourTable
    .Where(x => x.Parent != null && x.Parent.ValidFlag == 1)
    .GroupBy(x => x.ParentId)
    .Select(g => new { ParentId = g.Key, Children = g.ToList() })
    .ToList();

Answer 4

for your sample data, this will work :

        var validData = from d in data
                        where (!d.ParentID.HasValue && d.IsValid) //select all valid parents
                        || (d.ParentID.HasValue && data.Where(p => !p.ParentID.HasValue && p.IsValid).Select(p => p.ID).Contains(d.ParentID.Value)) //select children
                        select d;

but it won't work if there are multi-level hierarchies in your data, and you want to select the sub-children too.

another thing, i'm not sure if the above will work on linq-to-sql or another linq provider, but it does work for in-memory data.

Answer 5

I was going to use a GroupJoin, but this should satisfy your requirement.

 var query = dataContext.YourTable.Where(x => x.ValidFlag == 1 &&
 (x.ParentId == null ||
    dataContext.YourTable.Where( y => y.ParentId == x.Id)
    .First().ValidFlag == 1))
 .ToList();
                      .

Answer 6

Consider the following Linq to Sql entity:

Suppose we've named the sides of the OneToMany relationship like ChildTables and ParentTables, then the following code should do the job

//create data context
MyTableDataContext dc = new MyTableDataContext("Your connection string"); 
//find all children, i.e. the entities with ParentId set
var allChildEntities = dc.MyTable.Where(t=>t.ParentId.HasValue);
//find all valid parents, which have no parent and no children
var allParentsWithChild = dc.MyTable.Where(c => !c.ParentId.HasValue && 
                                                !c.ChildTables.Any());
//union the results
var result = allChildEntities.Union(allParentsWithChild);

If there is a foreign key relationship between Id and ParentId, then it's enough. If not, you should also probably search for child entites, with not existing parents. But this would probably be much easier done with pure sql

Answer 7

This should do it. Create a generic list of the type of your object that contains that data structure. Then use the .Where extension which returns an IEnumerable of the same type.

    List<YourObject> list = new List<YourObject>();
    IEnumerable<YourbObject> validItems = list.Where(x=>x.ValidFlag=1);