Linq return parent objects that have child items matching ALL items in separate list

c# entity-framework-6 linq

Question

I have an object which has a variable length list of items (incomingList in code example) and a list of people which each have a list of items. I want to return only those people that have all the items in the incomingList.

So looking at the example, I only want person 1 and 3 returned.

The people are in a database and I want to retrieve as little data as possible so I am trying to work out what the linq query needs to be to achieve this? If I knew the length of the incomingList was always going to be the same I could do "...Any(..) && ...Any(...) &&" etc - but the length will vary.

void Main()
{
    var incomingList = new IncomingItem();

    var matchItem1 = new MatchItem { ItemType = "objectId", ItemValue = "60" };
    var matchItem2 = new MatchItem { ItemType = "area", ItemValue = "CU" };

    incomingList.MatchList = new List<MatchItem>();
    incomingList.MatchList.Add(matchItem1);
    incomingList.MatchList.Add(matchItem2);

    var people = new List<Person>();

    var person1 = new Person { Id = 1 };
    person1.ListOfItems = new List<Item>();
    person1.ListOfItems.Add(new Item { ItemType = "objectId", ItemValue = "60" });
    person1.ListOfItems.Add(new Item { ItemType = "objectId", ItemValue = "1" });
    person1.ListOfItems.Add(new Item { ItemType = "objectId", ItemValue = "30" });
    person1.ListOfItems.Add(new Item { ItemType = "area", ItemValue = "CO" });
    person1.ListOfItems.Add(new Item { ItemType = "area", ItemValue = "CU" });
    people.Add(person1);

    var person2 = new Person { Id = 2 };
    person2.ListOfItems = new List<Item>();
    person2.ListOfItems.Add(new Item { ItemType = "objectId", ItemValue = "60" });
    people.Add(person2);

    var person3 = new Person { Id = 3 };
    person3.ListOfItems = new List<Item>();
    person3.ListOfItems.Add(new Item { ItemType = "objectId", ItemValue = "60" });
    person3.ListOfItems.Add(new Item { ItemType = "area", ItemValue = "CU" });
    people.Add(person3);

    var person4 = new Person { Id = 4 };
    person4.ListOfItems = new List<Item>();
    person4.ListOfItems.Add(new Item { ItemType = "objectId", ItemValue = "12" });
    people.Add(person4);
}

public class IncomingItem
{   
    public IList<MatchItem> MatchList { get; set; }
}

public class MatchItem
{
    public List<object> SomeMoreInformation { get; set; }

    public string ItemType { get; set; }

    public string ItemValue { get; set; }
}

public class Person
{
    public int Id { get; set; }

    public IList<Item> ListOfItems { get; set; }
}

public class Item
{
    public int Id { get; set; }

    public int PersonId { get; set; }

    public string ItemType { get; set; }

    public string ItemValue { get; set; }
}
1
5
6/9/2016 1:03:57 PM

Popular Answer

This returns all people that have all items of incomingList in their ListOfItems:

var result = people.Where(p => incomingList.MatchList
                       .All(l => p.ListOfItems.Select(loi => new { loi.ItemType, loi.ItemValue })
                           .Contains(new { l.ItemType, l.ItemValue }) ));

Anonymous types should have properties with equal names and types to resolve to "equal", which condition is met in this case.

2
6/9/2016 1:53:37 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow