Regex in Where clause for string comparison

[Patrick]

Hello,

I'm trying to clean some strings in my Where clause before doing a comparison. I'm getting a System.NotSupportedException exception, with this message - Execution of 'System.Text.RegularExpressions.Regex:Replace(String,String,String)' on the database server side currently not implemented.

I tried using Telerik.OpenAccess.35.Extensions and Telerik.OpenAccess.40.Extensions, version 2013.2.702.1

Here is my code:

db.Customers.Where(c =>
Regex.Replace(c.Name, "[^0-9a-zA-Z]+", string.Empty).ToUpper() ==
Regex.Replace(name, "[^0-9a-zA-Z]+", string.Empty).ToUpper());

Any thoughts?

Thanks in advance!

[Viktor Zhivkov]

Hello Patrick,

The way you have tried to implemented "database server" side Regex processing is not going to work because there is no way to push pure .Net methods to SQL.
There is a way to achieve the same behaviour using some additional tools, but they are dependent on the relational server that you are targeting and it's version.
Please tell us which database server you are targeting and we will send you a step by step guide how to implement it using OpenAccess.

Additionally you may want to apply Regex transformations to the name parameter before pushing it into the LINQ query. It will save some time during execution.

Regards,
Viktor Zhivkov
Telerik

[Patrick]

Hi Viktor, thanks for responding. I'm using SQL Server 2012.

What would you recommend I should do? I'm just looking to strip some characters in my query, so perhaps your suggestion of transforming the parameter might be the best option. Do you have some sample code?

Thanks!

[Patrick]

So I found a solution that seems to fit my needs, thought I'd share:

var customers = (from c in db.Customers
     where c.Company.Name.StartsWith(name[0].ToString())
     select new
     {
         Name = c.Company.Name
     }).ToArray();

return customers.Any(c => FormatForSearch(c.Name) == FormatForSearch(name));

I figure that the database Linq can be tweaked to use either StartsWith or Contains, depending on the needs. That should at least filter out non-relevant results. Then I can process it as an array but still have a 1-liner Linq query to check if there are any results that match my input.

[Viktor Zhivkov]

Hello Patrick,

Here is a quick tutorial how to enable Regex intergration with Microsoft SQL Server.
The guide is based on this MSDN article.
What you have to do in your scenario is:

1. Follow the steps defined in the MSDN article to define User-Defined Functions in assembly that is deployed inside the SQL Server. When creating the Database Project select the right .Net version of your server version;
2. Deploy the assembly on your server - use the article again;
3. Enable CLR Integration on the server if it is not yet enabled - open SQL Surface Area Configuration tool, click on Surface Area Configuration for Features, select the CLR Integration node in the tree view on the left and check the checkbox on the right;
4. Add methods for the new UDF functions in your data layer project and apply Telerik.OpenAccess.Metadata.MappedFunctionAttribute on them to enable LINQ integration. See how to do that in this article. When defining the functions use int as return type for all UDF functions that are returning SqlBoolean. This is required due to some compatibility issues.
5. Modify your LINQ query to call the new mapped methods instead of the original Regex ones and test your LINQ. In the result statements you should see a call to the UDF functions and all the Regex processing will be done on the database server.

I have attached a sample application that demonstrates how it should work.

The steps required are not trivial and in some production environment you may not be able to deploy additional assemblies so consider these fact before settling for your final LINQ implementation given that you already have work around.

This guidance is targeting Microsoft SQL Server 2005 and later, but in general you can use the last 2 steps to map any available functions for other backends like Oracle (which has some Regex operations built-in), MySql (which unfortunately does not support Regex.Replace() method, but supports a few other) and any backend supported by OpenAccess.

if you face any difficulties do not hesitate to contact us with your questions.

Regards,
Viktor Zhivkov
Telerik