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!
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>")
copy() method is Microsoft's API assuming they benchmarked it for performance. In terms of memory if you have one datatable loaded in memory creating a copy will double it up. So instead of loading the first dataTable into memory create a datareader and directly create your second table from it. 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;
}
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();
}
