CODEWITHSUNDEEP
c#.netlinqlinq-to-sqldatatable
Anubhav Sharma
Anubhav Sharma

Reputation: 323

Join rows using C# Linq

If there is a DataTable called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

How can we get the below DataTable as a result using Linq:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I tried achieving this result using the foreach loop crawling through each datarow one by one but came across major performance hit.

Not good with Linq but trying my hands on it. I would post my answer once I get something concrete but any help would be appreciated in between.

Sample working code without using Linq but not giving the desired performance. The below foreach loop is taking around 15mins if the DataTable dt2 has around 3500 records.

        DataTable dtFiles = dt2.Clone();

        //Logic to filter out the files by keyword name filter.
        foreach (DataRow row in dt2.Rows)
        {
            string studentid = row.Field<string>("StudentID");
            string filter = "StudentID = '" + studentid + "'";
            if(dtFiles.Select(filter).Count() == 0)//this means keyword is new 
            {
                DataRow dr = dtFiles.NewRow();
                dr["StudentName"] = row["StudentName"];
                dr["StudentID"] = row["StudentID"];
                dtFiles.Rows.Add(dr);
            }
            else
            {
                dtFiles.Select(filter).First<DataRow>()["StudentName"] += "," + row.Field<string>("StudentName");//Rows[0]
            }
        }

Upvotes: 1

Views: 1155

Answers (2)

James
James

Reputation: 429

Here is a little bit of a variation of the previous solution:

DataTable dt = dtFiles.Clone();

(from r in dtFiles.AsEnumerable()
         group r by r.Field<int>("SubjectID") into g
         select g)
         .Aggregate(dt, (d, g) =>
         {
              dt.Rows.Add(g.Key, String.Join(", ", g.Select(r => r.Field<string>("StudentName"))));
              return dt;
         });

Your result will be in the cloned DataTable dt.

Upvotes: 1

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

Get grouped data

var subjects = 
    from r in dtFiles.AsEnumerable()
    group r by r.Field<int>("SubjectID") into g
    select new {
       ID = g.Key,
       Students = String.Join(", ", g.Select(r => r.Field<string>("StudentName"))
    };

Then build DataTable (if you need it)

DataTable dt = new DataTable();
dt.Columns.Add("SubjectID", typeof(int));
dt.Columns.Add("StudentName", typeof(string));
// or you can just clone existing DataTable:
DataTable dt = dtFiles.Clone();

foreach(var subject in subjects)
   dt.Add(subject.ID, subject.Students);

Upvotes: 4

Related Questions