[LINQ].NET/C#应用程序开发如何使用LINQ表达式实现类似SQL语句中的LIKE子句呢？

问题描述

在.NET/C#应用程序开发中，我们如何使用LINQ表达式实现类似SQL语句中的LIKE子句呢？

比如，当前有一个字符串集合，现在需要查询包含指定字符串的结果。伪SQL语句如下：

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

在.NET/C#应用程序开发中，如何使用LINQ实现类似SQL语句中的LIKE查询呢？

方案一

在.NET/C#应用程序开发中，我们可以使用String.StartsWith / EndsWith / Contains 这些方法来处理。比如：

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

方案二

使用LINQ的Contains(...)方法，如下：

string filter = "BALTIMORE";
var result = list.Where(row => row.PortName.Contains(filter));

方案三

创建一个关于LINQ的Like(...)的静态扩展方法，如下：

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;
    }
}

调用示例：

// 输入示例数据
// numbers: 11-000-00, 00-111-00, 00-000-11

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

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

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