Filter in Entity Framework Include

Question

I need to include records of the child table with filtering on a condition Status='Completed'. I tried all the possible ways like Any(), IncludeFilter(), but I can't achieve what I'm looking for. I went through all the posts related to this query but no solution.

return await db.Jobs
               .Where(x => x.Account == id && 
                           x.Status == "Completed")
              .Include(x => x.Account1)
              .Include(x => x.Bids.Select(s => s.Account1))
              .ToListAsync();

I can filter on the main table Jobs, but I also need to filter the child table Bids. I short - I need jobs that are completed with bids whose status is Completed.

Possible duplicate of [EF 6 filtering child collections](https://stackoverflow.com/questions/34079166/ef-6-filtering-child-collections) — Progman, Sep 15 '19 at 08:19
You can check other questions like https://stackoverflow.com/questions/39636952/how-to-filter-include-entities-in-entity-framework, there are workarounds/solutions for your problem. — Progman, Sep 15 '19 at 08:36

score 0 · Answer 1 · answered Sep 18 '19 at 04:17

Include does not filter, it merely includes related data to be loaded eagerly along with the main entity being selected.

To get jobs where the account status is completed and has at least one bid with a status of completed:

db.Jobs.Where(x => x.Account == id 
    && x.Status == "Completed"
    && x.Bids.Any(b => b.Status == "Completed"))
    .Include(x => x.Account1)
    .ThenInclude(b => b.Account1)
    .ToListAsync();

The typical thing that trips people up when they return entities is that they'd say "but I only want back the Bids that are completed." This will return back all bids for jobs that are completed and have at least 1 completed bid.

To return back a filtered set of related data to those jobs, use ViewModels/DTOs to return to the views/API consumers rather than returning entities. Entities jobs are to reflect the data state. A job may include many bids, so the entity should reflect the complete data state for that job.

To return completed jobs with their completed bids, define POCO view model classes for the job, bid, etc. then use .Select() to project the entities into the view models:

var viewModels = db.Jobs
    .Where(x => x.Account == id 
    && x.Status == "Completed"
    && x.Bids.Any(b => b.Status == "Completed"))
    .Select(x => new JobViewModel
    {
       AccountId = x.Account,
       Account = x.Account1.Select(a => new AccountViewModel 
       { 
           AccountId = a.Id,
           // ...
       },
       Bids = x.Bids.Where(b => b.Status == "Completed)
           .Select(b => new BidViewModel 
           {
               BidId = b.Id,
               // ...
           }).ToList()
    }).ToListAsync();

Filter in Entity Framework Include

1 Answers1