Linq Where Inside Include

Question

I have a table of parents (InventoryItem) and another table of children (InventoryItemLines) and each parent can have an undetermined number of children

I need to get all the parents and for each parent, I need to get the list of children that respect a specific condition.

Example: I have an "Inactive" bit column in the children tables and i use this statement to get the data: "_repository is a IRepository < InventoryItem >

var entities = _repository.GetAsQueryable().Include(x => x.InventoryItemLines.Where(i => i.Inactive));

but i get an ArgumentException with the message:" The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path"

Can you please show me a good practice for this kind of situation ?

Basically you can't do it, there is no way to conditionally include related entities using EF. — Ben Robinson, Sep 07 '15 at 13:15
You can't do that , you gotta use projection though to achieve this — Coder1409, Sep 07 '15 at 13:24

score 0 · Answer 1 · answered Sep 07 '15 at 13:39

The code will look something like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
       
        static void Main(string[] args)
        {
            DataTable InventoryItem = new DataTable();

            InventoryItem.Columns.Add("Item", typeof(string));

            InventoryItem.Rows.Add(new object[] { "Shoes" });
            InventoryItem.Rows.Add(new object[] { "Ties" });
            InventoryItem.Rows.Add(new object[] { "Dresses" });

            DataTable InventoryItemLines = new DataTable();
            InventoryItemLines.Columns.Add("Item", typeof(string));
            InventoryItemLines.Columns.Add("Color", typeof(string));
            InventoryItemLines.Columns.Add("Active", typeof(Boolean));

            InventoryItemLines.Rows.Add(new object[] { "Shoes", "Red", true });
            InventoryItemLines.Rows.Add(new object[] { "Shoes", "Blue", true });
            InventoryItemLines.Rows.Add(new object[] { "Shoes", "Turquoise", false });
            InventoryItemLines.Rows.Add(new object[] { "Ties", "Red", true });
            InventoryItemLines.Rows.Add(new object[] { "Ties", "Stripped", true });
            InventoryItemLines.Rows.Add(new object[] { "Ties", "Pokerdot", false });
            InventoryItemLines.Rows.Add(new object[] { "Dresses", "Yellow", true });
            InventoryItemLines.Rows.Add(new object[] { "Dresses", "Blue", true });
            InventoryItemLines.Rows.Add(new object[] { "Dresses", "Violet", true });
            InventoryItemLines.Rows.Add(new object[] { "Tresses", "Stripped", false });

            var results = InventoryItem.AsEnumerable().Select(x => new
            {
                item = x.Field<string>("Item"),
                inactive = InventoryItemLines.AsEnumerable().Where(y => (y.Field<string>("Item") == x.Field<string>("Item")) && !y.Field<Boolean>("Active")).ToList()
            }).ToList();
        }
       
    }
}

Linq Where Inside Include

1 Answers1