[LINQ(toSQL)] Outer Join met Group

[tonyisgaaf]

Ondertussen opgelost, post aangevuld.

Ik ben bezig in C# een ASP.NET MVC webapp te bouwen. Nou was ik tot voor kort niet erg bekend met C#, maar heb me door "Pro ASP.NET MVC Framework" van Steve Sanderson gewerkt (waarin gelukkig een heleboel "language features" worden uitgelegd).
Om mijn nieuwe kennis gelijk te toetsen aan kunde heb ik besloten een oud PHP spaghetti-knutselwerkje van mezelf te porten naar ASP.NET MVC. Deze "huislijst" web-app heb ik een aantal jaren geleden gebouwd en is nog steeds in gebruik bij de verschillende studentenhuizen waar ik gewoond heb. De admin-kant had ik nooit afgebouwd, dus voor toevoegen en verwijderen van gebruikers is men nog op mij aangewezen.

Ik heb het volgende DB-diagram:

Dus:
Many-to-many Persons <==> Houses
Meerdere Payments in Houses
Meerdere PaymentParts in Payments

Ik heb een Person en Balance object:

C#:

public class Person
{
    public int ID { get; set; }
    public string EmailAddress { get; set; }
    public string Password { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Balance
{
    public Person Person { get; set; }
    public int Amount { get; set; }
}

Nu wil ik voor een House een IQueryable<Balance> terugkrijgen, met daarin alle Persons en bijhorende Amounts. Deze Balance.Amount wil ik krijgen door voor een persoon alle PaymentPart.Mutation op te tellen, maar natuurlijk alleen voor die PaymentParts die bij een Payment horen die bij het juiste House horen.

Dus ongeveer zoiets als:

edit:
SUM functie vergeten

SQL:

SELECT Person.ID, SUM(PaymentPart.Mutation) AS Amount
FROM Person LEFT OUTER JOIN PaymentPart
ON Person.ID = PaymentPart.PersonID
GROUP BY Person.ID

En dat dacht ik als volgt te doen in LINQ:

C#:

public IQueryable<Balance> GetBalances(int houseID)
{
    var persons = from p in _dc.Persons
                  join ph in _dc.PersonHouses
                  on p.PersonID equals ph.PersonID
                  where ph.HouseID == houseID
                  select p;

    var paymentParts = from pp in _dc.PaymentParts
                       join p in _dc.Payments
                       on pp.PaymentID equals p.PaymentID
                       where p.HouseID == houseID
                       select pp;

    var query = from p in persons
                join pp in paymentParts
                on p.PersonID equals pp.PersonID into g
                from ppg in g.DefaultIfEmpty()
                group ppg by ppg.PersonID into gpp // hier gaat het mis, want null
                select new Balance
                {
                    Person = GetPerson(gpp.Key),
                    Amount = gpp.Sum(p => p.Mutation) == null ? 0 : gpp.Sum(p => p.Mutation)
                };

    return query;
}

Het stuk "g.DefaultIfEmpty()" heb ik van onder andere http://www.jeffblankenbur...t-outer-join-in-linq.aspx en http://www.hookedonlinq.com/OuterJoinSample.ashx
Nou ben ik aan het testen, waarbij er wel Persons zijn, maar nog geen PaymentParts, dus het gaat mis bij "group ppg by ppg.PersonID", omdat ppg null is... wat klopt, want er zijn nog geen PaymentParts. Het resultaat zou een lijst van Balances met Personen met elk een Amount van 0 moeten zijn, maar zover komt het niet.

Hoe krijg ik het nou voor elkaar dat LINQ de ge-left-outer-joinde resultset grouped op person.PersonID?
Wat ik nu zie wat er gebeurd is dat "into g" resulteert in een IEnumerable<PaymentPart>, dus daar gaat het al mis, want daarin ontbreekt dan dus direct de relatie met de Persons.

Heeft iemand een aanwijzing hoe ik dit met LINQ voor elkaar kan krijgen?

Opgelost
Direct na deze post ben ik verder gaan zoeken en kwam ik dit antwoord tegen:
http://stackoverflow.com/...t-join-group-by-and-count
De denkfout die ik maakte was te denken dat "p" niet meer beschikbaar was in de "group by", maar dat is die dus nog wel.

Wat resulteert in de volgende (werkende) code:

C#:

public IQueryable<Balance> GetBalances(int houseID)
{
    // knip
    // zie code hierboven

    var query = from p in persons
                join pp in paymentParts
                on p.PersonID equals pp.PersonID into g
                from ppg in g.DefaultIfEmpty()
                group ppg by p.PersonID into gpp // hier is p dus nog gewoon beschikbaar
                select new Balance
                {
                    Person = GetPerson(gpp.Key),
                    Amount = gpp.Sum(p => p.Mutation) == null ? 0 : gpp.Sum(p => p.Mutation)
                };

    return query;
}

[ Voor 12% gewijzigd door tonyisgaaf op 09-10-2009 22:18 . Reden: opgelost ]