就像LINQ到对象中的操作符

本文关键字：操作符 对象 LINQ 就像 | 更新日期: 2023-09-27 17:50:16

我试图模拟LINQ到对象中的LIKE操作符。下面是我的代码:

List<string> list = new List<string>();
list.Add("line one");
list.Add("line two");
list.Add("line three");
list.Add("line four");
list.Add("line five");
list.Add("line six");
list.Add("line seven");
list.Add("line eight");
list.Add("line nine");
list.Add("line ten");
string pattern = "%ine%e";
var res = from i in list
            where System.Data.Linq.SqlClient.SqlMethods.Like(i, pattern)
              select i;

它没有得到我的结果，因为System.Data.Linq.SqlClient.SqlMethods.Like只是翻译成SQL。

是否有任何类似于sql LIKE操作符存在于LINQ对象世界?

就像LINQ到对象中的操作符

我不知道是否有现成的正则表达式，但是如果您熟悉正则表达式，您可以编写自己的正则表达式:

using System;
using System.Text.RegularExpressions;
public static class MyExtensions
{
    public static bool Like(this string s, string pattern, RegexOptions options = RegexOptions.IgnoreCase)
    {
        return Regex.IsMatch(s, pattern, options);
    }
}

然后在你的代码中:

string pattern = ".*ine.*e";
var res = from i in list
    where i.Like(pattern)
    select i;

这个代码片段将模拟Sql LIKE的行为和语法。您可以将其包装成您自己的lambda或扩展方法，以便在Linq语句中使用:

public static bool IsSqlLikeMatch(string input, string pattern)
{
   /* Turn "off" all regular expression related syntax in
    * the pattern string. */
   pattern = Regex.Escape(pattern);
   /* Replace the SQL LIKE wildcard metacharacters with the
    * equivalent regular expression metacharacters. */
   pattern = pattern.Replace("%", ".*?").Replace("_", ".");
   /* The previous call to Regex.Escape actually turned off
    * too many metacharacters, i.e. those which are recognized by
    * both the regular expression engine and the SQL LIKE
    * statement ([...] and [^...]). Those metacharacters have
    * to be manually unescaped here. */
   pattern = pattern.Replace(@"'[", "[").Replace(@"']", "]").Replace(@"'^", "^");
   return Regex.IsMatch(input, pattern, RegexOptions.IgnoreCase);
}

一个类似IEnumerable<T>.Where方法的粗略的扩展方法:

public static IEnumerable<T> Like<T>(this IEnumerable<T> source, Func<T, string> selector, string pattern)
{
   return source.Where(t => IsSqlLikeMatch(selector(t), pattern));
}

这将允许你格式化你的语句，像这样:

string pattern = "%ine%e";
var res = list.Like(s => s, pattern);

编辑改进的实现，如果有人偶然发现并想使用此代码。它为每个条目转换和编译一次正则表达式，上面从LIKE到正则表达式的转换有一些bug。

public static class LikeExtension
{
    public static IEnumerable<T> Like<T>(this IEnumerable<T> source, Func<T, string> selector, string pattern)
    {
        var regex = new Regex(ConvertLikeToRegex(pattern), RegexOptions.IgnoreCase);
        return source.Where(t => IsRegexMatch(selector(t), regex));
    }
    static bool IsRegexMatch(string input, Regex regex)
    {
        if (input == null)
            return false;
        return regex.IsMatch(input);
    }
    static string ConvertLikeToRegex(string pattern)
    {
        StringBuilder builder = new StringBuilder();
        // Turn "off" all regular expression related syntax in the pattern string
        // and add regex begining of and end of line tokens so '%abc' and 'abc%' work as expected
        builder.Append("^").Append(Regex.Escape(pattern)).Append("$");
        /* Replace the SQL LIKE wildcard metacharacters with the
        * equivalent regular expression metacharacters. */
        builder.Replace("%", ".*").Replace("_", ".");
        /* The previous call to Regex.Escape actually turned off
        * too many metacharacters, i.e. those which are recognized by
        * both the regular expression engine and the SQL LIKE
        * statement ([...] and [^...]). Those metacharacters have
        * to be manually unescaped here. */
        builder.Replace(@"'[", "[").Replace(@"']", "]").Replace(@"'^", "^");
        // put SQL LIKE wildcard literals back
        builder.Replace("[.*]", "[%]").Replace("[.]", "[_]");
        return builder.ToString();
    }
}

您必须为模式使用Regex，然后使用扩展方法Where来迭代并查找匹配。

所以你的代码应该像这样结束: 
string pattern = @".*ine.*e$";
var res = list.Where( e => Regex.IsMatch( e, pattern));

如果你不熟悉Regex，这是:

前0个或多个字符(.*)，后面是ine(line)，然后是0个或多个字符(.*) then和e ， e应该是字符串($)

的结尾

1。使用字符串。StartsWith或String。Endswith

写入如下查询:

var query = from c in ctx.Customers
            where c.City.StartsWith("Lo")
            select c;
will generate this SQL statement:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [Lo%]

，这正是我们想要的。String.EndsWith.

但是，我们想要查询城市名称为"L_n%"的客户是什么?(以大写字母"L"开头，然后是某个字符，然后是"n"，然后是名字的其余部分)。使用查询

var query = from c in ctx.Customers
            where c.City.StartsWith("L") && c.City.Contains("n")
            select c;
generates the statement:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [L%]
AND      City LIKE [%n%]

这不是我们想要的，而且有点复杂。

2。使用SqlMethods。像方法

深入System.Data.Linq.SqlClient名称空间，我发现了一个名为SqlMethods的小助手类，它在这种情况下非常有用。SqlMethods有一个名为Like的方法，可以在Linq to SQL查询中使用:

var query = from c in ctx.Customers
            where SqlMethods.Like(c.City, "L_n%")
            select c;

该方法获取要检查的字符串表达式(在本例中为客户所在的城市)和要测试的模式，其提供方式与在SQL中编写LIKE子句的方式相同。

使用上述查询生成所需的SQL语句:

SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [L_n%]

来源:http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/10/16/linq-to-sql-like-operator.aspx

我不知道它是否存在，但这里是使用Knuth-Morris-Pratt算法的扩展方法的实现。

public static IEnumerable<T> Like<T>(this IEnumerable<T> lista, Func<T, string> type, string pattern)
            {
                int[] pf = prefixFunction(pattern);
                foreach (T e in lista)
                {
                    if (patternKMP(pattern, type(e), pf))
                        yield return e;
                }
            }
            private static int[] prefixFunction(string p)
            {

                int[] pf = new int[p.Length];
                int k = pf[0] = -1;

                for (int i = 1; i < p.Length; i++)
                {
                    while (k > -1 && p[k + 1] != p[i])
                        k = pf[k];
                    pf[i] = (p[k + 1] == p[i]) ? ++k : k;
                }
                return pf;
            }
            private static bool patternKMP(string p, string t, int[] pf)
            {
                for (int i = 0, k = -1; i < t.Length; i++)
                {
                    while (k > -1 && p[k + 1] != t[i])
                        k = pf[k];
                    if (p[k + 1] == t[i])
                        k++;
                    if (k == p.Length - 1)
                        return true;    
                }
                return false;
            }
相关文章：