LINQ returning duplicate rows while SQL isn't

Question

I am working on a C# LINQ code using which I am trying to join two tables by grouping by a column in Table1

Below is the SQL query which returns correct results

SELECT
    SUM(scr.Count),spl.Name
FROM    Table1 scr
        ,Table2 spl
WHERE scr.StatusId = spl.StatusId
 GROUP by spl.Name

Equivalent LINQ which returns incorrect result

(from l in _dataContext.Table1
                     join r in
                     _dataContext.Table2 on
                     new {  l.StatusId } equals new { r.StatusId }
                     into gj
                     from r in gj.DefaultIfEmpty()                   
                     select new
                     {
                         l.Name,
                         Count = gj.Sum(j => j.Count)
                     }).GroupBy(l => l.Name).Select(x => new DataPoint
                     {
                         Name = x.Key,
                         Y = x.Sum(y => y.Count)
                     }).ToList();

Wasn't able to figure the error, Is there a better way to get the results equivalent to SQL query above?

Your Linq code is doing a left join, that's what DefaultIfEmpty does. — juharr
– juharr, Commented Jun 1, 2017 at 20:22
See msdn for Left Outer Join : code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng
– jdweng, Commented Jun 1, 2017 at 20:24

juharr · Accepted Answer · 2017-06-01 20:56:07Z

3

The verbatim equivalent would be

var results = (from l in db.Table1
               from r in db.Table2
               where l.StatusId == r.StatusId
               group j.Count by l.Name into g
               select new {
                   Name = g.Key,
                   Count = g.Sum()
               }).ToList();

The more correct join version would not use into with the join (also know as a group join) or DefaultIfEmpty as that results in a left join. Instead you just use a regular group by as part of your query syntax instead of needlessly tacking it on with Method syntax.

var results = (from l in db.Table1
               join j in db.Table2 on l.StatusId equals r.StatusId
               group j.Count by l.Name into g
               select new {
                   Name = g.Key,
                   Count = g.Sum()
               }).ToList();

And if you actually want all the names then this is how you'd do a left join.

var results = (from l in db.Table1
               join j in db.Table2 on l.StatusId equals r.StatusId into lrs
               from lr in lrs.DefaultIfEmpty()
               group lr.Count by l.Name into g
               select new {
                   Name = g.Key,
                   Count = g.Sum()
               }).ToList();

Or if the Count column is not nullable you might have to cast it to make it work

group (int?)lr.Count by l.Name into g

edited Jun 1, 2017 at 20:56

answered Jun 1, 2017 at 20:28

juharr

32.4k4 gold badges60 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

NetMage Over a year ago

Your more correct join version still uses into :)

juharr Over a year ago

@NetMage Yes, as part of the group by, not as part of the join, but I'll clarify that in the answer.

DoIt Over a year ago

@juharr I would like to include all the names in table1 even if there is no match in table2

juharr Over a year ago

@DoIt That's not what your SQL is doing.

DoIt Over a year ago

@juharr I thought so but now I would like to include them and if I use defaultifEmpty it causes redundancy again

|

Collectives™ on Stack Overflow

LINQ returning duplicate rows while SQL isn't

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related