CODEWITHSUNDEEP
sqlvb.netlinq
DDulla
DDulla

Reputation: 679

Select duplicate rows across multiple columns with LINQ

I'm attempting to find duplicate rows in a database across more then one column.

The following SQL query gives me a result of 247 rows

select Lastname, EmailAddress,  count(*) 
from leads where EmailAddress IS NOT null and isDeleted <> 'True' 
group by lastname, emailaddress having count(*) > 1

I thought the following in vb.net should return the same number of rows, but instead i get 201 results. 

  Dim duplicates = db.Leads.Where(Function(p) p.EmailAddress <> Nothing
  And p.isDeleted <> True)
 .GroupBy(Function(i) New With {i.EmailAddress, i.LastName})
 .Where(Function(g) g.Count() > 1)
 .[Select](Function(g) g.Key)

Should they not both return the same number of rows?

Upvotes: 2

Views: 936

Answers (1)

OneFineDay
OneFineDay

Reputation: 9024

Try this syntax: 

Dim duplicates = From p In db.leads Where
                 p.EmailAddress <> Nothing And
                 p.isDeleted <> True Group By
                 p.EmailAddress, p.LastName Into 
                 grp = Group Where (grp.Count() > 1)

Upvotes: 1

Related Questions