CODEWITHSUNDEEP
c#sqllinqfluent
aasukisuki
aasukisuki

Reputation: 1243

Fluent LINQ - Select a list of parents that contains a list of children where a subset of children are present

That title is the worst...

Anyway, what I'm trying to do is select a parent object, who contains n number of child objects. I will be passing in a list of criteria (1..n) that the child objects have to match. For the sake of brevity, here's the classes i'm working with:

public class Parent {

     public int Id { get; set; }

     public List<Child> Children { get; set; }

}

public class Child { 

    public int Id { get; set; }

    public int ParentId { get; set; }

    public int SomeValue { get; set; }

}

What I'm looking for is a list of parents that contain children that match all of the SomeValues i'm passing in

So if I have:

Parent 1
    Child 1, SomeValue 10
    Child 2, SomeValue 20
    Child 3, SomeValue 40
Parent 2
    Child 4, SomeValue 10
    Child 5, SomeValue 20
    Child 5, SomeValue 50

and myList is [10, 50], it needs to return Parent 2 only. If myList is [10, 20] then both parents should be returned. And finally, if myList is [10, 20, 60], nothing should be returned.

I don't think the following will work, because the values that children has that aren't in the list will remove it from the results (right?)

parents.where(p => p.children.all(c => myList.contains(c.SomeValue)));

and an any won't work, because it will just return anything as long as one of its children matches. I need to ensure that the parent has a child that matches every item in myList. I also thought about adding a count to make sure the matched items were at least as large as the myList.length, but that might not work because SomeValue doesn't have to be distinct in the collection of children (I guess i could run a distinct subselect on the children's SomeValues?)

Upvotes: 4

Views: 5789

Answers (2)

Daloupe
Daloupe

Reputation: 167

Here's some rough code that outputs:

10,20
1
2
10,50
2

var parents = new List<Parent>
            {
                new Parent
                {
                    Id = 1,
                    Children =
                        new List<Child>
                        {
                            new Child {SomeValue = 10},
                            new Child {SomeValue = 20},
                            new Child {SomeValue = 40}
                        }
                },
                new Parent
                {
                    Id = 2,
                    Children =
                        new List<Child>
                        {
                            new Child {SomeValue = 10},
                            new Child {SomeValue = 20},
                            new Child {SomeValue = 50}
                        }
                }
            };

            var val1 = 10;
            var val2 = 20;
            var query = from a in parents
                from b in a.Children
                where b.SomeValue == val1
                select a;

            var query2 = from a in parents
                        from b in a.Children
                        where b.SomeValue == val2
                        select a;

            Console.WriteLine("10,20");
            foreach(var parent in query.ToList().Intersect(query2.ToList()))
                Console.WriteLine(parent.Id);

            val1 = 10;
            val2 = 50;

            Console.WriteLine("10,50");
            foreach (var parent in query.ToList().Intersect(query2.ToList()))
                Console.WriteLine(parent.Id);

Upvotes: 0

Reed Copsey
Reed Copsey

Reputation: 564641

You can flip your condition, and check to make sure all of the list values are contained within the Children collection:

var matches = parents.Where(p => myList.All(v => p.Children.Select(c => c.SomeValue).Contains(v)));

Upvotes: 10

Related Questions