Filtering by object property that has a boolean property-CodePudding

I have the following objects:

public class Item 
{
  public int Id {get; set;}
  public Process Process {get; set;}
}

public class Process
{
  public bool Alive {get; set;}
}

And I'm trying to this:

context.DB.Items
  .Include(x => x.Process)
  .Where(x => x.Process.Alive);

But the filtering keeps getting me items where Process.Alive is false.

What is the best way to filter by a property which is included in an object which is a property of the main Item?

CodePudding user response：

With EF Core you can use filtered Include for one-off queries:

context.DB.Items
   .Include(x => x.Process  
      .Where(x => x.Process.Alive));

or use global query filters to tell EF to only fetch "Alive" records:

https://docs.microsoft.com/en-us/ef/core/querying/filters

For EF6, eager loading a collection using Include represents the complete data state for that entity. Filtered includes can be misleading because they do not necessarily represent the complete picture for the data domain state at any given point in time.

If you generally want to deal with only active rows such as when viewing results etc. then this can be managed via projection. For example if I have a view where I want to display an Item and it's Processes, I would have an ItemViewModel with a collection of ProcessViewModels presenting just the data my view cares about. When I project that view model:

var item = context.Items
    .Select(x => new ItemViewModel
    {
        ItemId = x.ItemId,
        // ....
        Processes = x.Processes
            .Where(x => x.Alive)
            .Select(p => new ProcessViewModel
            {
                ProcessId = p.ProcessId,
                // ...
            }).ToList()
    }).Single(x => x.ItemId == itemId);

This can be simplified and centralized using Automapper and it's ProjectTo method so that you configure the Automapper mappings to respect the "Alive" active state, then your queries just look like:

var item = context.Items
    .ProjectTo<ItemViewModel>(config)
    .Single(x => x.ItemId == itemId);

Where "config" is a MapperConfiguration with details on how to map Item to ItemViewModel and Process to ProcessViewModel. Automapper takes care of the rest, projecting the necessary details into EF to generate the suitable SQL statement.

If you do want to work with entities to perform updates and reference details then you can project an anonymous type:

var itemsAndAliveProcesses = context.Db.Items
   .Select(x => new 
   {
       Item = x,
       AliveProcesses = x.Processes.Where(p => p.Alive).ToList()
   }).Single(x => x.ItemId == itemId);

Here you would use itemsAndAliveProcesses.AliveProcesses to access the alive processes for the item, rather than .Item.Processes which could either be left to lazy-load or eager-load if you did want to access the entire set.

I would recommend projection over filtered Include as leveraging projection will lead to more efficient queries than loading entire entity graphs, and it helps ensure that when you do work with entity graphs, those methods can consistently expect to receive a complete, or completable representation of the data state. (rather than possibly a filtered subset since you can filter Include on anything)