c# - 包含和选择在一起的 Entity Framework

Question

我有以下实体(伪代码以节省空间)

Program [ int Id, 
          string Name, 
          List<ProgramFoodType> ProgramFoodTypes, 
          List<ProgramFood> ProgramFoods]

ProgramFoodType[ int Id, int ProgramId, int Type, bool IsActive]
ProgramFood [ int Id, int ProgramId, Food Food, FoodType FoodType]
Food [int Id, string Name]
FoodType [int Id, string Name]

我的任务是获取单个 Program 及其相关 ProgramFoodTypes 条件 ProgramFoodType should be active 和 ProgramFoods 及其相关实体 Food 和 FoodType

到目前为止我使用了以下内容

1- 下面的查询将检索 ProgramFoodTypes 和 ProgramFoods 的详细信息，但它会带来所有事件和非事件的 ProgramFoodTypes

var program = mEntities.Programs
                          .Include(p =>p.ProgramFoodTypes)
                          .Include(p =>p.ProgramFoods.Select(f =>f.Food))
                          .InClude(p =>p.ProgramFoods.Select( f =>f.FoodType))
                          .Where(m =>m.Id== Id);

2- 下面的查询将检索详细信息，但缺少 Food 和 FoodType

var program = (from p in mEntities.Programs
              where p.Id ==Id
              select new {
                 Program = p,
                 ProgramFoodTypes = from pf in p.ProgramFoodTypes
                                    where pf.IsActive
                                    select pf,                  
                 ProgramFoods = p.ProgramFoods // here i can't add include statement
              }).ToArray().Select(m => m.Program);

如何在第二个查询中包含食物和食物类型？

Answer 1

对于你的第二个解决方案，我认为你可以使用:

var program = (from p in mEntities.Programs
                  .Include(p => p.ProgramFoods.Select(f => f.Food))
                  .Include(p => p.ProgramFoods.Select(f => f.FoodType))
               where p.Id == Id
               select new {
                  Program = p,
                  ProgramFoodTypes = from pf in p.ProgramFoodTypes
                                     where pf.IsActive
                                     select pf,                  
                  p.ProgramFoods 
               }).ToArray().Select(m => m.Program);

更新:由于您在 linq 查询中使用匿名类型，Include statements are dismissed.
您必须在客户端加载所有相关的 ProgramFoodTypes，然后进行过滤:

var program = mEntities.Programs
                   .Include(p => p.ProgramFoodTypes)
                   .Include(p => p.ProgramFoods.Select(f => f.Food))
                   .Include(p => p.ProgramFoods.Select(f => f.FoodType))
                   .SingleOrDefault(m => m.Id == Id);

program.ProgramFoodTypes = program.ProgramFoodTypes.Where(pft => pft.IsActive);  

您可以使用 AsNoTracking() 或在新对象中克隆返回的 Program 对象，以确保您的数据在数据库端完好无损。