CODEWITHSUNDEEP
c#linqentity-frameworkfiltering
Ryan Betker - healthNCode
Ryan Betker - healthNCode

Reputation: 975

Filtering IQueryable sub list

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; }
}

Upvotes: 5

Views: 18230

Answers (4)

Steven Wexler
Steven Wexler

Reputation: 17329

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;
        }
    );

Upvotes: 4

Yusuf Uzun
Yusuf Uzun

Reputation: 1511

I think what you are looking is SelectMany. As an example for your case is something like this:

  positiveItems = items.SelectMany(x => x.SubItem).Where(x=> x.ID == 1).Select(x=>x.Item);
  //items still IQueryable , so we can concat it with another IQueryable

  negativeItems = items.SelectMany(x=>x.SubItem).Where(x=>x.ID != 1).Select(x=>x.Item);


  //just an using option
  allItems = positiveItems.Concat(negativeItems);

And just a suggestion. For high number of reference object set, you can use ValueInjecter It is very simple and fast. I used it couple of production projects and it saved my tons of times.

Upvotes: 0

Garrison Neely
Garrison Neely

Reputation: 3289

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

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727137

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.

Upvotes: 2

Related Questions