LIKE operator in LINQ

Is there any way to compare strings in a C# LINQ expression similar to SQL's LIKE operator?

Suppose I have a string list. On this list I want to search a string. In SQL, I could write:

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

Instead of the above, query want a linq syntax.

using System.Text.RegularExpressions;
…

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

My above LINQ syntax does not work. What have I got wrong?

Solution 1:

Typically you use String.StartsWith/EndsWith/Contains. For example:

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

I don't know if there's a way of doing proper regular expressions via LINQ to SQL though. (Note that it really does depend on which provider you're using - it would be fine in LINQ to Objects; it's a matter of whether the provider can convert the call into its native query format, e.g. SQL.)

EDIT: As BitKFu says, Single should be used when you expect exactly one result - when it's an error for that not to be the case. Options of SingleOrDefault, FirstOrDefault or First should be used depending on exactly what's expected.

Solution 2:

Regex? no. But for that query you can just use:

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

If you really want SQL LIKE, you can use System.Data.Linq.SqlClient.SqlMethods.Like(...), which LINQ-to-SQL maps to LIKE in SQL Server.

Solution 3:

Well... sometimes it may be uncomfortable to use Contains, StartsWith or EndsWith especially when searching value determine LIKE statment e.g. passed 'value%' require from developer to use StartsWith function in expression. So I decided to write extension for IQueryable objects.

Usage

// 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

Code

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

Solution 4:

In native LINQ you may use combination of Contains/StartsWith/EndsWith or RegExp.

In LINQ2SQL use method SqlMethods.Like()

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

add Assembly: System.Data.Linq (in System.Data.Linq.dll) to use this feature.

Solution 5:

As Jon Skeet and Marc Gravell already mentioned, you can simple take a contains condition. But in case of your like query, it's very dangerous to take a Single() statement, because that implies that you only find 1 result. In case of more results, you'll receive a nice exception :)

So I would prefer using FirstOrDefault() instead of Single():

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