如何模拟LINQ到SQL正则表达式 [英] How to simulate regular expressions in LINQ-to-SQL

问题描述

我有一个客户帐号的数据库表。在同一个表是不匹配生产格式测试帐户：说A1111是生产，但'JTEST不是。我只会拉我的产量占正则表达式。我需要一个具体编译查询只拉产量占。查询给我按地区和日期的客户数;每个区域内的概念计数：

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:

方法'布尔IsMatch（System.String，
System.String）没有支持
转换为SQL。

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

我一直在寻找一个用户定义的FUNC：

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.

有没有办法与谓词Builder来做到这一点？

Is there a way to do this with Predicate Builder?

我觉得另一种选择是使用方法：

Another option I think would be to use:

其中SqlMethods.Like（cust.AcctNum，ProductionMask）

不过，我ProductionMask是对于正则表达式写的：结果
^ [b，G] \d {4} $

However, my ProductionMask is written for Regex:
^[B,G]\d{4}$

有没有办法与SqlMethods做到这一点。 ？赞（...）

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

这是一个非常缓慢运行的查询。我有3个区域，这个运行查询和记录计数和放大器;返回的时间是：结果
263：903ms结果
342：822ms结果
146：711ms

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

推荐答案

我改变了查询，使用以下代替 Regex.IsMatch 的：

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

where SqlMethods.Like(cust.Acct, ProductionMask)  

其中， ProductionMask =[bBgG] [0-9] [0-9] [0-9] [0-9]

相当于正则表达式是： ^ [b，G] \d {4} $

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

如果有人看到这2口罩不应该产生相同的结果，请让我知道...

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

这篇关于如何模拟LINQ到SQL正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！