title: How to simulate regular expressions in LINQ-to-SQL
body: <p>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:</p>
  
  <pre><code>        getCustomerDistribution = CompiledQuery.Compile&lt;DataContext, String, DateTime, IEnumerable&lt;ServerLoad&gt;&gt;(
              (context, region, processDate) =&gt; (from cust in context.GetTable&lt;tbl_CustomerDistro&gt;()
                                                 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 =&gt; c.Concept == custDistro.Key.Concept)
                                                      .Select(c =&gt; c.Concept).Count()
                                                 )));
  </code></pre>
  
  <p>Problem is that I get the following message at run-time:</p>
  
  <blockquote>
    <p>Method 'Boolean IsMatch(System.String,
    System.String)' has no supported
    translation to SQL.</p>
  </blockquote>
  
  <p>I was looking at a user defined func:</p>
  
  <pre><code>static Func&lt;striing, bool&gt; IsProduction = (AcctNum) =&gt; Regex.IsMatch(AcctNum, ProductionMask);
  </code></pre>
  
  <p>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. </p>
  
  <p>Is there a way to do this with Predicate Builder?  </p>
  
  <h2>Update:</h2>
  
  <p>Another option I think would be to use:  </p>
  
  <p><code>where SqlMethods.Like (cust.AcctNum, ProductionMask)</code></p>
  
  <p>However, my ProductionMask is written for Regex:  </p>
  
  <pre><code>^[B,G]\d{4}$
  </code></pre>
  
  <p>Is there a way to do this with the SqlMethods.Like(...)?</p>
  
  <h2>Update 2:</h2>
  
  <p>This is a very slow running query. I have 3 regions that this query runs against and the record counts &amp; return times are:<br>
      263: 903ms<br>
      342: 822ms<br>
      146: 711ms  </p>