Entity Framework includes a child collection of filters

Question

I find it difficult to add any filter condition for the included elements in my LINQ query. My request is similar to

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

In the code above, "Internal" is another list of items. Now I need to filter out the internal elements. I only need the inner element with the filter condition inner.isDeleted = true.

The request should return a class, for example

public class Order
{

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

and the InnerDetails class, for example

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 for this because I'm new to LINQ and EF

Answer 1

: Entity Framework Plus

EF+ Query IncludeFilter .

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

: Include & IncludeFilter.

Wiki: EF+ Query IncludeFilter

:

, EF

,

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

:

:

EF Core. IncludeFilter EF+

IncludeFilter, IncludeFilter EF Core 2.x

:

Answer 2

EF Core. , EF6.

:

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