Filtering IQueryable sub list

Question

Working with Entity Framework, but that's probably irrelevant If I have an Iqueryable, how do I filter a sub list and keep it IQueryable so it doesn't yet hit the DB?

If I have 10 items and each has 3 sub items, how do I filter it so there's a return of all 10 items and their subitems are filtered to where id = 1?

class Item has about 20 properties on it, so I wouldn't want to use projection on each of them, because of maintenance concerns..

items = items.select(??);//how to do this so items are returned, and their children are filtered?

class SubItem
{ private int ID { get; set; }
}
class Item 
{
private List<SubItem> SubItems { get; set; }
}

Solution 1

I interpret your question as you want to return all Items no matter what, but you want to filter SubItems. There is no good way to say "I want to return this object except I want a modified version of X property" for an IQueryable. You'll have to use a select statement where you select a new object if you want to this.

Option 1: Return the data separately

var itemsAndSubItems = items
    .Select(item => new 
        {
            Item = item,
            SubItems = item.SubItems.Where(sub => sub.ID = 1)
        }
    );

or if you don't mind eagerly loading the items into memory:

IEnumerable<Item> = items
    .Select(item => new 
        {
            Item = item,
            SubItems = item.SubItems.Where(sub => sub.ID = 1)
        }
    )
    .ToList()
    .Select(row => 
        { 
            var item = row.Item;
            item.SubItems = row.SubItems;
            return item;
        }
    );

Option 2: Return a new instance of your class (which it seems you don't want to do)

IQueryable<Item> items = items
    .Select(item => new Item 
        { 
            SubItems = item.SubItems.Where(sub => sub.ID == 1),
            OtherProp = item.OtherProp
            /*etc for the other properties on Item*/
        }
    );

Option 3: Add another property to your class. I recommend this least. Note that your query will still return all sub items here when you access SubItemsWithIdOne

class Item 
{
    private List<SubItem> SubItems { get; set; }
    private List<SubItem> SubItemsWithIdOne 
    {
        get 
        {
            return this.SubItems.Where(sub => sub.ID == 1); 
        }
    }
}

Option 4: Add a property on SubItem that references it's parent Item. Then return a list of SubItem. This way you'll have both SubItems and Items where your criteria is satisfied.

...If you're working with an IEnumerable you can do:

IEnumerable items = items
    .Select(item =>
        {
            item.SubItems.Where(sub => sub.ID = 1);
            return item;
        }
    );

Solution 2

If you want to filter children down to where there's only one child per parent, you need to start with children, select their parents, and do not touch the parents' subitems:

IQueryable<SubItem> childItems = context
    .SubItems.Include("Item")
    .Where(si => si.Id == 1 && si.Item.SomeAttr == someValue);
//               ^^^^^^^^^^    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
//                   |                         |
//                   |           Set a condition on the parent
//  Set a condition on the child

I assume that each sub-item has a link "pointing" back at its parent.

Solution 3

items.Where(i => i.SubItems.Any(subItem => subItem.Id == 1));