CODEWITHSUNDEEP
c#sqllinq
Janis S.
Janis S.

Reputation: 2626

Group by LINQ vs group by SQL

I have the following query in SQL which I would like to convert to LINQ statement.

select AreaId, BrandId, MilestoneId, DocumentCategoryId
from Document
group by AreaId, Brandid, MilestoneId, DocumentCategoryId

I have tried, e.g.,

var docs =
    from d in documents
    group d by new
    {
        d.Area,
        d.Brand,
        d.MilestoneId,
        d.DocumentCategoryId
    } into gcs
    select new Group()
    {
        Area = gcs.Key.Area,
        Brand = gcs.Key.Brand,
        MilestoneId = gcs.Key.MilestoneId,
        DocumentCategoryId = gcs.Key.DocumentCategoryId,
    };

And

var docs = documents
    .GroupBy(d => new Group
    {
        Area = d.Area,
        Brand = d.Brand,
        MilestoneId = d.MilestoneId,
        DocumentCategoryId = d.DocumentCategoryId,
    })

but the result in SQL returns 88 rows (the aim), in query syntax 78 rows and in LINQ 270 (total number).

I would like a LINQ statement for to return 88 rows.

Upvotes: 1

Views: 1857

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063704

I expect the final version is essentially switching to LINQ-to-Objects - i.e. populating a Group object per row, then grouping in .NET terms, which will mean one row per group as I doubt your Group implements the right patterns for equality. To get LINQ to treat the last version correctly, you probably need to add an anonymous type into the mix (LINQ understands this should behave like a tuple):

var docs = documents
    .GroupBy(d => new
    {
        Area = d.Area,
        Brand = d.Brand,
        MilestoneId = d.MilestoneId,
        DocumentCategoryId = d.DocumentCategoryId,
    }).Select(grp => new Group {
        Area = grp.Key.Area,
        Brand = grp.Key.Brand,
        MilestoneId = grp.Key.MilestoneId,
        DocumentCategoryId = grp.Key.DocumentCategoryId,
    });

As for the 88 vs 78: have you looked at what SQL is being issued? That should tell you what it is doing differently.

Upvotes: 2

Related Questions