LINQ Use "like" instead of "(( NVL(INSTR(x, y), 0) ) = 1)"

Question

When using .Contains()/.StartsWith()/.EndsWith() the generated SQL looks like this :

(( NVL(INSTR(x, y), 0) ) = 1)

Is there a way of using this instead :

LIKE 'x%' or '%x%' or '%x'

Because there is a huge cost difference between those two in the execution plan of the query (44 000 vs 30).

This is pretty much down to the Oracle provider you are using. Which one is it? — DavidG, Jun 12 '18 at 12:37
No, that's Entity Framework, the provider is the library responsible for sitting between ED and the database. — DavidG, Jun 12 '18 at 12:44
A list of provider [here](https://stackoverflow.com/questions/1865352/a-list-of-entity-framework-providers-for-various-databases) on so to help you clarify this point. Perhaps one of them will ring the bell and help the clarification. — Drag and Drop, Jun 12 '18 at 12:46
Can you wrap your query in a stored procedure? They provide the greatest performance improvement in most cases. — Captain Kenpachi, Jun 12 '18 at 13:03
I don't think `x LIKE '%y'` or `x LIKE '%y%'` would be substantially better than using `INSTR(x,y)` in this instance - `x LIKE 'y%'` definitely would be as long as the column in question is indexed. So the real issue is `StartsWith()`. — David Faber, Jun 12 '18 at 13:55

score 1 · Accepted Answer · answered Jun 12 '18 at 12:58

When i look around abit i found LIKE operator in LINQ which were a which have a few good examples of how you could do this. I have tested the one below that was from the link above

Here is a extension for using Like with lambda that was posted by adobrzyc

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

To use it you you just simply add Like function where you would put the Contains functions. You can see below for a example

            using (CustomerEntities customerContext = new CustomerEntities())
            {
                IQueryable<Customer> customer = customerContext.Customer.Like(x => x.psn, "%1%");    
            }

This will create a sql query that looks something like this.

SELECT 
[Extent1].[psn] AS [psn]
FROM [dbo].[Customer] AS [Extent1]
WHERE [Extent1].[psn] LIKE '%1%'

LINQ Use "like" instead of "(( NVL(INSTR(x, y), 0) ) = 1)"

1 Answers1

Linked