Compare input double with datatable string column values within a range using between query(SQL query to Linq on datatable c# )

Question

Compare input double with datatable string column values within a range using between query(SQL server query to Linq on datatable c# )

DataTable has below columns and all are strings.

Value From  | Value To |  %
1.00          1.50      10.6%
1.51          2.0       20.4%
2.01          2.50      25.5%

User given input value is double. I have to get the % column value from datatble where user given input matches in between the range 'Value From' and 'Value To'

Ex: If input is 1.81 then I should get 20.4% as a query result.
    If user gives 2.05 then I should get 25.5% as a query result.
    If user gives 1.35 then I should get 10.6% as a query result.

I'm able to get the same result with sql query from sql server stored procedure:
select % from [TestDatabase_GPA].[dbo].[AcadamicGPA] where @Acadamic_GPA between Value From and Value To

How can I convert SQL query to get same result from DataTable using Linq or select. Here the problem is I have to convert the datatable column datatype string to double and then perform between operation. I tried this Compare string values with double in DataTable.Select() but I didn't get required result. I'm new to Linq please share with me any sample code or any ideas would be appreciated.

Chaitanya · Answer 1 · 2016-06-14T18:58:54.603

2

As columns are string datatype, I have casted them to Double.As the input is said to be Double I haven't casted to Double

   var percentage = (from myRow in table.AsEnumerable()
                      where Convert.ToDouble(myRow.Field<string>("Value From")) <= Acadamic_GPA && Convert.ToDouble(myRow.Field<string>("Value To")) >= Acadamic_GPA
                      select myRow.Field<string>("%")).FirstOrDefault();

edited Jun 14 '16 at 18:58

answered Jun 14 '16 at 18:32

Chaitanya

171
1
7

All datatable columns are strings including 'Value From' and 'Value To' as I have 'Total' as a lastrow which is under 'Value To' column. So I can't make them double. I tried this but I received (Specified cast is not valid.) exception – himak Jun 14 '16 at 18:46
@priya777 Updated the answer – Chaitanya Jun 14 '16 at 18:52
Thank you for the update. Its working with minor data type tweak. What is the use of FirstOrDefault(). Here is the final version. (from row in finaExpeResult.AsEnumerable() where avgPoints >= Convert.ToDouble(row.Field("Value From")) && avgPoints <= Convert.ToDouble(row.Field("Value To")) select row.Field("%")).FirstOrDefault(); – himak Jun 14 '16 at 19:07

score 0 · Answer 2 · edited May 23 '17 at 12:10

0

Try this LINQ query:

from dataTable.AsEnumerable()  
where @Acadamic_GPA >= Convert.ToDouble(Value From) && @Acadamic_GPA <= Convert.ToDouble(Value To)
select %;

Reference

edited May 23 '17 at 12:10

Community

1
1

answered Jun 14 '16 at 18:29

Shaharyar

12,254
4
46
66

score 0 · Answer 3 · answered Jun 14 '16 at 18:30

The GetPercent method should give you the answer you need;

using System;
using System.Linq;

namespace ConsoleApplication8
{
class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine(GetPercent("1.68"));
        Console.ReadLine();
    }

    static string GetPercent(string val)
    {
        var vals = new[]
        {
            new {from = "1.00", to = "1.50", percent = "10.6%"},
            new {from = "1.51", to = "2.0", percent = "20.4%"},
            new {from = "2.01", to = "2.50", percent = "25.5%"}
        };

        return
            vals.Single(
                v =>
                    Convert.ToDouble(val) >= Convert.ToDouble(v.from) &&
                    Convert.ToDouble(val) <= Convert.ToDouble(v.to)).percent;
    }
}
}

You can test that it works using the application I provided but obviously this will need adapting to fit your solution.

Compare input double with datatable string column values within a range using between query(SQL query to Linq on datatable c# )

3 Answers3