[linq] Group by over dezelfde tabel met count

[Dennis]

Hallo,

Ik probeer met Linq de topics met het aantal replies uit mijn database te krijgen, maar dat wil nog niet zo lukken. Probleem is de count, waarmee ik in de knoei ga.

De query gaat maar over 1 tabel, maar die komt wel meerdere keren voor. In de join met zichzelf zit een aggregatie functie (count).

Dit is de query die wel goed werkt:

SQL:

SELECT
    t.MessageId MessageId, 
    COUNT(m.MessageId) Replies
FROM
    Message t
LEFT JOIN
    Message m
ON
    m.ParentMessageId = t.MessageId
WHERE
    t.ParentMessageId IS NULL AND
    t.CategoryId = 1
GROUP BY
    t.MessageId

Hierbij krijg ik twee resultaten terug: één Message met twee child-Messages, en één Message met geen child-Messages.

Met linq kwam ik tot:

C#:

            var test =
                from t in set.Messages
                join m in set.Messages on t.MessageId equals m.ParentMessageId
                where t.CategoryId == 1
                group m by new { t.MessageId, m.ParentMessageId } into bla
                select new { bla.Key.MessageId, ra = bla.Count() };

Ik krijg dan echter alleen de Message terug met twee child-Messages, de andere zie ik überhaupt niet eens.

Hoewel je denkt dat dit toch een bekend issue zou zijn kan ik er niets over vinden (of ik ben slecht in googlen ). Om één of andere reden moet die join left worden, en ik denk dat hij standaard een inner pakt ofzo?

Heeft iemand een suggestie?

[Dido]

De reden dat die join left moet worden lijkt me nogal duidelijk: als ie niet left (outer) is negeert ie alle records in je linker tabel die hij niet in je rechter tabel vint, dus alle topics met 0 reacties zie je niet terug.

De echte vraag is dus inderdaad, hoe maak je van die join in linq een left join?

Een andere oplossing is natuurlijk om alle messages een parent-id te geven, en een topicstart zichzelf als parent mee te geven. Dan doet een inner join het namelijk weer uitstekend. Kun je gewoon een count(*) -1 in je select opnemen

offtopic:
Kan iemand me eens het verschil uitleggen tussen een count(veld) en een count(*)? Je telt volgens mij namelijk altijd records, en geen velden.

[Verwijderd]

offtopic:
Bij count(veld) dwing je de database uit te gaan van dat specifieke veld bij 't tellen, en als dat veld niet in de gebruikte index(en) voorkomt die sowieso al nodig was/waren voor de rest van de query, zal 'ie meer database acties nodig hebben om het resultaat op te halen.
Bij count(*) mag de database zelf bepalen welk veld 'ie gebruikt (zal meestal een PK veld zijn die 'ie toch al nodig had) en is dus eigenlijk altijd minstens zo efficient als het veld zelf meegeven.

[pedorus]

Die vraag over count(*) versus count(veld) is hier niet offtopic, omdat dat verschil hier belangrijk is. Met count(veld) tellen rijen met null-waardes op dat veld niet mee, met count(*) wel. Voor performance wil je liever count(*) gebruiken als dat kan, maar dat geeft hier niet het juiste resultaat. Je moet dus iets met Count(...) doen ipv Count(). Hier het antwoord:

quote: http://stackoverflow.com/...t-join-group-by-and-count

SQL:

SELECT p.ParentId, COUNT(c.ChildId)
FROM ParentTable p
  LEFT OUTER JOIN ChildTable c ON p.ParentId = c.ChildParentId
GROUP BY p.ParentId

How can I translate this into LINQ to SQL?
[...]

C#:

from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
group j2 by p.ParentId into grouped
select new { ParentId = grouped.Key, 
             Count = grouped.Count(t=>t.ChildId != null) }

[Dido]

pedorus schreef op zaterdag 18 april 2009 @ 12:33:
Voor performance wil je liever count(*) gebruiken als dat kan, maar dat geeft hier niet het juiste resultaat.

Het geeft dus alleen niet het juiste resultaat omdat topicstarts "bijzonder" zijn. Als je een topicstart gewoon zichzelf als parentID geeft, werkt count(*) dus wel, en werkt een simpele (inner) join ook

Moet je dus alleen, zoals gezegd, 1 aftrekken van die count als je het aantal replies wilt weten in plaats van het aantal messages per thread.

[EfBe]

Verwijderd schreef op zaterdag 18 april 2009 @ 11:58:

offtopic:
Bij count(veld) dwing je de database uit te gaan van dat specifieke veld bij 't tellen, en als dat veld niet in de gebruikte index(en) voorkomt die sowieso al nodig was/waren voor de rest van de query, zal 'ie meer database acties nodig hebben om het resultaat op te halen.
Bij count(*) mag de database zelf bepalen welk veld 'ie gebruikt (zal meestal een PK veld zijn die 'ie toch al nodig had) en is dus eigenlijk altijd minstens zo efficient als het veld zelf meegeven.

count(*) gaat de database gewoon kijken hoeveel rows er zijn en neemt niet zoals jij zegt een veld, hij telt gewoon het aantal rows. In een grouped set zal dat het aantal rows in de group zijn. Count(field) is iets heel anders, want dan wil je binnen een set het aantal values weten. Het rare is dat sommige databases niet Count(DISTINCT Field) ondersteunen, terwijl dat nu juist het enige bestaansrecht voor count(field) is. Immers, Count(field) zonder distinct == count(*), maar count(distinct field) is nu precies wat je wilt weten: het aantal values voor het veld field.

Dennis schreef op zaterdag 18 april 2009 @ 10:26:
Ik probeer met Linq de topics met het aantal replies uit mijn database te krijgen, maar dat wil nog niet zo lukken. Probleem is de count, waarmee ik in de knoei ga.

De query gaat maar over 1 tabel, maar die komt wel meerdere keren voor. In de join met zichzelf zit een aggregatie functie (count).

Dit is de query die wel goed werkt:

SQL:

SELECT
    t.MessageId MessageId, 
    COUNT(m.MessageId) Replies
FROM
    Message t
LEFT JOIN
    Message m
ON
    m.ParentMessageId = t.MessageId
WHERE
    t.ParentMessageId IS NULL AND
    t.CategoryId = 1
GROUP BY
    t.MessageId

Hierbij krijg ik twee resultaten terug: één Message met twee child-Messages, en één Message met geen child-Messages.

In Linq is groupby niet hetzelfde als in SQL. In Linq vorm je nl. grouped objects, dus in feite een hierarchy en in SQL is het een enkele set. Dit moet je goed uit elkaar halen.

Met linq kwam ik tot:

C#:

            var test =
                from t in set.Messages
                join m in set.Messages on t.MessageId equals m.ParentMessageId
                where t.CategoryId == 1
                group m by new { t.MessageId, m.ParentMessageId } into bla
                select new { bla.Key.MessageId, ra = bla.Count() };

Ik krijg dan echter alleen de Message terug met twee child-Messages, de andere zie ik überhaupt niet eens.
Hoewel je denkt dat dit toch een bekend issue zou zijn kan ik er niets over vinden (of ik ben slecht in googlen ). Om één of andere reden moet die join left worden, en ik denk dat hij standaard een inner pakt ofzo?

De join is waarschijnlijk een inner join. Een 'into x' achter de join plaatsen en een extra from with DefaultIfEmpty, zoiets als: (en de groupby is echt op 1 veld!)

code:

var test =  from t in set.Messages
            where t.CategoryId == 1
            join m in set.Messages on t.MessageId equals m.ParentMessageId into messages
            from v in messages.DefaultIfEmpty()
            group v by v.MessageId into bla
            select new { bla.Key, ra = bla.Count() };

Je kunt het ook anders doen:

code:

SELECT M.MessageId, 
       (select count(*) FROM Messages M2
        WHERE M2.MessageId=M.ParentMessageId) AS NumberOfChildMessages
FROM Messages M
WHERE M.CategoryId = 1

En die is waarschijnlijk simpeler te schrijven, want een message heeft waarschijnlijk een collectie 'ChildMessages', dus dan wordt de linq query:

code:

var q = from m in ctx.Messages
        where m.CategoryId == 1
        select new { m.MessageId, NumberOfChildMessages = m.ChildMessages.Count()};

[ Voor 53% gewijzigd door EfBe op 18-04-2009 14:56 ]

[Dennis]

Efbe, wat ontzettend stom dat dat niet in mij is opgekomen. Had ik natuurlijk kunnen bedenken, maar ik probeer voor het eerst Linq en ben nog niet helemaal doordrongen dat het object geörienteerd is, en niet zoals Sql .

Goed om te zien ook dat er in ieder geval nog een leuke discussie over die count aggregatie op gang is gekomen, want ik had het gevoel dat ik een makkelijke vraag had gesteld. Ik heb hem echter wel op kunnen lossen. Allereerst met de reply van pedorus, die eenvoudig te wijzigen was naar mijn situatie. Ik bleef toen echter het probleem met de grouped sets houden, die Efbe ook al noemde.

Daarna heb ik het hier naartoe vertaald:

C#:

            XElement element = new XElement("Collection",
                from t in set.Messages
                where t.ParentMessageId == null && t.CategoryId == categoryId
                select new XElement("Message",
                    new XElement("MessageId", t.MessageId),
                    new XElement("CategoryId", t.CategoryId),
                    new XElement("InsertUserId", t.InsertUserId),
                    new XElement("InsertDate", t.InsertDate),
                    new XElement("ModificationUserId", t.ModificationUserId),
                    new XElement("ModificationDate", t.ModificationDate),
                    new XElement("Subject", t.Subject),
                    new XElement("MessageBodyParsed", t.MessageBodyParsed),
                    new XElement("Icon", t.Icon),
                    new XElement("Views", t.Views),
                    new XElement("Status", t.Status),
                    new XElement("ChildMessages", t.Messages.Count), 
                    new XElement("LatestChildMessageInsertDate", t.Messages.Max(y => y.InsertDate))));

Zoals je ziet prop ik alles in XElement objecten, omdat mijn functie een webservice is. De code die hier staat werkt nog niet helemaal, want de laatste regel mag niet. Ik heb daar vast een fout in de expressie staan, want hoewel hij compileert, krijg ik bij uitvoeren een exception.

Heeft iemand een idee hoe ik dat moet aanpassen? Ik hoop niet per se op een werkend voorbeeld, maar ben vooral benieuwd naar het hoe en waarom, want die (lambda) expressies blijf ik erg lastig vinden .

[Dennis]

Iemand hier nog een suggestie .

[pedorus]

Voor alle XElements: volgens mij heeft .NET al uitgebreide XML-serialisatie en hoef je dit niet perse uit te schrijven.

Voor de foutmelding: mijn glazen bol is stuk over welke foutmelding je krijgt, maar ik denk dat het te maken heeft met het nemen van een maximum over 0 waarnemingen. Probeer het eens door aan de rechterkant te casten naar een Nullable type (DateTime?).