How to get the highest value from a datatable?

Question

I have a datatable which has a few columns. I want to return the one which matches my id, with the highest value in the verlabel column, in this case I want to return 1.1

Datatable structure

How do I achieve this? Current code just returns the column name into my string variable, rather than the actual value.

Namespace.EnquiryEngine.DataLists ds2 = new Namespace.EnquiryEngine.DataLists("VERSION");
Namespace.Common.KeyValueCollection ds2params = new Namespace.Common.KeyValueCollection();
ds2params.Add("id", class.ID); 
ds2.ChangeParameters(ds2params);
System.Data.DataTable dt = ds2.Run(false) as System.Data.DataTable;
String version = Convert.ToString(dt.Rows[0]["version"]);

New code:

var highestVersion = (from row in dt.AsEnumerable() 
                  where row.Field<int>("id") == doc.ID
                  select row.Field<double>("verlabel")).Max();

Have a look at [linq to datatable](http://stackoverflow.com/questions/10855/linq-query-on-a-datatable) — Gilad Green, Oct 11 '16 at 13:39

score 3 · Answer 1 · answered Oct 11 '16 at 13:59

Another option will result in DataRow, from which any relevant Column can be selected, provided its not Null

var dataRow =   dt.AsEnumerable()
                            .Where(row => row.Field<string>("id") == class.ID)
                            .OrderByDescending(row => row.Field<double>("verlabel"))
                            .FirstOrDefault();

var result = dataRow?["ColumnName"]

score 3 · Answer 2 · answered Oct 11 '16 at 13:59

3

Use the following code:

var res = dt.AsEnumerable().where(a => a.Field<String>("id") == class.ID).Select(b => b.Field<double>>("verlabel").Max();

answered Oct 11 '16 at 13:59

Green Falcon

818
3
17
48

score 2 · Accepted Answer · answered Oct 11 '16 at 13:42

2

Haven't tried it myself but probably something along:

var highestVersion = (from row in dt.AsEnumerable()
                      where row.Field<string>("id") == class.ID
                      select row.Field<double>("verlabel")).Max();

answered Oct 11 '16 at 13:42

Gilad Green

36,708
7
61
95

This will result in `double` not `DataRow` as expected – Mrinal Kamboj Oct 11 '16 at 13:55
@MrinalKamboj - the expected is actually a string (`String version`) but I decided to keep in on double. He can add `ToString()` if needed :) – Gilad Green Oct 11 '16 at 13:56
1

I like the look of this answer - simple and to the point. Unfortunately, I'm getting exceptions about `System.Data.EnumerableRowCollection` not containing a definition for `max`. Any ideas? – Syntax Error Oct 11 '16 at 13:59
@jbab8 - are you `using System.Linq`? – Gilad Green Oct 11 '16 at 14:01
@jbab8, also beware of the types used for `id` and `verlabel` columns, since they are based on certain assumptions and may not be totally correct – Mrinal Kamboj Oct 11 '16 at 14:01
@GiladGreen Without that it would fail much earlier than `Max`, it is Linq All through – Mrinal Kamboj Oct 11 '16 at 14:02
@MrinalKamboj - incorrect. I've just removed the `using System.Linq` from my code and got the same compile error that he is receiving. And I got it on the `.Max()` – Gilad Green Oct 11 '16 at 14:04
I'm using `system.linq` and have amended `id` to be an `int` rather than string. Still getting the missing definition for `max` though! So close! – Syntax Error Oct 11 '16 at 14:04
@jbab8 - can you edit your question to add the new version of the code? So I can copy it and check? – Gilad Green Oct 11 '16 at 14:05
@GiladGreen I've added the new version – Syntax Error Oct 11 '16 at 14:07
@GiladGreen Alright for `DataTable` its different, since its part of an Extension class in `System.Data`, only Linq code is Max in your case and None in my case. Nonetheless +1 from my side, its a good solution. – Mrinal Kamboj Oct 11 '16 at 14:09
@jbab8 - Saw the new version. Sorry to ask more but can you add a print screen showing it.. having the code + the using? because it is strange.. – Gilad Green Oct 11 '16 at 14:10
@GiladGreen Unfortunately not because this is part of an enterprise application, which is closed source. It's against our licence agreement to post any code, even custom stuff, hence me using placeholders like "Namespace" and "class" and removing a lot of other things (none of which affect this bit directly btw). It may be that it's not 100% using LINQ to talk to this data source, which is why even after importing the assembly / reference it still doesn't like it. I may contact the company for an alternative method! – Syntax Error Oct 11 '16 at 14:14
@jbab8 - I'm pretty sure that answer will be no but worth checking - do any other linq methods give you the problem? and do you have the problem with `AsEnumerable()`? – Gilad Green Oct 11 '16 at 14:17
Here `AsEnumerable()` belongs to `System.Data.DataTableExtensions`, No Linq – Mrinal Kamboj Oct 11 '16 at 14:18
@MrinalKamboj - I know and that is what I want him to check :) from what I see the only using my snippet needs is `linq` and `data`. – Gilad Green Oct 11 '16 at 14:20
I had problems with `AsEnumerable` until I imported the `System.Data.DataTableExtensions` assembly! I'm having problems with other examples posted, things like `FirstOrDefault()` are missing as well. – Syntax Error Oct 11 '16 at 14:23
Yes as @GiladGreen suggested you are surely missing `System.Linq`, check both assembly and namespace import – Mrinal Kamboj Oct 11 '16 at 14:31
@jbab8 - I'm not sure.. maybe something stupid like resetting VS... I'm just guessing now haha – Gilad Green Oct 11 '16 at 14:31
@MrinalKamboj - Thanks :) – Gilad Green Oct 11 '16 at 14:31
1

@GiladGreen I have kinda given up now. I've no doubt your solution (and yours, Mrinal) would work in any other environment. I think it's just this application that maybe needs some bespoke method to be invoked. I will investigate. Thank you both though – Syntax Error Oct 11 '16 at 14:37
@jbab8 - You are welcome :) When you solve it please let us know if possible - it is interesting – Gilad Green Oct 11 '16 at 14:40
1

When on windows always keep system restart and Visual studio restart as possible options in the armour, always handy, all the very best :) – Mrinal Kamboj Oct 11 '16 at 14:45
1

@GiladGreen It appears to be an issue with the vendor's SDK, rather than the code itself. I successfully tested it outside of the SDK without an issue as did the vendor... – Syntax Error Oct 11 '16 at 15:26

How to get the highest value from a datatable?

3 Answers3