How to delete first column in all datatables within a collection of datatables C#

Question

I have need to check for the data returned from a Sybase query to check the first column which corresponds to which spreadsheet a given set of data will be displayed. I can get the data correctly in my dataset but the trouble comes when I try and remove the grouped column.

My sample data is as follows:

public static DataTable JustDataTableWithSheetColumn()
{
    var sample = new DataTable();

    sample.Columns.Add("SomethingSheet", typeof(string));
    sample.Columns.Add("Column1", typeof(string));
    sample.Columns.Add("Column2", typeof(string));

    sample.Rows.Add("1", "Somestuff", "this other stuff");
    sample.Rows.Add("1", "Fluffy", "this stuff");
    sample.Rows.Add("2", "ToolShed", "this other stf");
    sample.Rows.Add("2", "FudgeCycles", "this oer stuff");
    sample.Rows.Add("2", "Crap", "thr stuff");
    sample.Rows.Add("2", "stuff and stuff", "thiuff");
    sample.Rows.Add("2", "test crap", "this  stuff");
    sample.Rows.Add("2", "dungheap", "this othuff");
    sample.Rows.Add("3", "people eater", "this other stf");
    sample.Rows.Add("3", "no purple people", "ths oth stff");

    return sample;
}

And the following properly groups and applies them to datatables:

var thing = DataTableExamples.JustDataTableWithSheetColumn();

if (thing.Columns[0].ColumnName.Contains("Sheet"))
{
    var test = from t in thing.AsEnumerable()
        group t by t.ItemArray[0]
        into g
        select g.ToList().CopyToDataTable();
}

I have tried without the .CopyToDataTable() to just have a collection of DataRows and tried to query those using Skip(1) but I think my order of operations is incorrect or I am within that nebula I sometimes get lost in with IEnumerable vs IQueryable and all those variations.

Like so:

datarow.ItemArray.Skip(1).ToArray();

I have also tried to loop through the datatable collection and use dt.RemoveAt(0) but I am unable to reference the manipulated datatable (or incorrectly doing so).

foreach (var dt in test)
{ 
    dt.Columns.RemoveAt(0);
}

Bottom line is that the first column on the data returned from the database doesn't need to be displayed or used once I translate them into datatables for later addition to the Dataset I return from this API.

So I am looking for an elegant way to get what I require with as little boxing and unboxing as possible. This part has to be done prior to adding it to the end Dataset because each of these weird stored procedures could potentially return a different datatable structure so I can't simply remove all of the first columns from the end dataset.

Thanks for your help.

Answer 1

dr.ItemArray.Skip(1).ToArray();

I suspect this (whatever dr is - it's not mentioned in your question) was used in the wrong place, and you ended up removing the column you wanted to group on, before the grouping was done

query those using Skip(1)

Skipping a datarow won't help you skip a column though - it just drops the first entire row of data

---

You can just drop the column after you prepare rather than getting into anything convoluted:

if (thing.Columns[0].ColumnName.Contains("Sheet"))
{
    var test = from t in thing.AsEnumerable()
        group t by t.ItemArray[0]
        into g
        select g.ToList().CopyToDataTable();

    foreach(var dt in test){
        dt.Columns.RemoveAt(0);
        endDataSet.Tables.Add(dt);
    }
}

Or you can manipulate the result dataset:

    //referencing known table names
    var dtToStrip = new[] { "ATableName", "BTableName" }
    foreach(DataTable dt in endDataSet.Tables)
      if(dtToStrip.Contains(dt.TableName))
        dt.Columns.RemoveAt(0);

    //or removing any column named sheet from any table in the set
    foreach(DataTable dt in endDataSet.Tables)
      if(dt.Columns.Count > 0 && dt.Columns[0].ColumName.Contains("Sheet"))
        dt.Columns.RemoveAt(0);

Answer 2

I believe I am a victim of the deferred execution in this case.

Originally, I was doing the following:

if (thing.Columns[0].ColumnName.Contains("Sheet"))
{
    var test = from t in thing.AsEnumerable()
        group t by t.ItemArray[0]
        into g
        select g.ToList().CopyToDataTable();

    foreach (var dt in test)
    { 
        dt.Columns.RemoveAt(0);
    }

    ds.Tables.AddRange(test.ToArray());
}

Which I mistakenly assumed was executing the query upon doing the first .ToList(). But it didn't get executed until I did the .ToArray() when adding it to the DataSet at the end. Which, if I understand this correctly, means that the executions within my foreach were all for naught because they were independent executions.

If I instead move that .ToArray() to the LINQ query first it seems to work:

if (thing.Columns[0].ColumnName.Contains("Sheet"))
{
    var test = (from t in thing.AsEnumerable()
        group t by t.ItemArray[0]
        into g
        select g.ToList().CopyToDataTable()).ToArray();

    foreach (var dt in test)
    { 
        dt.Columns.RemoveAt(0);
    }

    ds.Tables.AddRange(test);
}

Thanks for the help.

Please let me know if my conclusions are incorrect or if I missed some logic as some point that would make this more helpful for other devs having a similar issue.