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?

Solution

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.

Comments

[Basanta Matia]

Can I ask do we need to add EF+ reference or what ?

[Jonathan Magnan]

Yes, you need to add in reference this library and add "using Z.EntityFramework.Plus" namespace to be able to use the IncludeFilter feature.

[Basanta Matia]

But we can achieve this using EF only. In my answer

[Michael Freidgeim]

Unfortunately IncludeFilter has many limitations, and I wasn’t able to make it working

[greyxit]

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

[Offir]

@JonathanMagnan How can I ThenInclude after filtering?

[Admin]

hi Jonathon , can you answer this question? Thanks, stackoverflow.com/questions/62033907/… also will IncludeFilter do the filtering in the database or filter after retrieiving the initial IEnumerable results? Just curious for SQL optimization, thanks