比较电话号码，而不考虑实体的Linq格式，并检查性能 [英] Compare phone numbers without considering any format in Linq to entities and check performance as well

问题描述

我必须将Linq中的电话号码与实体进行比较.我正在使用正则表达式，以便在忽略格式时只能比较数字.

I have to compare phone numbers in Linq to entities. I am using Regex so that I can compare only numbers while ignoring format.

由于我们无法在where子句中使用Regex，因此我获得了电话号码列表，然后应用了循环来比较电话号码.

As we can not use Regex in where clause so I get list of phone numbers and then applied loop to compare the phone numbers.

这是代码:

    /// <summary>
    /// Check Phone number exist or Not
    /// </summary>
    /// <param name="accountNumber"></param>
    /// <returns></returns>
    public bool CheckPhoneNumberExist(string phoneNumber)
    {
        LP= new LPEntities();

        // In order to compare phone number, we need to use regex so that we can compare only numbers and not any specific format
        phoneNumber = Regex.Replace(phoneNumber, "[^0-9]", "");

        var phoneList = (from act in LP.Accounts
                         select act).ToList();
        if (phoneList.Count > 0)
        {
            foreach (var item in phoneList)
            {
                if (item.Telephone != null)
                {
                    if (Regex.Replace(item.Telephone, "[^0-9]", "") == phoneNumber)
                    {
                        return true;
                    }
                }
            }
        }

        return false;
      }

代码工作正常，但是存在性能问题.我们数据库中有2万条记录，这些记录一直在增加，因此创建一个列表然后遍历该列表的速度很慢.

The code is working fine but there is a performance issue. We have 20 thousands records in the database which keep on increasing so its slow to make a list and then iterate through that list.

我们如何优化以上内容?是否还有其他解决方案，例如是否可以使用存储过程呢?

How might we optimize the above? Is there some other solution like if we can do it with stored procedure?

谢谢

推荐答案

请注意，您的正则表达式技术不会识别出这些数字实际上是相同的:

Note that your regex technique won't identify that these numbers are actually the same:

[phone]
[phone]
01234 567890

我的第一选择是将数字存储为varchar，而不进行格式化，而仅对其进行格式化以进行显示.这意味着您可以摆脱检索时的正则表达式，并且可以添加索引来进一步提高检索速度.

My first choice would be to store the number as a varchar without formatting and only format it for display. That means you get rid of the regex on retrieval and you can add an index to speed it up even more.

我的第二个选择是添加一列，以便您存储它们并使它们保持同步.

My second choice would be to add a column so that you store both and keep them synchronised.

在这里查看答案:

是最好以某种规范格式或按输入的形式"存储电话号码?

最后，如果您将linq更改为此，则可能会有所改善:

Lastly you might get some improvement if you alter your linq to this:

bool exists = (from act in LP.Accounts
               where act.Telephone != null //move the null check to the database
               select act.Telephone)  //select only the telephone number
              .AsEnumerable()  //using linq-to-objects
                               //after retrieving the results from the database
                               //lets you use Regex 
                               //- but it does get them all into memory
              .Any(pn => Regex.Replace(pn, "[^0-9]", "") == phoneNumber) 
              //Any stops at the first one 
              //better than Counting them all then checking `> 0`

顺便说一句.在 foreach(phoneList中的var项目)前面添加检查 if(phoneList.Count> 0)是多余的，因为如果存在循环，则不会执行循环主体列表中没有任何项目.

As an aside. Adding the check if (phoneList.Count > 0) is redundant in front of foreach (var item in phoneList) because the body of the loop is not executed if there are no items in the list.

这篇关于比较电话号码，而不考虑实体的Linq格式，并检查性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！