Use a NaturalSortComparer in a LINQ Where Clause

Question

Say I have a table Table1 with a string field [ProductString] with values: Alpha, alphanumeric or numeric: eg ABC, B4, U2, C 5, 100, U1, U5, U6, U11

I want to be able to take a where clause like "ProductString >= U5", and pass this to a LINQ statement as a string so it evaluates

Table1.Where(t=> t.ProductString >= 'U5');

Normally this would return results U5 and U6.

However, this I want to be able to use a NaturalSortComparer somehow so that the results returned are U5, U6 and U11.

I know how to use the comparer in an OrderBy, by I wanted to be able to use it at the Where stage.

Answer 1

Using natural sort comparer:

var comparer = new NaturalComparer();
Table1.Where(t=> 
    comparer.Compare(t.ProductString, "U5") >= 0);

Presuming all your product strings is on the format U%number% then why not abuse that fact?

Table1.Where(t=> int.Parse(t.ProductString.Replace("U","")) >= 5);

If you're using LINQ to Entities I'm not certain this will compile to a store expression (i.e that SQL knows what to do with this - I guess it should).

Answer 2

I'm a little confused, given the accepted answer, about whether this question relates to LINQ to Entities or not. The accepted answer doesn't appear to be a solution that would work in the LINQ to Entities context, but the comments on the question by the OP seem to confirm that this is being executed in the database context. Anyway, this answer is specifically targeted toward LINQ to Entities.

I think doing this in SQL Server would be hard, but not impossible. The problem is that .NET knows what NaturalSortComparer is, but SQL Server (where you want the query to ultimately take place) has no such concept. The best idea I can think of would consist of 2 parts:

1. Create a UDF (User Defined Function) in SQL server that will give a product that is orderable via natural sort: CREATE FUNCTION Naturalize(@val as nvarchar(max)) RETURNS nvarchar(1000). There's a pretty cool answer here that creates a UDF wrapper around a CLR function to accomplish just that.

2. Next create a function mapping for your DbContext that maps the UDF above to a function that can be called inside an EF query against the DbContext. Something like this:

   [DbFunction("MyContext", "Naturalize")]
   public static string Naturalize(this string value)
   {
       throw new NotSupportedException("This function can only be invoked from LINQ to Entities.");
   }
   

Once you've got these two pieces in place, you can readily use this new function inside an entity query to compare strings using the Naturalized value in the comparison:

Table1.Where(t=> t.ProductString.Naturalize() >= "U5".Naturalize());

Bear in mind that the UDF will be executed against every row contained in the query, which is the whole table in the above example. You'll want to make sure to pare down your query to something manageable before applying the function as a sub-query. Or you may want to try applying some type of UDF-based index on the table in question.

Answer 3

If you are going to be doing searches like this a lot, then what will be the best thing to do is add two new fields to your table, [ProductCode] & [ProductNumber] which separate the two parts of the [ProductString].

Then you comparison becomes:

 Table1.Where(t=> t.ProductCode == "U" && t.ProductNumer > 5);