.Net - Find max value in DataTable

Question

I have a .net DataTable having various columns. I want to find max value stored in across all rows/table. Currently I am looking through all rows and columns, is there any easier way to find the max value (perhaps using LINQ)?

Current code:

double maxValue = 0;

foreach (DataRow dr in dataTable.Rows)
{
    foreach (DataColumn dc in dataTable.Columns)
    {
        if (dc.ColumnName != "Index" && dr[dc.ColumnName] != DBNull.Value)
        {
            maxValue = Math.Max(maxValue, Convert.ToDouble(dr[dc.ColumnName]));
        }
    }
}

Use Column-oriented DBMS will be the best choice. https://en.wikipedia.org/wiki/Column-oriented_DBMS — David, Jul 05 '19 at 10:42
Side note: you may want `double maxValue = double.NegativeInfinity;` instead of `double maxValue = 0;` — Dmitry Bychenko, Jul 05 '19 at 10:51

Dmitry Bychenko · Accepted Answer · 2019-07-05T11:06:21.470

3

Technically, you can convert loops into Linq like this:

  var maxValue = dataTable
   .Rows
   .OfType<DataRow>()
   .SelectMany(dr => dataTable
      .Columns
      .OfType<DataColumn>()
      .Where(dc => dc.ColumnName != "Index" && dr[dc.ColumnName] != DBNull.Value)
      .Select(dc => Convert.ToDouble(dr[dc.ColumnName])))
   .Aggregate(0.0, (s, a) => Math.Max(s, a));

Or if dataTable has at least one non negative value you can turn Aggregate into Max

  var maxValue = dataTable
   .Rows
   .OfType<DataRow>()
   .SelectMany(dr => dataTable
      .Columns
      .OfType<DataColumn>()
      .Where(dc => dc.ColumnName != "Index" && dr[dc.ColumnName] != DBNull.Value)
      .Select(dc => Convert.ToDouble(dr[dc.ColumnName])))
   .Max();

But I doubt if it's simpler or easier to read than original loops

edited Jul 05 '19 at 11:06

answered Jul 05 '19 at 10:49

Dmitry Bychenko

180,369
20
160
215

Interesting, I agree the original loop will be much simpler to read. Thanks for the answer. – developer Jul 05 '19 at 10:54
Nice answer (upvoted). However, if we manage to separate the function from its use-case, then we get even more benefits. – Lajos Arpad Jul 05 '19 at 11:05

score 1 · Answer 2 · answered Jul 05 '19 at 11:03

We have several concepts of maximum here. We intend to get the maximum value of all rows and all columns, except for Index and for null values. From this perspective, we calculate the maximum of maximums, that is, each record has a maximum value and we intend to find the maximum value of these maximums. So, we need to implement the calculation of the maximum value of a record as a custom function and then write a LINQ selection to calculate the maximum of these maximums. The outer usage is simple, but what do we put inside:

myQueryableVariable.Select(/*Here comes the magic*/).Max()

Very nice. So, what will the magic be? You can of course write a custom function. You can also use the dynamic query library. You will need to implement a function which will be evaluated for each record. Is this a simpler approach? It is simpler to read, but more difficult to implement, however, only at the first time. The real power of this approach is that it separates the function from its use-case, which:

makes it reusable at different clauses in very different ways
makes the function easily replaceable

Reusing at different clauses is extremely good, as you can use a Where condition for example to check whether the maximum value of column values for a given row is bigger than 10, for example.

Also, you can replace your function. If you need to similarly calculate something else, like the Minimum, all you need to do is to implement the function for minimum and use it instead.

I do not have a .NET environment installed since years, so I cannot test codes written, so if there are any problems with this answer, please let me know about them in the comment section.

score 0 · Answer 3 · answered Jul 05 '19 at 12:00

Assuming there are any non-DBNull values that are not in the Index column :

double maxValue = dataTable.Rows.Cast<DataRow>().Max(r => r.ItemArray.Where((o, i) => 
        i != dataTable.Columns["Index"].Ordinal && o != DBNull.Value).Max(Convert.ToDouble));

.Net - Find max value in DataTable

3 Answers3