Linq to get complete row based on duplicate values in column

Question

I am looking for a LINQ to use with my datatable and display all the duplicate rows based on certain columns in windows form datagrid view.

The LINQ should give a result similar to the plain old below give SQL.

SELECT *
FROM Table1 t
JOIN (  SELECT Taskid, Studentid, DateChangeid
      FROM Table1
      GROUP BY Taskid, studentid, datechangeid
      HAVING COUNT(*) > 1) temp
  ON t.taskid = temp.taskid AND t.studentid= temp.studentid AND t.datechangeid= temp.datechangeid

Please never just dump SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), and show your own first efforts. They clarify more to us than you might think. — Gert Arnold
– Gert Arnold, Commented Feb 5, 2016 at 19:36
Noted, thanks. I am very new to C#, LiNQ. I will post detailed case for any further queries. Just added my code used for a similar situation. — user3658516
– user3658516, Commented Feb 12, 2016 at 18:35

Alexander Derck · Accepted Answer · 2016-02-05 07:58:43Z

1

This should do what you want:

var result = 
 t.GroupBy(g => new {TaskId = Taskid, StudentId = studentid, DatechangeId = datechangeid})
  .Where(g => g.Count > 1)
  .ToList();

Now you're joining Table1 on Table1 which isn't needed, just do the filtering on Table1 only.

Now the result will be of type List<IGrouping<'a,Table1>> though because of the anonymous keyselector, you can also define TaskId , StudentId and DateChangeID in a class to get rid of that. For example:

public class Table1GroupKey
{
   public int TaskId {get; set;}
   public int StudentId {get; set;}
   public int DateChangeId {get; set;}
}

Then you can use:

GroupBy(g => new Table1GroupKey { ... })

And your result will be List<IGrouping<Table1GroupKey,Table1>>.

edited Feb 5, 2016 at 7:58

answered Feb 5, 2016 at 7:53

Alexander Derck

14.7k5 gold badges61 silver badges78 bronze badges

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

2 Comments

user3658516 Over a year ago

Thanks, This is listing only 3 columns used for grouping. in my case I want to get complete rows with all columns from Table1. There are more columns in in my table other than the matching columns.

Alexander Derck Over a year ago

@user3658516 hence the IGrouping. They will be grouped by Table1GroupKey and for every key there is a list of records of type Table1 so you do have all the columns

user3658516 · Accepted Answer · 2016-02-12 18:28:59Z

0

I managed it by writing two linq as given below.

var RepeatedValues = from d in DataTableitems.AsEnumerable()
            group d by d.Field<string>("MobileNo") into itemGroup
            where itemGroup.Count() > 1
            select new { name = itemGroup.Key };

var RepeatedInRows= from d in DataTableitems.AsEnumerable()
              join c in RepeatedValues.AsEnumerable() on d.Field<string>
              ("MobileNo") equals c.name
              select d;

answered Feb 12, 2016 at 18:28

user3658516

12311 bronze badges

Collectives™ on Stack Overflow

Linq to get complete row based on duplicate values in column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related