Remove Column from Dataset having same row values

Question

ID   Name      Value
 ......................
 1     aa      123
 2     bb      123
 3     cd      123

Wanted to remove column "value" which has all the row values equal to 123 from Dataset using linq

http://stackoverflow.com/questions/18854395/how-to-delete-rows-from-datatable-with-linq — Thirisangu Ramanathan, Oct 20 '14 at 13:24
Do you want to remove the column from the table if all values are the same? — Tim Schmelter, Oct 20 '14 at 13:33
wanted to find and delete the column were all the values are same in that column. — Ziyad Ahmad, Oct 20 '14 at 13:51

Tim Schmelter · Answer 1 · 2014-10-20T14:12:28.790

If you want to delet the whole column if all values are the same use Enumerable.All, for example in:

foreach(DataTable dt in ds.Tables)
{
   if(dt.Rows.Count > 0 && dt.Columns.Contains("Value") && dt.Columns["Value"].DataType == typeof(int))
   {
       int firstValue = dt.Rows[0].Field<int>("Value");
       if(dt.AsEnumerable().Skip(1).All(r => r.Field<int>("Value") == firstValue))
       {
           dt.Columns.Remove("Value");
       }
   }
}

Update: "wanted to find and delete the column were all the values are same in that column."

Then you just have to generalize above code:

foreach (DataTable dt in ds.Tables)
{
    List<DataColumn> columnsToDelete = new List<DataColumn>();
    foreach (DataColumn col in dt.Columns)
    {
        object first = dt.Rows[0][col];
        if (dt.AsEnumerable().Skip(1).All(r => r[col].Equals(first)))
        {
            columnsToDelete.Add(col);
        }
    }
    foreach (DataColumn colToRemove in columnsToDelete)
        dt.Columns.Remove(colToRemove);
}

score 0 · Answer 2 · answered Oct 20 '14 at 13:43

0

Here return in list of datarow

ds.Tables["tableName"].AsEnumerable().Where(g => g.Field<Int32>("Value") != 123).ToList<DataRow>();

answered Oct 20 '14 at 13:43

pradeep varma

136
3

score 0 · Answer 3 · edited May 23 '17 at 11:57

0

Worked with the solution given by SLaks https://stackoverflow.com/a/1766950/848286

foreach (var column in ds.Tables[0].Columns.Cast<DataColumn>().ToArray())
        {
            if (ds.Tables[0].AsEnumerable().All(dr => dr.IsNull(column)))
            {
                ds.Tables[0].Columns.Remove(column);
            }
        }

edited May 23 '17 at 11:57

Community

1
1

answered Oct 20 '14 at 14:12

Ziyad Ahmad

527
2
6
18

But it seems not to do what you want, does it? _"wanted to find and delete the column were all the values are same in that column."_ This just checks if the dataset's first table's first column contains only `123`. So if it all are `111` the column is not removed. You have't mentioned either that you only what to check a single column of one table. Please specify your requirement more precisely next time. – Tim Schmelter Oct 20 '14 at 14:16
This iterates through the columns and check if all the row values are same. then deletes that column – Ziyad Ahmad Oct 20 '14 at 14:48
But it does not even compile since `dr[column]` returns `object` whereas `123` is an `int`. You cannot compare both with `==`. – Tim Schmelter Oct 20 '14 at 14:52

Remove Column from Dataset having same row values

3 Answers3