CODEWITHSUNDEEP
c#linqentity-framework-6
bitshift
bitshift

Reputation: 6852

Why do I need distinct with this LINQ query but not with SQL

Im getting a list of codes from a table and their respective count. I dont need to use distinct in SQL, but if I do not add this in the LINQ query, I get many dupe rows.

So, why would I need the distinct call on the LINQ query?

SQL

SELECT COUNT(*) AS ServiceCodeCnt, d.ServiceCode 
FROM dbo.BackOrderItem d 
GROUP BY d.ServiceCode,  d.Model
HAVING d.Model ='UUTTIISJWW'

LINQ (via LINQpad)

void Main()
{

    var retval = (from a in BackOrderItems where a.Model == "UUTTIISJWW"
                 group a by new {a.ServiceCode, a.Model} into grp1
                 from b in grp1
                    select new {Code = b.ServiceCode, Count = grp1.Count( ) }).ToList().Distinct();             


    retval.Dump();              

}

Upvotes: 1

Views: 56

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109117

The statement ...

from b in grp1

... flattens the grouping. So you select all individual rows in each group, each with the count of its group.

Just remove this statement, so it becomes:

var retval = (from a in BackOrderItems where a.Model == "UUTTIISJWW"
             group a by a.ServiceCode into grp1
             select new
                    {
                        Code = grp1.Key, 
                        Count = grp1.Count()
                    })
             .ToList();

Note that I also removed Model from the grouping. It's not necessary, because you only filter out one Model.

Upvotes: 3

Related Questions