DataTable as IEnumerable to improve performance in C#

Question

In our application, to improve the performance of filtering the rows in DataTable. We modified the below code to get IEnumerable.

DataRow[] drow = ds.Tables[0].Select("ID ='" + id + "'");

To

DataRow[] drow =
                        (
                        from item in ds.Tables[0].AsEnumerable()
                        where item["ID"].ToString() == id
                        select item
                        ).ToArray();

It was successful and working fine. Note: ds is a dataset object.

We have another scenario to filter the rows based on the condition like

DataRow[] maxBalRow = ds.Tables[0].Select("BALANCE = MAX(BALANCE)");

Here we are unable to resolve as the condition contains "MAX" DB function.

I doubt that the LINQ query is more efficient than your `Select` call — Tim Schmelter, Jan 11 '17 at 12:53

score 2 · Answer 1 · edited Jan 11 '17 at 13:51

2

You could transform the DataTable into the array with (suggestion of Tim Schmelter on comments):

DataRow[] drow = ds.Tables[0].Select();

After that, you could perform your queries, for sample:

DataRow item = drow.FirstOrDefault(row => row["ID"] == id);

and

// get the max balance first
double maxBalance = drow.Max(row => (double)row["BALANCE"]);

// search for items with the max balance
DataRow[] item = drow.Where(row => row["BALANCE"] == maxBalance).ToArray();

edited Jan 11 '17 at 13:51

Jeroen van Langen

21,446
3
42
57

answered Jan 11 '17 at 12:53

Felipe Oriani

37,948
19
131
194

1

If you need a `DataRow[]` of all rows of a `DataTable` this is the most efficient way: `DataRow[] drow = ds.Tables[0].Select();`. But you can start processing the table with LINQ queries without an array, use `DataTable.AsEnumerable()` – Tim Schmelter Jan 11 '17 at 12:55
Thanks Tim Schmelter and Felipe Oriani for the response, yes we want to use DataTable.AsEnumerable(). But in the above suggested solution there are three steps. one is to get all rows from table, second is to find out Max value and last step is to compare. I think, this will hit the performance ? Am I correct ? Please let me know. And is there any other solution to handle this ? – Sri Jan 11 '17 at 13:21

score 0 · Answer 2 · answered Jan 11 '17 at 13:15

0

Another solution can be to order the table in asc order with respect to the field 'BALANCE' and select the last row.e.g

   DataRow[] myrows= ds.Tables[0].Select("","BALANCE ASC");
   DataRow rowtoget=myrows[myrows.Length-1];

Hope it helps.

answered Jan 11 '17 at 13:15

SteveTheGrk

352
1
7

Jeroen van Langen · Answer 3 · 2017-01-11T13:55:50.020

I think looking for an ID, you should use the table.Rows.Find() method, which is way faster than a table scan.

ds.Tables[0].Rows.Find(id);

Searching for the MAX(BALANCE) (and all matching rows), you have to do a tablescan. Instead of doing two scans (determine the max and compare them), you could combine them in one scan:

// list of balances.
var maxBalances = new List<DataRow>();

// initial value
double maxBalance = 0;

// check each row
foreach(var row in ds.Tables[0])
{
    // read the value
    var value = (double)row["BALANCE"];

    // if the value is higher than the previous balance, forget all previous selected rows.
    if(value > maxBalance)
    {
        // set the new maxBalance value.
        maxBalance = value;

        // clear the list
        maxBalances.Clear();
    }

    // if the value == maxBalance, add it to the list.
    if(value == maxBalance)
        maxBalances.Add(row);
}

// maxBalances contains all rows who share the highest balance.

DataTable as IEnumerable to improve performance in C#

3 Answers3