.net mvc c#

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