Search for an integer item in a datatable with string column values

3
votes

I have a datatable where column datatype is string. 

DataTable dt = new DataTable();
dt.Columns.Add("LowerRange", typeof(string));
dt.Columns.Add("UpperRange", typeof(string));
DataRow dr = dt.NewRow();
dr["LowerRange"] = "1,2,3,4,5"; 
dr["UpperRange"] = "55"; 
dt.Rows.Add(dr);

dr = dt.NewRow();
dr["LowerRange"] = "6,7,8,9,10";
dr["UpperRange"] = "66";
dt.Rows.Add(dr);

I have a list of integers.

List<int> lstEff = new List<int>() { 4,5,6,7 };

Among the items in the integer list I want to find out the column value in datatable where there are one or more matches in list. And the lowest match has to be taken.

In this case I should get the row number corresponding to "1,2,3,4,5".

I cannot use Contains since if the value in row is 55 and I am searching for 5, it will return true. I want to do integer search using linq.

I am new to linq. I tried something like this but there is an error.

foreach (int i in lstEff)
{
    dt.AsEnumerable().Where(c => c.Field<string>("LowerRange").Split(',').ToList<int>().Contains(i.ToString()));
}
1
c#linqdatatable
At a first glance, should be Split(',') with quotes - Pikoh
Yea.. Error in converting string to list of integers.. Split(',').ToList<int>() this part I think - Manoj Nayak
What error you are getting? - Chetan

1 Answers

1
votes

String.Split gives you a list of strings. You should convert their values from strings to ints and compare afterwards. Convert to list first and you'll get the rowindex via FindIndex: 

var rowNo=
    dt.AsEnumerable().ToList().FindIndex(c => c.Field<string>("LowerRange").Split(',').Select(rangeItemStr=>int.Parse(rangeItemStr)).Any(rangeItem=>lstEff.Contains(rangeItem)));

To get both row and rowIndex, I would recommend sth like this

var rows= dt.AsEnumerable().ToList();
var rowIndex = rows.FindIndex(c => c.Field<string>("LowerRange").Split(',').Select(rangeItemStr=>int.Parse(rangeItemStr)).Any(rangeItem=>lstEff.Contains(rangeItem)));
var row = rows[rowIndex];