[ACCEPTED]-How to simulate regular expressions in LINQ-to-SQL-linq-to-sql

Accepted answer
Score: 13

I changed the query to use the following 4 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 3 RegEx is: ^[B,G]\d{4}$

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

Score: 8

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

RegExpLike Extension For Sql 9 Server

Reference links:

Step1: Compile SqlRegularExpressions.cs 8 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>
    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 7 DbInstall.sql SQL on the database


sp_configure 'clr enabled', 1

--needs full path to DLL
create assembly SqlRegularExpressions 
from '..\SqlRegularExpressions.dll' 

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

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


drop function RegExpLike
drop function RegExpMatches

drop assembly SqlRegularExpressions

sp_configure 'clr enabled', 0

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

Step 4: Create 3 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 2 you can use regular expressions on LINQ 1 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)
Score: 6

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

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

It 1 does give 3 alternatives though.

Score: 1

Could you replace the Regex.IsMatch with

where cust.AcctNum.StartsWith(ProductionMask)

Or 1 Contains / EndsWith depending on your needs

Score: 1

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

Regex rx = LikeToRegEx(emailToMatch);

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

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

Score: 0

In EF 6.2 to 6.4.4 use DbFunctions.Like


More Related questions