Entity Framework with inclusion and selection together

Question

I have the following entities (pseudocode to save space)

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] 

my task is to get a single Program with its associated ProgramFoodTypes with the condition that ProgramFoodType must be active and ProgramFoods with related Food and FoodType

I used the following:

1- the query below will retrieve the details of ProgramFoodTypes and ProgramFoods , but it will list all active and inactive 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- below the request will receive details, but no Food and 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); 

How to include the type of nutrition and food in the second request?

Answer 1

may be:

 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.Select(y => new { Food = y.Food, Type = y.FoodType }) }).ToArray().Select(m => m.Program); 

Answer 2

For your second solution, I think you can use:

 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, ProgramFoods = p.ProgramFoods }).ToArray().Select(m => m.Program); 

Update : Since you are using an anonymous type in your linq request, Include statements are rejected.
You will need to download all the related ProgramFoodTypes on the client side, and then filter:

 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); 

You can use AsNoTracking() or clone the returned Program object in a new object if you want to make sure your data is intact on the db side.

Answer 3

Try the following:

 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 && m.ProgramFoodTypes.All(t => t.IsActive));