CODEWITHSUNDEEP
c#linqdatatable
GustavoKnx
GustavoKnx

Reputation: 93

Create DataTable using LINQ to select multiple columns

I need to create a DataTable from another DataTable that I have but with only some columns (exactly same rows).

Is there an easy way to do this using LINQ?

I tried:

DataTable dataCopy = dt.AsEnumerable()
                       .Select(r => new { A = r.Field<string>("Column A"),
                                          B = r.Field<string>("Column B") });

But it doesn't have a "CopyToDataTable" method.

I'm looking for the best performance way to do this because my "source" DataTable is huge!

Upvotes: 1

Views: 2150

Answers (3)

BanMe
BanMe

Reputation: 133

    public static DataTable GetDataTablePart(this DataTable dt, params string[] ColumnNames)
    {
        var dtPart = new DataTable("TablePart");
        var Names = new List<DataColumn>();
        foreach (DataColumn Column in dt.Columns)
        {
            if(ColumnNames.Contains(Column.ColumnName))
            {
                Names.Add(Column);
            }
        }
        dtPart.Columns.AddRange(Names.ToArray());
        foreach(DataRow row in dt.Rows)
        {
            var NewRow = new object[Names.Count()];
            var i = 0;
            foreach (var Name in Names)
            {
                NewRow[i] = row[Name];
                i = i + 1;
            }
            dtPart.LoadDataRow(NewRow, false);
        }
        return dtPart;
    }

linq version....

    public static DataTable GetDataTablePart(this DataTable dt, params string[] ColumnNames)
    {
        var RowCount = 0;
        var dtPart = new DataTable("TablePart");
        dtPart.Columns.AddRange((from column in dt.Columns.Cast<DataColumn>()
                     where ColumnNames.Contains(column.ColumnName)
                     select column).ToArray());
        return (from row in dt.AsEnumerable()
                  let rowCount = RowCount = dt.Rows.Count
                  let RowValues = (from column in dtPart.Columns.Cast<DataColumn>()
                                   select row[column]).ToArray()
                  let decCount = RowCount = RowCount - 1
                  where dtPart.LoadDataRow(RowValues,LoadOption.OverwriteChanges) != default && decCount > 0
                  select dtPart).FirstOrDefault();
    }

Upvotes: 0

Leandro Bianch
Leandro Bianch

Reputation: 92

    Stopwatch time = new Stopwatch();

                time.Start();
    //COMPARE YOUR CODE (.Copy, Clone or Develop yourself)
                DataTable dtTarget = dtSource.CopyDataTable(new List<string>() { "A", "B" });

                foreach (DataColumn column in dtTarget.Columns)
                {
                    Console.WriteLine("ColumnName : {0}", column.ColumnName);

                    foreach (DataRow row in dtTarget.Rows)
                    {
                        Console.WriteLine("Rows : {0}", row[column.ColumnName]);
                    }
                }

                time.Stop();

                Console.WriteLine("{0}", time.Elapsed);



public static class DataTableHelper
    {
        public static DataTable CopyDataTable(
           this DataTable dtSource,
            List<string> columnsName)
        {

            DataTable dtTarget = new DataTable();
            if (dtSource.Columns.Count > 0)
            {

                foreach (DataColumn columnSource in dtSource.Columns)
                {
                    var columnTargetMapped = columnsName.FirstOrDefault(c => c == columnSource.ColumnName);

                    if(columnTargetMapped == null)
                    {
                        continue;
                    }

                    dtTarget.Columns.Add(columnTargetMapped);

                    foreach (DataRow drSource in dtSource.Rows)
                    {
                        var valueColumn = drSource[columnSource];

                        DataRow drTarget = dtTarget.NewRow();

                        drTarget[columnTargetMapped] = valueColumn;

                        dtTarget.Rows.Add(drTarget);
                    }
                }
            }

            return dtTarget;
        }

Upvotes: 0

vendettamit
vendettamit

Reputation: 14687

You can simply copy the dataTable using dataTable.Copy() and remove the unwanted columns from the copied object. 

var dt1 = dt.Copy();
dt.Columns.Remove("<columnsToRemove>")

Upvotes: 1

Related Questions