CODEWITHSUNDEEP
c#linqdatatable
user3658516
user3658516

Reputation: 113

selecting repeated values from distinct group using LINQ

I have a Datatable as below and I want to select repeated names from other Classes

Name    Class
Akbar   1B
Akbar   1B
Amar    1A
Amar    1C
Antoney 1A
Bindhu  1B
Bindhu  1D
John    1C
Raj     1B
Bindhu  2A

Th result should be as below

Amar    1A
Amar    1C
Bindhu  1D
Bindhu  1B
Bindhu  2A

Thanks in advance for any guidance

Code 

var dtStudents = new DataTable();
dtStudents.Columns.Add("StudentID", typeof(int));
dtStudents.Columns.Add("StudentName", typeof(string));
dtStudents.Columns.Add("Class", typeof(string));
dtStudents.Columns.Add("ContactNo", typeof(string));

DataRow drStudent = dtStudents.NewRow();

drStudent["StudentID"] = 1;
drStudent["StudentName"] = "Akbar";
drStudent["Class"] = "1B";
drStudent["ContactNo"] = "989878679";
dtStudents.Rows.Add(drStudent);

dtStudents.Rows.Add(new object[] { 2, "Akabr", "1B", "989777" });
dtStudents.Rows.Add(new object[] { 3, "Amar", "1A", "3453" });
dtStudents.Rows.Add(new object[] { 4, "Amar", "1C", "543534" });
dtStudents.Rows.Add(new object[] { 5, "Antoney", "1A", "54345" });
dtStudents.Rows.Add(new object[] { 6, "Bindhu", "1B", "53453" });
dtStudents.Rows.Add(new object[] { 7, "Bindhu", "1D", "3453453" });
dtStudents.Rows.Add(new object[] { 8, "John", "1C", "3245345" });
dtStudents.Rows.Add(new object[] { 9, "Bindhu", "2A", "5345345" });

var results =
    from d in dtStudents.AsEnumerable()
    select d;

dataGridView1.DataSource = results.CopyToDataTable<DataRow>();

Upvotes: 3

Views: 985

Answers (3)

devuxer
devuxer

Reputation: 42374

var results = dtStudents.AsEnumerable()
    .GroupBy(
        x => x.Field<string>("StudentName"), // group by student name
        (k, xs) => xs.GroupBy(
            x1 => x1.Field<string>("Class"), // group by class
            (k1, xs1) => xs1.First())) // if there are duplicates, take only the first
    .Where(x => x.Count() >= 2) // remove if student only has one class
    .SelectMany(x => x); // flatten back to a single collection

dataGridView1.DataSource = results.CopyToDataTable<DataRow>();

Upvotes: 2

Enigmativity
Enigmativity

Reputation: 117124

I would go about this this way:

var results =
    dtStudents
        .AsEnumerable()
        .Select(x => new
        {
            StudentID = (int)x[0],
            StudentName = (string)x[1],
            Class = (string)x[2],
            ContactNo = (string)x[3],
        })
        .GroupBy(
            x => x.StudentName,
            (key, xs) => new 
            { 
                StudentName = key,
                Classes = xs.Select(x => x.Class).Distinct()
            })
        .Where(x => x.Classes.Skip(1).Any())
        .SelectMany(x => x.Classes
            .Select(y => new 
            { 
                StudentName = x.StudentName,
                Class = y
            }));

Upvotes: 1

Jason Boyd
Jason Boyd

Reputation: 7029

Well, you don't really specify how you are connecting to your database so... regardless, let's assume you have some IQueryable called, I don't know, classes, or something. Then you can do the following:

classes
.GroupBy(
    x => x.Name, 
    (key, values) => new { Name = key, Classes = values.Select(x => x.Class).Distinct())
.Where(x => x.Classes.Take(2).Count() == 2)

Upvotes: 1

Related Questions