CODEWITHSUNDEEP
c#
Johny Bravo
Johny Bravo

Reputation: 424

Select row from DataTable based on condition

I am trying to use Datatable Select but I am getting incorrect data

 double marks = 5; 
 DataRow[] result = dsGrades.Tables[0].Select("Convert(MarksFrom, 'System.Decimal') >=" + marks + " And " + marks + "<= Convert(MarksTo, 'System.Decimal') ");

dsGrades contains below data, enter image description here

when 'marks' contain '5.0', I am expecting row where MarksFrom = 5.0 and MarksTo = 5.9, as 5.0 falls in this range, but here it is returning 5 rows.

Whats wrong with datatable select? Any help is appreciated.

Upvotes: 1

Views: 10355

Answers (3)

Rohil Patel
Rohil Patel

Reputation: 468

Needs to do casting from datatable as below:

   DataRow[] drGrdPercntl = dt_GrdPercntil.Select($"{SubjVal} >= Convert(MarksFrom, 'System.Decimal') AND {SubjVal} <= Convert(MarksTo, 'System.Decimal')");

Upvotes: 0

Daniel Loudon
Daniel Loudon

Reputation: 799

You can do it like this: 

double marks = 5.0;
decimal newMarks = Convert.ToDecimal(marks);
var result = 
     dsGrades.Tables[0]
             .AsEnumerable()
             .Where( dr => dr.Field<decimal>( "MarksFrom" ) >= newMarks 
                     && dr.Field<decimal>( "MarksTo" ) < newMarks + 1);

This could be the solution:

var result = dsGrades.Tables[0].Select("Convert(MarksFrom, 'System.Decimal') >=" + newMarks + " And Convert(MarksTo, 'System.Decimal') < " newMarks + 1);

From my comment on question explaining problem:

Getting all the rows where MarksFrom is above 5 will return the first 5 visible rows in table, checking the second condition for these 5 rows and 5.0 is less than or equal to MarksTo in each of the rows so this would evaluate true for these rows. Therefore grabbing 5 rows

Upvotes: 2

Parrish Husband
Parrish Husband

Reputation: 3178

If would make sense to change your DataColumn types to double, however even with decimal you don't need a conversion inside the expression.

Note in your provided example, your constraint appears to be backwards. You're specifying that you want MarksFrom greater or equal to the passed in amount, which won't return a single row in the range you want.

This should return a single row for any mark passed in: 

double marks = 5.0; 
DataRow[] result = dsGrades.Tables[0].Select($"{marks} >= MarksFrom AND {marks} <= MarksTo");

Also since you're always only expecting a single match, you could change this to:

DataRow match = table.Select($"{marks} >= MarksFrom AND {marks} <= MarksTo").SingleOrDefault();

SingleOrDefault will throw an InvalidOperationException if more than one result is returned, which may be the desired outcome in this case.

Upvotes: 2

Related Questions