In an EFCore eager loading LINQ query, how to reference the top-level entity within the ThenInclude() expression?

Rory_8479 0
Viewed 164

I have a LINQ query using EFCore 5 that eagerly loads several levels of related entities. In one of them I need to filter the referenced entities based on a field on the top-level entity, is there a way to do this?

The query I'd like, except within the Where how can I reference product?

context.Products
  .Include(product => product.PrimaryComponent)
  .ThenInclude(component => component.ComponentRules
                              .Where(cRule => cRule.FactoryId == product.FactoryId))
 
  .Where( /* other filters */ )

Within the Where() expression I can reference cRule and component but I can't reference product.

Tables:

dbo.Product ( Id int, FactoryId int, PrimaryComponentId int )
dbo.Component ( Id int, Name nvarchar(100) ) 
dbo.ComponentRule ( ComponentId int, RuleId int, FactoryId int, Notes nvarchar(max) ) 

-- These tables aren't used in these queries but here they are fyi: 
dbo.Rule ( Id int, Name nvarchar(100), ... ) 
dbo.Factory ( Id int, Name nvarchar(100), ... )

ERD

In this db, Products use Components and each Component has many associated Rules, depending on which Factory we're talking about. Each Product is built in just one Factory, so when I get the ComponentRule objects I want to load only the ones relevant to the Product's FactoryId, not all ComponentRules for all factories. There will still be several ComponentRules for each Component, just not so many.

Here's the idea if I were to write a SQL query:

select * 
from dbo.Product
inner join dbo.Component 
                on Product.PrimaryComponentId = Component.Id
inner join dbo.ComponentRule
                on Component.Id = ComponentRule.ComponentId

-- The line below is the tricky one: 
               and ComponentRule.FactoryId = Product.FactoryId 

-- ... plus other filters
where

I can't easily just write the SQL for it because I'm really pulling in several other entities and using .AsSplitQuery() for efficiency. So I'd really just like to be able to reference that top-level Product.FactoryId from within the .ThenInclude(...). Is there any way to do so?

ef-core-5.0 entity-framework-core c#
1 Answers

UPD: Assuming your model is:

// in your OnModelCreating()
modelBuilder.Entity<ComponentRule>()
            .HasOne(p => p.Component)
            .WithMany(b => b.ComponentRules);

public class Product
{
    public int Id { get; set; }
    public int FactoryId { get; set; }

    public int PrimaryComponentId { get; set; }
    public Component PrimaryComponent { get; set; }
}
public class Component
{
    public int Id { get; set; }
    public string Name { get; set; }

    public List<ComponentRule> ComponentRules { get; set; }
}
public class ComponentRule
{
    public int ComponentId { get; set; }
    public Component Component { get; set; }

    public int FactoryId { get; set; }
}

Maybe you can do:

context.Products
  // 'Include's are not needed for LINQ query with custom projection (thanks Svyatoslav Danyliv)
  // .Include(product => product.PrimaryComponent)
  // .ThenInclude(component => component.ComponentRules)
  .Where( /* other filters */ )
  .Select(product => new Product {
     Id = product.Id,
     FactoryId = product.FactoryId,
     PrimaryComponent = new Component {
       Id = product.PrimaryComponent.Id,
       Name = product.PrimaryComponent.Name,
       ComponentRules = product.PrimaryComponent.ComponentRules
                         .Where(r => r.FactoryId == product.FactoryId).ToList()
     },
  })
user_162970510
Related