Linq to get elements in a list (parent) where a condition is satisfied on property of an object in a child list?
I was wondering if someone could help me in generating a linq query for the following scenario.
Here are the classes with the relevant properties:
public class Employee
{
IList<Employee> DirectReports { get; set;}
IList<BonusPlan> BonusPlans { get; set;}
BonusPlanTemplate BonusPlanTemplate { get; set;}
}
public class BonusPlan
{
FiscalPeriod FiscalPeriod { get; set; }
Employee Employee { get; set;}
}
I'm trying to create a method:
IEnumerable<Employee> GetDirectReportsWithoutBonusPlansCreatedForFiscalPeriod(FiscalPeriod fiscalPeriod)
So basically I have this to get the directreports with bonus plans for a particular fiscal period:
var query = from dr in DirectReports
开发者_高级运维 from bp in dr.BonusPlans
where bp.Employee.BonusPlanTemplate != BonusPlanTemplate.Empty &&
bp.FiscalPeriod==fiscalPeriod
select dr;
IList<Employee> directReportsWithBonusPlansCreated = query.ToList();
Then I get all of the DirectReports that should have bonus plans setup (indicated by having a BonusPlanTemplate assigned) that aren't in the list from the previous query.
var query2 = from dr in DirectReports
where dr.BonusPlanTemplate != BonusPlanTemplate.Empty &&
!directReportsWithBonusPlansCreated.Contains(dr)
select dr;
This produces the correct results but it seems like there must be another way. I'm not sure if I need to do this in two steps. Can someone please help me to combine these two linq queries and possibly make it more efficient. I have relatively little experience with Linq.
Do you need the first query for any other reason? If not, it's pretty easy:
var query = from dr in DirectReports
where dr.BonusPlanTemplate != BonusPlanTemplate.Empty
&& !dr.BonusPlans.Any(bp => bp.FiscalPeriod == fiscalPeriod)
select dr;
You could make your life easier use an extra method in Employee
:
public bool HasBonusPlanForPeriod(FiscalPeriod period)
{
return BonusPlans.Any(bp => bp.FiscalPeriod == fiscalPeriod);
}
Then your original first query becomes:
var query = from dr in DirectReports
where dr.BonusPlanTemplate != BonusPlanTemplate.Empty &&
dr.HasBonusPlanForPeriod(fiscalPeriod)
select dr;
IList<Employee> directReportsWithBonusPlansCreated = query.ToList();
and the second query becomes:
var query = from dr in DirectReports
where dr.BonusPlanTemplate != BonusPlanTemplate.Empty &&
!dr.HasBonusPlanForPeriod(fiscalPeriod)
select dr;
IList<Employee> directReportsWithBonusPlansCreated = query.ToList();
This is a tricky one...first I thought "Oh it's an outer join...use DefaultIfEmpty". Then I realized you were doing a select many (that's what the two from clauses boil down to). So I did a search for DefaultIfEmpty combined with SelectMany and came up with this gem. Applied to your scenario we get
var query =
from dr in DirectReports
from bp in dr.BonusPlans.DefaultIfEmpty()
where dr.BonusPlanTemplate != BonusPlanTemplate.Empty &&
bp.FiscalPeriod==fiscalPeriod &&
bp==null
select dr;
See if that works for you.
精彩评论