Include filter child collection

Question

I have some difficulty to add some filter condition for included items in my LINQ query. My query is like

var item = _Context.Order.Include("Inner")
           .Include("Inner.first")
           .Include("Inner.second")
           .Where(x => ( !(x.IsDeleted) && (x.IsActive) && 
                 (x.itemid == id))).FirstOrDefault();

In the above code "Inner" is another list of item. Now i need to filter inner items. I only need inner item with filter condition inner.isDeleted = true.

Query should return a class like,

public class Order
{
    public string Name { get; set; }
    public List<InnerDetails> Inner{ get; set; }
    public bool IsDeleted { get; set; }
}

and InnerDetails class like

public class InnerDetails 
{
    public string Sample { get; set; }
    public bool IsDeleted { get; set; }
    public int firstId { get; set; }
    public int secondID { get; set; }
    public First first{ get; set; }
    public Second second{ get; set; }
}

Can anyone suggest me a better approach to do this because i am new in LINQ and EF?

What exactly are you trying to filter - do you want to include only the entries from order.inner where order.inner.isdeleted, or do you want to remove the whole record eg the whole row of order that does not meet the condition? If it is the second one, what if you have 2 entries in order.inner, one where isDeleted = true and one where isDeleted - false — Alex, Apr 04 '17 at 08:32
@Alex: I mentioned in the question like 'I only need inner item with filter condition inner.isDeleted = true.', ie i only need to include deleted inner record(inner.isDeleted = true). this is only for inner record and this will not affect outer record. — Isha John, Apr 04 '17 at 08:38
It seems you are trying to filter the nested list. I think this question may help you http://stackoverflow.com/questions/25183685/how-to-filter-nested-list-using-linq-lambda and this one http://stackoverflow.com/questions/7079378/how-to-filter-nested-collection-entity-framework-objects — Alex, Apr 04 '17 at 08:41

Jonathan Magnan · Accepted Answer · 2020-04-06T11:29:54.390

17

Disclaimer: I'm the owner of the project Entity Framework Plus

EF+ Query IncludeFilter feature allows filtering related entities.

var item = _Context.Order
           .IncludeFilter(x => x.Inner.Where(y => y.IsDeleted))
           .IncludeFilter(x => x.Inner.Where(y => y.IsDeleted).Select(y => y.first))
           .IncludeFilter(x => x.Inner.Where(y => y.IsDeleted).Select(y => y.second))
           .Where(x => ( !(x.IsDeleted) && (x.IsActive) && 
                 (x.itemid == id))).FirstOrDefault();

Note: You cannot mix Include & IncludeFilter.

Wiki: EF+ Query IncludeFilter

EDIT: Answer sub-question

But we can achieve this using EF only

Yes, under the hood, my library uses a similar solution as projection

var item = _Context.Order.Select(x => new {
                Order = x,
                Inner = x.Inner.Where(y => y.IsDeleted),
                first = x.Inner.Where(y => y.IsDeleted).Select(y => y.first)
                second = x.Inner.Where(y => y.IsDeleted).Select(y => y.second)
            })
            .Where(x => ( !(x.IsDeleted) && (x.IsActive) && (x.itemid == id)))
            .FirstOrDefault()
            .Select(x => x.Order)
            .FirstOrDefault();

Note: Code have not been tested

EDIT: Answer comment

I came across this issue in EF Core. Are you going to implement IncludeFilter also in the EF+Core version

Starting from the v1.10.0, the IncludeFilter is now supported in EF Core 2.x

See: Release Note

EDIT: Answer sub-question

How can I ThenInclude after filtering

We do not have a ThenInclude yet.

So you need to use IncludeFilter again with all filter and navigate through the list or entity you want to include.

edited Apr 06 '20 at 11:29

answered Apr 04 '17 at 13:16

Jonathan Magnan

10,874
2
38
60

Can I ask do we need to add EF+ reference or what ? – Basanta Matia Apr 04 '17 at 13:19
Yes, you need to add in reference this library and add "using Z.EntityFramework.Plus" namespace to be able to use the IncludeFilter feature. – Jonathan Magnan Apr 04 '17 at 13:24
But we can achieve this using EF only. In my answer – Basanta Matia Apr 04 '17 at 13:45
Unfortunately IncludeFilter has many limitations, and I wasn’t able to make it working – Michael Freidgeim Apr 26 '18 at 12:07
I came across this issue in EF Core. Are you going to implement IncludeFilter also in the EF+Core version? ? – greyxit May 07 '18 at 20:32
@JonathanMagnan How can I `ThenInclude` after filtering? – Offir Apr 03 '20 at 18:08
hi Jonathon , can you answer this question? Thanks, https://stackoverflow.com/questions/62033907/c-filtering-on-theninclude-with-entityframeworkplus-includefilter also will IncludeFilter do the filtering in the database or filter after retrieiving the initial IEnumerable results? Just curious for SQL optimization, thanks – May 27 '20 at 01:45

Amruthann K S · Answer 2 · 2020-02-21T08:36:49.887

-3

You can achieve this in EF Core itself:

var item = 
ctx.Order.Include("Inner")
         .Select(x => x.Inner
                .Where(innerItem => innerItem.IsDeleted))
         .FirstOrDefault();

Or,

var item = 
    ctx.Order.Include(o=> o.Inner
                     .Where(x => x.IsDeleted));

edited Feb 21 '20 at 08:36

answered Feb 21 '20 at 08:13

Amruthann K S

13
1

Include filter child collection

2 Answers2

Linked