Linq Query with a Where clause in an Include statement [duplicate]

Question

I am trying to replace my big, ugly query; although ugly it works as desired:-

using (var ctx = new Data.Model.xxxTrackingEntities())
{
    var result = ctx.Offenders
        .Join(ctx.Fees, o => o.OffenderId, f => f.OffenderId,
        (o, f) => new { Offenders = o, Fees = f })
        .Join(ctx.ViolationOffenders, o => o.Fees.ViolationId, vo => vo.ViolationId,
        (o, vo) => new { Offenders = o, ViolationOffenders = vo })
        .Join(ctx.Violations, v => v.ViolationOffenders.ViolationId, vo => vo.ViolationId,
        (v, vo) => new { Violations = v, ViolationOffenders = vo })
        .Where(o => o.Violations.Offenders.Offenders.YouthNumber != "")
        .ToList();

    gvwData.DataSource = result;
}

with the following linq Query:-

 var result = ctx.Offenders
        .Include(o => o.Fees.Where(f => f.Amount != null))
        .Include(o => o.ViolationOffenders)
        .Include(o => o.ViolationOffenders.Select(of => of.Violation))
        .Where(o => o.YouthNumber != "" && o.FirstName != "")
        .ToList();

I am blowing up on the 2nd line of the query... once I add the Where clause... o => o.Fees.Where(f=> f.Amount != null)

The error message I get...

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.

In addition, I tried writing my query as:-

   var result = ctx.Offenders
        .Include(o => o.Fees)
        .Include(o => o.ViolationOffenders)
        .Include(o => o.ViolationOffenders.Select(of => of.Violation))
        .Where(o => o.YouthNumber != "" && o.FirstName != "" && o.Fees.Where(f=> f.Amount != null))
        .ToList();

But then I get the following error:-

Operator '&&' cannot be applied to operands of type 'bool' and 'System.Collections.Generic.IEnumerable

I know the concept is right, but I need help with the syntax.

Answer 1

In .Net 5 Filtered include feature is added (EF Core 5.0).

Supported operations are: Where, OrderBy, OrderByDescending, ThenBy, ThenByDescending, Skip, and Take

using (var context = new BloggingContext())
{
    var filteredBlogs = context.Blogs
        .Include(blog => blog.Posts
            .Where(post => post.BlogId == 1)
            .OrderByDescending(post => post.Title)
            .Take(5))
        .ToList();
}

MSDN Reference : https://docs.microsoft.com/en-us/ef/core/querying/related-data/eager

Answer 2

You cant have a Where inside the Where, but you can use Any which will return a boolean

var result = ctx.Offenders
    .Include(o => o.Fees)
    .Include(o => o.ViolationOffenders)
    .Include(o => o.ViolationOffenders.Select(of => of.Violation))
    .Where(o => o.YouthNumber != "" && o.FirstName != "" 
        && o.Fees.Any(f=> f.Amount != null)) // here
    .ToList();