CODEWITHSUNDEEP
c#selectlinq-to-sqlcount
mattgcon
mattgcon

Reputation: 4858

Linq statement to return count and fields

I need to be able to return the rows from a table where there are duplicates of a certain data column.

The table contains numerous columns, but I only need to return the lastname, firstname, email and the count of the emails (i.e. if there is more than one record with the same email address.)

I have tried the following which returns the correct records:

var _i = (from a in _db.WebPersonalInfos group a by a.Email into g where g.Count() > 1 orderby g.Key select g).ToList();

However, I need to revise this to only return lastname, firstname, email and the count(email).

I.E. "Smith", "Joe", "[email protected]", 4

I am not sure how to accomplish this exactly.

Upvotes: 0

Views: 1375

Answers (1)

D Stanley
D Stanley

Reputation: 152644

Assuming that records with the same email address have the same first/last names you could do:

var _i = (from a in _db.WebPersonalInfos 
          group a by a.Email into g 
          where g.Count() > 1 
          orderby g.Key 
          select new {
             LastName = g.First().LastName, 
             FirstName = g.First().FirstName, 
             Email = g.Key, 
             Count = g.Count()
          })
         .ToList();

Upvotes: 1

Related Questions