LINQ 中的 LIKE 运算符

Question

有没有什么方法可以比较 C# LINQ 表达式中的字符串，类似于 SQL 的 LIKE 运算符？

假设我有一个字符串列表。在此列表中我想搜索一个字符串。在 SQL 中，我可以这样写：

SELECT * FROM DischargePort WHERE PortName LIKE '%BALTIMORE%'

查询需要 linq 语法，而不是上面的内容。

using System.Text.RegularExpressions;
…

var regex = new Regex(sDischargePort, RegexOptions.IgnoreCase);
var sPortCode = Database.DischargePorts
                .Where(p => regex.IsMatch(p.PortName))
                .Single().PortCode;

我的上述 LINQ 语法不起作用。我有什么错吗？

Answer 1

通常您使用String.StartsWith/EndsWith/Contains。例如：

var portCode = Database.DischargePorts
                       .Where(p => p.PortName.Contains("BALTIMORE"))
                       .Single()
                       .PortCode;

我不知道是否有办法通过 LINQ to SQL 执行正确的正则表达式。 （请注意，它确实取决于您使用的提供程序 - 在 LINQ to Objects 中这很好；问题在于提供程序是否可以将调用转换为其本机查询格式，例如 SQL。）

编辑：正如 BitKFu 所说，当您期望恰好一个结果时，应使用Single - 当情况并非如此时会出现错误。应根据预期使用 SingleOrDefault、FirstOrDefault 或 First 选项。

Answer 2

正则表达式？不。但是对于该查询您可以使用：

 string filter = "BALTIMORE";
 (blah) .Where(row => row.PortName.Contains(filter)) (blah)

如果您确实想要 SQL LIKE，您可以使用 System.Data.Linq.SqlClient.SqlMethods.Like(.. .)，LINQ-to-SQL 映射到 SQL Server 中的 LIKE。

Answer 3

嗯...有时使用 Contains、StartsWith 或 EndsWith 可能会不舒服，尤其是当搜索值确定 LIKE 时声明例如传递的“value%”要求开发人员在表达式中使用 StartsWith 函数。所以我决定为 IQueryable 对象编写扩展。

使用

// numbers: 11-000-00, 00-111-00, 00-000-11

var data1 = parts.Like(p => p.Number, "%11%");
// result: 11-000-00, 00-111-00, 00-000-11

var data2 = parts.Like(p => p.Number, "11%");
// result: 11-000-00

var data3 = parts.Like(p => p.Number, "%11");
// result: 00-000-11

代码

public static class LinqEx
{
    private static readonly MethodInfo ContainsMethod = typeof(string).GetMethod("Contains");
    private static readonly MethodInfo StartsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
    private static readonly MethodInfo EndsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });

    public static Expression<Func<TSource, bool>> LikeExpression<TSource, TMember>(Expression<Func<TSource, TMember>> property, string value)
    {
        var param = Expression.Parameter(typeof(TSource), "t");
        var propertyInfo = GetPropertyInfo(property);
        var member = Expression.Property(param, propertyInfo.Name);

        var startWith = value.StartsWith("%");
        var endsWith = value.EndsWith("%");

        if (startWith)
            value = value.Remove(0, 1);

        if (endsWith)
            value = value.Remove(value.Length - 1, 1);

        var constant = Expression.Constant(value);
        Expression exp;

        if (endsWith && startWith)
        {
            exp = Expression.Call(member, ContainsMethod, constant);
        }
        else if (startWith) 
        {
            exp = Expression.Call(member, EndsWithMethod, constant);
        }
        else if (endsWith)
        {
            exp = Expression.Call(member, StartsWithMethod, constant);
        }
        else
        {
            exp = Expression.Equal(member, constant);
        }

        return Expression.Lambda<Func<TSource, bool>>(exp, param);
    }

    public static IQueryable<TSource> Like<TSource, TMember>(this IQueryable<TSource> source, Expression<Func<TSource, TMember>> parameter, string value)
    {
        return source.Where(LikeExpression(parameter, value));
    }

    private static PropertyInfo GetPropertyInfo(Expression expression)
    {
        var lambda = expression as LambdaExpression;
        if (lambda == null)
            throw new ArgumentNullException("expression");

        MemberExpression memberExpr = null;

        switch (lambda.Body.NodeType)
        {
            case ExpressionType.Convert:
                memberExpr = ((UnaryExpression)lambda.Body).Operand as MemberExpression;
                break;
            case ExpressionType.MemberAccess:
                memberExpr = lambda.Body as MemberExpression;
                break;
        }

        if (memberExpr == null)
            throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression.");


        var output = memberExpr.Member as PropertyInfo;

        if (output == null)
            throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression.");

        return output;
    }
}

Answer 4

在本机 LINQ 中，您可以使用 Contains/StartsWith/EndsWith 或 RegExp 的组合。

在 LINQ2SQL 中，使用方法 SqlMethods.Like()

    from i in db.myTable
    where SqlMethods.Like(i.field, "tra%ata")
    select i

添加 Assembly: System.Data.Linq（在 System.Data.Linq.dll 中）来使用此功能。

Answer 5

正如 Jon Skeet 和 Marc Gravell 已经提到的，您可以简单地采用包含条件。但对于 like 查询，采用 Single() 语句是非常危险的，因为这意味着您只能找到 1 个结果。如果有更多结果，您将收到一个很好的异常:)

所以我更喜欢使用 FirstOrDefault() 而不是 Single()：

var first = Database.DischargePorts.FirstOrDefault(p => p.PortName.Contains("BALTIMORE"));
var portcode = first != null ? first.PortCode : string.Empty;

Answer 6

一个简单的

string[] users = new string[] {"Paul","Steve","Annick","Yannick"};    
var result = from u in users where u.Contains("nn") select u;

结果 ->安尼克·扬尼克

Answer 7

理想情况下，您应该使用 StartWith 或 EndWith。

这是一个例子：

DataContext  dc = new DCGeneral();
List<Person> lstPerson= dc.GetTable<Person>().StartWith(c=> c.strNombre).ToList();

return lstPerson;

Answer 8

您可以使用谓词调用单个方法：

var portCode = Database.DischargePorts
                   .Single(p => p.PortName.Contains("BALTIMORE"))
                   .PortCode;

Answer 9

也可以使用.net5中测试的EF功能

public async Task<IEnumerable<District>> SearchDistrict(string query, int stateId)
        {
            return await _dbContext
                .Districts
                .Include(s => s.State)
                .Where(s => s.StateId == stateId && EF.Functions.Like(s.Name, "$%{query}%"))
                .AsNoTracking()
                .ToListAsync();
        }

Answer 10

  .Where(e => e.Value.StartsWith("BALTIMORE"))

这就像 SQL 的“LIKE”一样......

Answer 11

List<Categories> categoriess;
        private void Buscar()
        {
            try
            {
                categoriess = Contexto.Categories.ToList();
                categoriess = categoriess.Where(n => n.CategoryID >= Convert.ToInt32(txtCatID.Text) && n.CategoryID <= Convert.ToInt32(txtCatID1.Text) && (n.CategoryName.Contains(txtCatName.Text)) ).ToList();

Answer 12

Like 扩展 Linq / SQL

LikeExtension 类

中测试

 public static class LikeExtension {

    private static string ColumnDataBase<TEntity, TKey>(IModel model, Expression<Func<TEntity, TKey>> predicate) where TEntity : class {

        ITable table = model
            .GetRelationalModel()
            .Tables
            .First(f => f
                .EntityTypeMappings
                .First()
                .EntityType == model
                .FindEntityType(predicate
                    .Parameters
                    .First()
                .Type
            ));

        string column = (predicate.Body as MemberExpression).Member.Name;
        string columnDataBase = table.Columns.First(f => f.PropertyMappings.Count(f2 => f2.Property.Name == column) > 0).Name;

        return columnDataBase;

    }

    public static IQueryable<TEntity> Like<TEntity, TKey>(this DbContext context, Expression<Func<TEntity, TKey>> predicate, string text) where TEntity : class {

        string columnDataBase = ColumnDataBase(context.Model, predicate);
        return context.Set<TEntity>().FromSqlRaw(context.Set<TEntity>().ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text);

    }

    public static async Task<IEnumerable<TEntity>> LikeAsync<TEntity, TKey>(this DbContext context, Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) where TEntity : class {

        string columnDataBase = ColumnDataBase(context.Model, predicate);
        return await context.Set<TEntity>().FromSqlRaw(context.Set<TEntity>().ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text).ToListAsync(cancellationToken);

    }

    public static async Task<IEnumerable<TEntity>> LikeAsync<TEntity, TKey>(this IQueryable<TEntity> query, Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) where TEntity : class {

        DbSet<TEntity> entities = query as DbSet<TEntity>;
        string columnDataBase = ColumnDataBase(entities.EntityType.Model, predicate);
        return await entities.FromSqlRaw(query.ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text).ToListAsync(cancellationToken);

    }

    public static IQueryable<TEntity> Like<TEntity, TKey>(this IQueryable<TEntity> query, Expression<Func<TEntity, TKey>> predicate, string text) where TEntity : class {

        DbSet<TEntity> entities = query as DbSet<TEntity>;
        string columnDataBase = ColumnDataBase(entities.EntityType.Model, predicate);
        return entities.FromSqlRaw(query.ToQueryString() + " WHERE [" + columnDataBase + "] LIKE {0}", text);

    }

}

在 .NET 5存储库

    public async Task<IEnumerable<TEntity>> LikeAsync<TKey>(Expression<Func<TEntity, TKey>> predicate, string text, CancellationToken cancellationToken) {

        return await context.LikeAsync(predicate, text, cancellationToken);

    }

    public IQueryable<TEntity> Like<TKey>(Expression<Func<TEntity, TKey>> predicate, string text) {

        return context.Like(predicate, text);

    }

使用

 IQueryable<CountryEntity> result = countryRepository
     .Like(k => k.Name, "%Bra[sz]il%") /*Use Sync*/
     .Where(w => w.DateRegister < DateTime.Now) /*Example*/
     .Take(10); /*Example*/

Or

 IEnumerable<CountryEntity> result = await countryRepository
     .LikeAsync(k => k.Name, "%Bra[sz]il%", cancellationToken); /*Use Async*/

Or

 IQueryable<CountryEntity> result = context.Countries
     .Like(k => k.Name, "%Bra[sz]il%")
     .Where(w => w.Name != null); /*Example*/

Or

 List<CountryEntity> result2 = await context.Countries
     .Like(k => k.Name, "%Bra[sz]il%")
     .Where(w => w.Name != null) /*Example*/
     .ToListAsync(); /*Use Async*/

Or

 IEnumerable<CountryEntity> result3 = await context.Countries
     .Where(w => w.Name != null)
     .LikeAsync(k => k.Name, "%Bra[sz]il%", cancellationToken); /*Use Async*/

Answer 13

   public static class StringEx
    {
        public static bool Contains(this String str, string[] Arr, StringComparison comp)
        {
            if (Arr != null)
            {
                foreach (string s in Arr)
                {
                    if (str.IndexOf(s, comp)>=0)
                    { return true; }
                }
            }

            return false;
        }

        public static bool Contains(this String str,string[] Arr)
        {
            if (Arr != null)
            {
                foreach (string s in Arr)
                {
                    if (str.Contains(s))
                    { return true; }
                }
            }

            return false;
        }
    }


var portCode = Database.DischargePorts
                   .Single(p => p.PortName.Contains( new string[] {"BALTIMORE"},  StringComparison.CurrentCultureIgnoreCase) ))
                   .PortCode;

Answer 14

只需添加到字符串对象扩展方法即可。

public static class StringEx
{
    public static bool Contains(this String str, string[] Arr, StringComparison comp)
    {
        if (Arr != null)
        {
            foreach (string s in Arr)
            {
                if (str.IndexOf(s, comp)>=0)
                { return true; }
            }
        }

        return false;
    }

    public static bool Contains(this String str,string[] Arr)
    {
        if (Arr != null)
        {
            foreach (string s in Arr)
            {
                if (str.Contains(s))
                { return true; }
            }
        }

        return false;
    }
}

用法：

use namespase that contains this class;

var sPortCode = Database.DischargePorts
            .Where(p => p.PortName.Contains(new string [] {"BALTIMORE"},  StringComparison.CurrentCultureIgnoreCase) )
            .Single().PortCode;

Answer 15

@adobrzyc 有这个很棒的自定义 LIKE 函数 - 我只是想分享它的 IEnumerable 版本。

public static class LinqEx
{
    private static readonly MethodInfo ContainsMethod = typeof(string).GetMethod("Contains");
    private static readonly MethodInfo StartsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
    private static readonly MethodInfo EndsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });

    private static Func<TSource, bool> LikeExpression<TSource, TMember>(Expression<Func<TSource, TMember>> property, string value)
    {
        var param = Expression.Parameter(typeof(TSource), "t");
        var propertyInfo = GetPropertyInfo(property);
        var member = Expression.Property(param, propertyInfo.Name);

        var startWith = value.StartsWith("%");
        var endsWith = value.EndsWith("%");

        if (startWith)
            value = value.Remove(0, 1);

        if (endsWith)
            value = value.Remove(value.Length - 1, 1);

        var constant = Expression.Constant(value);
        Expression exp;

        if (endsWith && startWith)
        {
            exp = Expression.Call(member, ContainsMethod, constant);
        }
        else if (startWith)
        {
            exp = Expression.Call(member, EndsWithMethod, constant);
        }
        else if (endsWith)
        {
            exp = Expression.Call(member, StartsWithMethod, constant);
        }
        else
        {
            exp = Expression.Equal(member, constant);
        }

        return Expression.Lambda<Func<TSource, bool>>(exp, param).Compile();
    }

    public static IEnumerable<TSource> Like<TSource, TMember>(this IEnumerable<TSource> source, Expression<Func<TSource, TMember>> parameter, string value)
    {
        return source.Where(LikeExpression(parameter, value));
    }


    private static PropertyInfo GetPropertyInfo(Expression expression)
    {
        var lambda = expression as LambdaExpression;
        if (lambda == null)
            throw new ArgumentNullException("expression");

        MemberExpression memberExpr = null;

        switch (lambda.Body.NodeType)
        {
            case ExpressionType.Convert:
                memberExpr = ((UnaryExpression)lambda.Body).Operand as MemberExpression;
                break;
            case ExpressionType.MemberAccess:
                memberExpr = lambda.Body as MemberExpression;
                break;
        }

        if (memberExpr == null)
            throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression.");


        var output = memberExpr.Member as PropertyInfo;

        if (output == null)
            throw new InvalidOperationException("Specified expression is invalid. Unable to determine property info from expression.");

        return output;
    }
}

Answer 16

@adobrzyc 提供的出色自定义 LIKE 功能don'在 LINQPad 中对我来说不起作用。

这里的版本在 LINQPad 中工作（在 LINQPad 5 和 LINQPad 6 上测试）

代码

void Main()
{
    var users = from au in ApplicationUsers
                select au;
    
    users.Like(u => u.UserName, "Ada Byron").Dump();
    users.Like(u => u.UserName, "%yro%").Dump();
    users.Like(u => u.UserName, "% Byron").Dump();
    users.Like(u => u.UserName, "Ada %").Dump();    

    users.Like(u => u.UserName, "%yro%").Like(u => u.UserName, "Ada %").Dump();    
    // => SQL =>
    // DECLARE @p0 NVarChar(1000) = 'Ada %'
    // DECLARE @p1 NVarChar(1000) = '%yro%'
    // SELECT [t0].[UserName], ...
    // FROM [ApplicationUsers] AS [t0]
    // WHERE ([t0].[UserName] LIKE @p0) AND ([t0].[UserName] LIKE @p1)
}

// based on LinqEx by adobrzyc (https://stackoverflow.com/a/35636138/1351740)
public static class LinqExFork
{
    private static readonly MethodInfo ContainsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    private static readonly MethodInfo StartsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
    private static readonly MethodInfo EndsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });

    public static Expression<Func<TSource, bool>> LikeExpression<TSource, TMember>(Expression<Func<TSource, TMember>> property, string value)
    {
        var param = Expression.Parameter(typeof(TSource), "t");
        var memberInfo = GetMemberInfo(property);
        
        MemberExpression member;
        if (memberInfo is PropertyInfo)
            member = Expression.Property(param, memberInfo.Name);
        else if (memberInfo is FieldInfo)
            member = Expression.Field(param, memberInfo.Name);
        else                
            throw new InvalidOperationException("Unable to determine propery or field info from expression.");

        var startWith = value.StartsWith("%");
        if (startWith)
            value = value.Remove(0, 1);
            
        var endsWith = value.EndsWith("%");
        if (endsWith)
            value = value.Remove(value.Length - 1, 1);

        var constant = Expression.Constant(value);
        
        Expression exp;
        if (endsWith && startWith)
            exp = Expression.Call(member, ContainsMethod, constant);
        else if (startWith) 
            exp = Expression.Call(member, EndsWithMethod, constant);
        else if (endsWith)
            exp = Expression.Call(member, StartsWithMethod, constant);
        else
            exp = Expression.Equal(member, constant);

        return Expression.Lambda<Func<TSource, bool>>(exp, param);
    }

    public static IQueryable<TSource> Like<TSource, TMember>(this IQueryable<TSource> source, Expression<Func<TSource, TMember>> parameter, string value)
    {
        return source.Where(LikeExpression(parameter, value));
    }

    private static MemberInfo GetMemberInfo(Expression expression)
    {
        var lambda = expression as LambdaExpression;
        if (lambda == null)
            throw new ArgumentNullException("expression");

        MemberExpression memberExpr = null;
        switch (lambda.Body.NodeType)
        {
            case ExpressionType.Convert:
                memberExpr = ((UnaryExpression)lambda.Body).Operand as MemberExpression;
                break;
                
            case ExpressionType.MemberAccess:
                memberExpr = lambda.Body as MemberExpression;
                break;
        }

        if (memberExpr == null)
            throw new InvalidOperationException("Specified expression is invalid. Unable to determine member info from expression.");

        var output = memberExpr.Member as MemberInfo;
        if (output == null)
            throw new InvalidOperationException("Specified expression is invalid. Unable to determine member info from expression.");

        return output;
    }
}