Comparing two strings containing multiple dots inside a Linq to Sql query

Question

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?

Version.Parse doesn't have a translation into SQL - simply use the Replace function to do this. — connectedsoftware
– connectedsoftware, Commented Oct 4, 2015 at 7:39

connectedsoftware · Accepted Answer · 2015-10-04 07:26:29Z

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('.', '')));

answered Oct 4, 2015 at 7:26

connectedsoftware

7,0973 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kipotlov Over a year ago

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.

Farhad Jabiyev · Accepted Answer · 2015-10-04 07:56:05Z

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('.', '')));

edited Oct 4, 2015 at 7:56

answered Oct 4, 2015 at 7:15

Farhad Jabiyev

26.9k8 gold badges79 silver badges103 bronze badges

3 Comments

connectedsoftware Over a year ago

What if the table had 10 million rows? I definitely wouldn't be loading the entire table and then processing them in the application.

Sobhan Over a year ago

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.

Farhad Jabiyev Over a year ago

@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.

Collectives™ on Stack Overflow

Comparing two strings containing multiple dots inside a Linq to Sql query

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related