如何在 LINQ-to-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:

Method 'Boolean IsMatch(System.String,System.String)' 不支持翻译成 SQL.

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.

有没有办法用 Predicate Builder 做到这一点?

Is there a way to do this with Predicate Builder?

我认为另一种选择是:

where SqlMethods.Like (cust.AcctNum, ProductionMask)

但是，我的 ProductionMask 是为 Regex 编写的:

However, my ProductionMask is written for Regex:

^[B,G]d{4}$

有没有办法用 SqlMethods.Like(...) 做到这一点?

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

这是一个运行速度非常慢的查询.我有 3 个区域运行此查询，并且记录计数 &返回时间为:
263:903 毫秒
342:822 毫秒
146:711 毫秒

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)  

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

等效的正则表达式为:^[B,G]d{4}$

如果有人看到两个面具不应该产生相同的结果，请告诉我...

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

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