开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜