2

Inside my SQL table, i have a version column with string datatype which contains typical version values like '1.0.0' and '2.0.2' and so on.

I want to write a query which retrieve all the records with the version lower/greater than the specified version.

For example, suppose my table is like this: 

                 ====MY TABLE====
  version            column2     column3  , ....
_________________________________________________
   2.0.6               ...         ...       ...
   2.0.5               ...         ...       ...
   2.0.4               ...         ...       ...
   2.0.3               ...         ...       ...

Now i want to retrieve all the records with version lower and equal to 2.0.5

My main issue is that the input (2.0.5 in this case) and the version column values are both strings. So they cannot be compared normally.

I've tried : 

from o in MyTable.Where(w=> float.Parse(w.version) <= float.Parse(inputVersion) )
/* Other codes ommited for clarity */

But obviously it throws an exception because 2.0.3 is not a float.

There is a method that compares two version strings but i cannot use it inside a Linq2Sql query since it throws an exception saying

"Method X has no supported translation to SQL."

Also i've tried: 

from o in MyTable.Where(w=> Version.Parse(w.version) <= Version.Parse(inputVersion) )
/* Other codes ommited for clarity */

But it throws the same exception as well.

So my question would be how can i compare these values inside a linq to sql query?

Community
  • 1
  • 1
Sobhan
  • 1,051
  • 2
  • 13
  • 29

2 Answers2

1

Firstly, we need to replace . with Empty string, and then pare it to Float:

var allVersions = MyTable.ToList();
var filter = from o in allVersions
    .Where(w => Convert.ToInt32(w.version.Replace('.', '')) <= Convert.ToInt32(inputVersion.Replace('.', '')));

(I have always used Entity Framework and such thing s works on EF only with the help of some Canonical Functions. So I have told you to get all datas and then filter in the application memory. But it is worth to check this query directly in Linq to SQL, because after some investigation on Google it seems that converting data types and replacing operation are supported in Linq to Sql.)

Update:

I didn't use LinqToSQL, but this code could also run in database, so don't forget to check that also without retrieving all datas:

var filter = from o in MyTable
    .Where(w => Convert.ToInt32(w.version.Replace('.', '')) <= Convert.ToInt32(inputVersion.Replace('.', '')));
Farhad Jabiyev
  • 26,014
  • 8
  • 72
  • 98
  • What if the table had 10 million rows? I definitely wouldn't be loading the entire table and then processing them in the application. – connectedsoftware Oct 04 '15 at 07:40
  • 1
    Thank you @FarhadJabiyev, Loading the entire table first and then filtering it is certainly an option and would work well with small tables but if there is a way to directly pull out the records then it is the way. I tested your updated code but it seems `float.Parse()` inside a LinqToSql query throws an exception: `Method 'Single Parse(System.String)' has no supported translation to SQL.` `Convert.ToInt32()` along side `Replace` worked well. – Sobhan Oct 04 '15 at 07:55
  • 1
    @Sobhan Thanks, also I am glad that you marked the other answer. Because I didn't know that `float.parse` didn't support. As I told I am not using Linq to SQL much. – Farhad Jabiyev Oct 04 '15 at 07:57
1

You can use Replace and Convert.ToInt32(), these get translated in Linq2Sql which saves you having to load the entire table first and then process later.

This query will execute directly against the database and return only the matching data:

var matchingResults = from o in MyTable.Where(w=> Convert.ToInt32(w.version.Replace('.', '')) <= Convert.ToInt32(inputVersion.Replace('.', '')));

connectedsoftware
  • 6,987
  • 3
  • 28
  • 43
  • 1
    This doesn't work if versions don't have same number of number. Ex. 7.5.2.66 vs 8.0.1.2 - Compare 75266 vs 8012. – Kipotlov Jul 07 '16 at 17:55