Splitting a Datatable based on Column value

Question

I have a datatable containing a Bool(bit) Column.

  a         b
------    ------ 
  1         10
  0         20
  1         30
  0         20
  1         10

I wish to split this into 2 independently sortable datatables based on this value (column a)

I've had a look and found an old question:

Split a DataTable into 2 or more DataTables based on Column value

Which gave this:

List<DataTable> result = myDataTable.AsEnumerable()
        .GroupBy(row => row.Field<Boolean>("a"))
        .Select(g => g.CopyToDataTable())
        .ToList();

But the result isn't a list of datatables I can refer to using result[0] and result[1] Like I'd expected.

When I hit result[1] I get:

"Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index"

I'd add a comment to ask but the thread is well over 2 years old and I'm afraid I'd not get a response.

Can anyone advise of a way to achieve what I'm needing either using this code or something fresh?

It's not clear why the approach you have shown doesn't work. The result _is_ a `List`. So the first table is `result[0]` and the second table is `result[1]`. It never contains more than two tables. — Tim Schmelter, Jun 18 '15 at 11:13
Do you mean the order of the tables is not correct - you could order the linq query. — PaulF, Jun 18 '15 at 11:17
I mean when I try to access results[0] I get the expected shortened list of results. When I hit results [1] I get "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index" — Spitfire2k6, Jun 18 '15 at 11:20
@Spitfire2k6: you get that exception only if the list contains only one table and that is the case if the rows have all the same column-a-value. — Tim Schmelter, Jun 18 '15 at 11:26

Tim Schmelter · Answer 1 · 2015-06-18T11:31:02.933

Your result is a List<DataTable>. So the first table is result[0] and the second table is result[1]. It never contains more than two tables.

It can contain

0 tables if the source-table was empty
1 table if all had the same value or
2 tables if both values were contained.

From your comment:

When I try to access results[0] I get the expected shortened list of results. When I hit results [1] I get "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index"

You get that exception if the list contains only one table and that is only the case if the rows have all the same column-a-value(f.e. all have 1).

But you could use another approach if you want. This uses a Lookup<TKey, TValue>:

var aColLookup= myDataTable.AsEnumerable().ToLookup(row => row.Field<int>("a"));

DataTable trueTable = myDataTable.Clone();
DataTable falseTable = myDataTable.Clone();
if(aColLookup[1].Any())
    trueTable = aColLookup[1].CopyToDataTable();
if (aColLookup[0].Any())
    falseTable = aColLookup[0].CopyToDataTable();

I have to use Clone(creates an empty table with the same columns) and the Any-check because CopyToDataTable throws an exception if the sequence contains no rows.

score 1 · Accepted Answer · answered Jun 18 '15 at 11:20

1

considering dt datatable has all the records including both the columns, you can create two DataView with your filter condition like

EnumerableRowCollection<DataRow> query =
    from data in dt.AsEnumerable()
    where data.Field<bool>("a") == true 
    select data;

DataView view1 = query.AsDataView();



EnumerableRowCollection<DataRow> query1 =
    from data in dt.AsEnumerable()
    where data.Field<bool>("a") == false 
    select data;

DataView view2 = query1.AsDataView();

answered Jun 18 '15 at 11:20

Rahul

76,197
13
71
125

Now that's what I call an answer! Cheers bud :D – Spitfire2k6 Jun 18 '15 at 11:25
I have one datatable and would like to split in half by the number of columns. Please help. http://stackoverflow.com/questions/31271259/how-to-split-a-datatable-in-two-separate-by-columns – SearchForKnowledge Jul 07 '15 at 14:28
1

@SearchForKnowledge, have posted an answer in your question thread. See if that helps. – Rahul Jul 08 '15 at 06:46

score 0 · Answer 3 · answered Feb 06 '19 at 08:17

0

 Globals.Productsdt = Pfdt.AsEnumerable()
                                            .Where(r => r.Field<string>("basecurve") == PFlist[j].PFBC.ToString())
                                            .Where(r => r.Field<string>("diameter") == PFlist[j].PFDM.ToString())
                                            .CopyToDataTable();

answered Feb 06 '19 at 08:17

Varun Tej Reddy Putchakayala

21
1

Note that `CopyToDataTable()` throws an exception of `The source contains no DataRows` if the query returns no rows. – Kevin Swann Dec 17 '21 at 21:26

Splitting a Datatable based on Column value

3 Answers3