CODEWITHSUNDEEP
c#linq
prashanat chaudhari
prashanat chaudhari

Reputation: 31

Scenario to split and create new row in Datatable?

I have a DataTable having 3 Columns. I want to perform a split operation on column 2 row value one by one and create new rows from the output of split. Is there any Linq method syntax for this senario?

{
    Col1    UserID          RNo
    ABC     FName1.SName1   10
    PQR     FName2.SName2   20
    XYZ     FName3.SName3   30

}

Desired Output

{
    Col1    UserID      RNo
    ABC     FName1      10
    ABC     SName1      10
    PQR     FName2      20
    PQR     SName2      20
    XYZ     FName3      30
    XYZ     SName3      30
}

Upvotes: 2

Views: 503

Answers (2)

jdweng
jdweng

Reputation: 34421

Using a helper method :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication137
{
    class Program
    {
        const string SPLIT_COLUMN = "UserID";
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Col1", typeof(string));
            dt.Columns.Add("UserID", typeof(string));
            dt.Columns.Add("RNo", typeof(int));

            dt.Rows.Add(new object[] {"ABC", "FName1.SName1", 10});
            dt.Rows.Add(new object[] {"PQR", "FName2.SName2", 20});
            dt.Rows.Add(new object[] {"XYZ", "FName3.SName3", 30});

            DataTable dt2 = dt.AsEnumerable()
                .SelectMany(x => CloneRow(x, SPLIT_COLUMN)).CopyToDataTable();

        }
        public static  List<DataRow> CloneRow(DataRow row, string splitColumn)
        {
            DataTable dt = row.Table.Clone();

            foreach (string id in row.Field<string>(splitColumn).Split(new char[] { '.' }))
            {
                object[] newRow = dt.Columns.Cast<DataColumn>().Select(x => (x.ColumnName == splitColumn) ? id : row[x.ColumnName] ).ToArray();
                dt.Rows.Add(newRow);
            }
            return dt.AsEnumerable().ToList();
        }


    } 
}

Upvotes: 2

Abid Zaidi
Abid Zaidi

Reputation: 615

You can get your answer from here: "LINQ to separate column value of a row to different rows in .net"

In your case, considering a User Class with three properties, your LINQ would be:

LINQ Query

List<User> result = uList.SelectMany(item => item.UserId
                                       .Split('.')
                                       .Select(singleUserId => new User
                                       {
                                           Name = item.Name,
                                           UserId = singleUserId,
                                           RollNo = item.RollNo
                                       })).ToList();

Output

Name    UserId  RollNo
ABC     FName1  10
ABC     SName1  10
PQR     FName2  20
PQR     SName2  20
XYZ     FName3  30
XYZ     SName3  30

I hope this helps..

Upvotes: 3

Related Questions