I have the following entities ( pseudo code 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 single Program with its related ProgramFoodTypes with condition ProgramFoodType should be active and ProgramFoods with related entities Food and FoodType
I used the following so far
1- the below query will retrieve the details of ProgramFoodTypes and ProgramFoods but it will bring 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- the below query will retrieve the details but missing the 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 food and food type in the second query?
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, p.ProgramFoods }).ToArray().Select(m => m.Program); update : since you are using an anonymous type in your linq query, the include statements are discarded.
You would have to load 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 into a new object in case you want to make sure your data will be intact on the database side.
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);
Try this:
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));