How to simulate regular expressions in LINQ-to-SQL

I have a database table with customer account numbers. Within the same table are test accounts that don't match the production formatting: say, 'A1111' is production but 'JTest' is not. I have the Regex that will pull only my production accounts. I need a specific compiled query to pull only the production accounts. The query gives me a customer count by region and date; and concept counts within each region:

        getCustomerDistribution = CompiledQuery.Compile<DataContext, String, DateTime, IEnumerable<ServerLoad>>(
            (context, region, processDate) => (from cust in context.GetTable<tbl_CustomerDistro>()
                                               where cust.ProcessedDate.Date == processDate.Date
                                               where cust.Region == region
                                               where Regex.IsMatch(cust.AcctNum, ProductionMask)
                                               group cust by new
                                               {
                                                   cust.Region,
                                                   cust.Concept
                                               } into custDistro
                                               orderby custDistro.Key.Region
                                               select new CustomerDistro
 开发者_开发百科                                              (
                                                   custDistro.Key.Region,
                                                   custDistro.Key.Concept,
                                                   custDistro
                                                    .Where(c => c.Concept == custDistro.Key.Concept)
                                                    .Select(c => c.Concept).Count()
                                               )));

Problem is that I get the following message at run-time:

Method 'Boolean IsMatch(System.String, System.String)' has no supported translation to SQL.

I was looking at a user defined func:

static Func<striing, bool> IsProduction = (AcctNum) => Regex.IsMatch(AcctNum, ProductionMask);

This doesn't work either. I don't want to iterate the records that are retrieved to further filter unless there is just no other way to do this.

Is there a way to do this with Predicate Builder?

Update:

Another option I think would be to use:

where SqlMethods.Like (cust.AcctNum, ProductionMask)

However, my ProductionMask is written for Regex:

^[B,G]\d{4}$

Is there a way to do this with the SqlMethods.Like(...)?

Update 2:

This is a very slow running query. I have 3 regions that this query runs against and the record counts & return times are:

263: 903ms

342: 822ms

146: 711ms

I changed the query to use the following in place of the Regex.IsMatch:

where SqlMethods.Like(cust.Acct, ProductionMask)  

where ProductionMask = "[bBgG][0-9][0-9][0-9][0-9]"

the equivalent RegEx is: ^[B,G]\d{4}$

If anyone sees that the 2 masks should not produce the same results, please let me know...

special thanks to Roman Khramtsov and db_developer for reference information, and thanks to Microsoft :P

RegExpLike Extension For Sql Server

Reference links:
http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server-2005-2008
http://msdn.microsoft.com/en-us/library/dd456847.aspx

Step1: Compile SqlRegularExpressions.cs to generate SqlRegularExpressions.dll

// SqlRegularExpressions.cs
// © Copyright 2009, Roman Khramtsov / Major League - SqlRegularExpressions

using System;
using System.Data.SqlTypes;         //SqlChars
using System.Collections;           //IEnumerable
using System.Text.RegularExpressions;   //Match, Regex
using Microsoft.SqlServer.Server;       //SqlFunctionAttribute

/// <summary>
/// Class that allows to support regular expressions in MS SQL Server 2005/2008
/// </summary>
public partial class SqlRegularExpressions
{
    /// <summary>
    /// Checks string on match to regular expression
    /// </summary>
    /// <param name="text">string to check</param>
    /// <param name="pattern">regular expression</param>
    /// <returns>true - text consists match one at least, false - no matches</returns>
    [SqlFunction]
    public static bool Like(string text, string pattern, int options)
    {
        return (Regex.IsMatch(text, pattern, (RegexOptions)options));
    }

    /// <summary>
    /// Gets matches from text using pattern
    /// </summary>
    /// <param name="text">text to parse</param>
    /// <param name="pattern">regular expression pattern</param>
    /// <returns>MatchCollection</returns>
    [SqlFunction(FillRowMethodName = "FillMatch")]
    public static IEnumerable GetMatches(string text, string pattern, int options)
    {
        return Regex.Matches(text, pattern, (RegexOptions)options);
    }

    /// <summary>
    /// Parses match-object and returns its parameters 
    /// </summary>
    /// <param name="obj">Match-object</param>
    /// <param name="index">TThe zero-based starting position in the original string where the captured
    ///     substring was found</param>
    /// <param name="length">The length of the captured substring.</param>
    /// <param name="value">The actual substring that was captured by the match.</param>
    public static void FillMatch(object obj, out int index, out int length, out SqlChars value)
    {
        Match match = (Match)obj;
        index = match.Index;
        length = match.Length;
        value = new SqlChars(match.Value);
    }

}

Step 2: Run DbInstall.sql SQL on the database

DbInstall.sql

sp_configure 'clr enabled', 1
reconfigure
go

--needs full path to DLL
create assembly SqlRegularExpressions 
from '..\SqlRegularExpressions.dll' 
with PERMISSION_SET = SAFE
go

create function RegExpLike(@Text nvarchar(max), @Pattern nvarchar(255), @Options int = 0) 
returns bit 
as external name SqlRegularExpressions.SqlRegularExpressions.[Like]
go

create function RegExpMatches(@text nvarchar(max), @pattern nvarchar(255), @Options int = 0)
returns table ([Index] int, [Length] int, [Value] nvarchar(255))
as external name SqlRegularExpressions.SqlRegularExpressions.GetMatches
go

DbUninstall.sql

drop function RegExpLike
drop function RegExpMatches

drop assembly SqlRegularExpressions
go

sp_configure 'clr enabled', 0
reconfigure
go

Step 3: On model diagram right click, select “Update Model from Database...”, use update wizard to add stored functions to model.

Step 4: Create imported functions in entity context class.

public class TheCompanyContext : Entities
{
        // Please check your entity store name
        [EdmFunction("TheCompanyDbModel.Store", "RegExpLike")]
        public bool RegExpLike(string text, string pattern, int options)
        {
            throw new NotSupportedException("Direct calls are not supported.");
        }
}

Step 5: Finally you can use regular expressions on LINQ to Entities :)

User[] qry = (from u in context.Users
              where u.ApplicationName == pApplicationName
                 && context.RegExpLike(u.Username, usernameToMatch, (int)RegexOptions.IgnoreCase)
              orderby u.Username
              select u)
             .Skip(startIndex)
             .Take(pageSize)
             .ToArray();

Are you using LINQ-to-SQL? If so, MSDN forums state the following:

LINQ to SQL cannot translate regular expressions to SQL because there's no support for Regex at the SQL end.

It does give 3 alternatives though.

Could you replace the Regex.IsMatch with

where cust.AcctNum.StartsWith(ProductionMask)

Or Contains / EndsWith depending on your needs

I've had the same problem, but managed to get rid of it. I know it's slow but works, any optimization/bugfix hint will be welcomed :) The code gathers the data first then processes, so you need to filter as much as you can before calling toarray() or buy more ram :)
hope it helps, enjoy

Regex rx = LikeToRegEx(emailToMatch);

User[] qry = (from u in context.Users
              where u.ApplicationName == pApplicationName
              orderby u.Username
              select u)
             .ToArray()
             .Where(u => rx.IsMatch(u.Email))
             .ToArray();

 // -- LikeToRegEx : Converts SQL like match pattern to a regular expression -- 
 public Regex LikeToRegEx(string likestr, RegexOptions opt = RegexOptions.None)
 {
            likestr = likestr
                     .Replace("*", ".")
                     .Replace("+", ".")
                     .Replace("(", ".")
                     .Replace("[", ".")
                     .Replace("/", ".")
                     .Replace("\\", ".")
                     .Replace("^", ".")
                     .Replace("$", ".")
                     .Replace("_", ".")
                     .Replace("%", ".*");

            return new Regex(likestr, opt);
 }

P.S. This is a fast way for processing light data tables, you can improve it by just fetching needed columns for processing and just return ID columns for full access to rows. You can use my last post for a more general heavy duty scenarios. Choice is yours.

In EF 6.2 to 6.4.4 use DbFunctions.Like