none
Datatable query by boolean first followed by MAX value RRS feed

  • Question

  • This is spinning me out, I'm sure its something simple...

    Basically I have a Datatable with a some data similar to the following

    Name, Price, New, Location

    item1, 6, True, AB
    item2, 5, False, AB
    item1, 7, True, AB
    item1, 10, False, AB

    I'm trying to use a simple Datarow Select to find the highest price but only if New is True, is there a way to do this similar to a SQL WHERE?

    Monday, April 30, 2018 8:29 PM

All replies

  • https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    http://www.codecompiled.com/query-datatable-using-linq-in-csharp/

    var max = (from a in datatable.AsEnumerable()
    where item.Field<boolean>("New") == true
    select a).Max();
    Monday, April 30, 2018 9:56 PM
  • Hi DGC GHOST,

    We could use LINQ to achieve it, and the following code for your reference.

    DataTable dt = new DataTable();
                dt.Columns.Add("Name", Type.GetType("System.String"));
                dt.Columns.Add("Price", Type.GetType("System.Double"));
                dt.Columns.Add("New", Type.GetType("System.Boolean"));
                dt.Columns.Add("Location", Type.GetType("System.String"));
    
                DataRow dr = dt.NewRow();
                dr["Name"] = "item1";
                dr["Price"] = 6;
                dr["New"] = true;
                dr["Location"] = "AB";
    
                dt.Rows.Add(dr);
                dr = dt.NewRow();
                dr["Name"] = "item2";
                dr["Price"] = 5;
                dr["New"] = true;
                dr["Location"] = "AB";
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr["Name"] = "item1";
                dr["Price"] = 7;
                dr["New"] = true;
                dr["Location"] = "AB";
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr["Name"] = "item1";
                dr["Price"] = 10;
                dr["New"] = false;
                dr["Location"] = "AB";
                dt.Rows.Add(dr);
    
                var max = (from row in dt.AsEnumerable()
                           where (bool)row["New"] == true
                           orderby (double)row["Price"] descending
                           select row).FirstOrDefault();

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 1, 2018 2:53 AM
    Moderator
  • Similar to Zhanglong's approach.

    var dt = LoadTable();
    
    //Filter to the rows you care about using LINQ syntax
    var rows = from r in dt.AsEnumerable()
                where r.Field<bool>("New")
                select r;
    
    //Alternative approach using only methods
    //var rows = dt.AsEnumerable().Where(r => r.Field<bool>("New"));
    
    //Now get the max price
    var maxPrice = rows.Max(r => r.Field<decimal>("Price"));

    Note that I assume that the price is a decimal as that is what you should be using for monetary values.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, May 1, 2018 1:48 PM
    Moderator
  • Try the next Select too:

       DataRow foundRow = myDataTable.Select( "New=true", "Price DESC" ).FirstOrDefault();

    Then you can do:

       if( foundRow != null )

       {

          decimal highest_price = foundRow.Field<decimal>( "Price" );

          // . . .

       }


    • Edited by Viorel_MVP Tuesday, May 1, 2018 4:29 PM
    Tuesday, May 1, 2018 4:23 PM
  • A simple way. 

    The datatable has build in a DataView (which is a filter which can be set) the name of it is the property DefaultView

    Therefore you can use it as 

    OldTable.Defaultview.RowFilter = "Expression";
    var NewTable = OldTable.DefaultView; 

    https://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter(v=vs.110).aspx

    The expression rules is also with a link on that page.


    Success
    Cor

    Tuesday, May 1, 2018 4:36 PM