How to simulate regular expressions in LINQ-to-SQL

Question

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

Answer 1

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

Answer 2

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();

Answer 3

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.