Linq to select a specific row from a local dataTable (winform VS2012)

Question

_{Monday, June 16, 2014 3:27 PM}

the local DataTable1 contains the following columns and data.

DataTable1
Rank      Items

 1           itm1
 3           itm2
 7           itm3
 12         itm4
 15         itm5

In a user form a user enters the value 11 into a textbox and needs to retrieve a row from DataTable1 where the rank is closest to or equal to but not greater than the entered value 11.  In the sample data above that would be the row where rank = 7. 

What would a link query look like to perform this operation?  Can I perform Linq directly on DataTable1 or would I want to retrieve the rank values into a list and then perform the Linq Query on the list?

Any suggestions greatly appreciated.

Rich P

All replies (3)

_{Monday, June 16, 2014 4:18 PM ✅Answered}

Just another approach and correction to the Brian's answer.

var item = (from dr in dt.Select()
            where (int)dr["Rank"] <= rankValue
            select dr)
            .Max(dr => (int)dr["Rank"]);

Brian, DataTable does not gives you rows list. As usual there are many approaches. One is using Select method.

from dr in dt.Select()

Another one is using explicit type specification for Rows collection

from DataRow dr in dt.Rows

Also SingleOrSelect will throw exception if more than one item satisfies the condition.

So you should use FirstOrDefault

Corrected code would be like

var item = (from DataRow dr in dt.Rows
            where (int)dr["Rank"] <= rankValue
            orderby (int)dr["Rank"] descending
            select dr).FirstOrDefault();

Muthukrishnan Ramasamy
net4.rmkrishnan.net
Use only what you need, Reduce global warming

_{Monday, June 16, 2014 3:34 PM}

something like:

var item = (from d in DataTable 
where d.Rank <= rankvalue
orderby d.Rank descending
select d.Items).SingleOrDefault();

_{Monday, June 16, 2014 5:34 PM}

Thank you all for the replies. I tried both methods suggested. The method using the datarow worked, but the method which referenced the DataTable (will guess DataTable1) got an error where it said DataTable.  Here is my implementation of the two suggested methods and the resuts

DataTable dt = new DataTable();

dt.Columns.Add("rank", typeof(int));
dt.Columns.Add("Color", typeof(string));

DataRow dr = dt.NewRow();
dr["rank"] = 1;
dr["Items"] = "itm1";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["rank"] = 3;
dr["Items"] = "itm2";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["rank"] = 7;
dr["Items"] = "itm2";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["rank"] = 12;
dr["Items"] = "itm3";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["rank"] = 15;
dr["Items"] = "itm4";
dt.Rows.Add(dr);

int rankValue = 11;

//--this method worked OK
var item = (from DataRow dr1 in dt.Rows
            where (int)dr1["rank"] <= rankValue
            orderby (int)dr1["rank"] descending
            select dr1).FirstOrDefault();

Console.WriteLine("{0} -- {1}", item[0].ToString(), item[1].ToString());

//
//--I was getting the red sqiggle line on dt with this sample
var item1 = (from d in dt
            where d.rank <= rankValue
            orderby d.rank descending
            select d.Items).SingleOrDefault();

--for this method -- is there anything I need to do with dt?  I tried surrounding it with [], but that did not help anything (made it worse).

Rich P