none
Left outer join with filter on child records RRS feed

  • Question

  • I have two tables, ClaimPeriods (parent) and ExpenseClaims (child). (one-to-many)

    I would like to retrieve a list of all claim periods, with associated expense claims for each period, but with only those expense claims for a particular user.

    The following retrieves data in the right structure but with expense claims from everyone i.e. no restriction on whose expense claims they are

    from cp in ClaimPeriods
    select new {cp, cp.ExpenseClaims}
    
    


    And the following link query (left outer join) returns the right data, but in wrong structure as there are multiple rows for the same claim period.

    from cp in ClaimPeriods
    from ec in cp.ExpenseClaims 
    join ec in ExpenseClaims.Where(f=> f.claimantNumber == "7101") 
    on cp.id equals ec.claimPeriodID  into claims
    from x in claims.DefaultIfEmpty()
    
    select new {cp, x }
    
    


    Is there any way of combining the structure of the first query with the data retrieval of the second?

    If not, how do you parse the results from the second query so you get a set of claim periods each with their own set of expense claims?

    I hope I've made myself clear, it's quite hard to explain what I'm trying to get!
    • Edited by tc7101 Thursday, March 11, 2010 11:35 AM more readable
    Thursday, March 11, 2010 11:08 AM

Answers

All replies