Fix Linq Generate ‘ESCAPE’ Keyword on Contains/StartsWith/EndsWith Predicate and Cause Performance Hit

When using Linq to Entity (or Linq to SQL) with these predicates, Linq will generate ‘ESCAPE’ keyword in SQL statement which cause performance hit.

This code:

public IEnumerable GetUser(string filter)
{
    using (var db = new SomeEntities())
    {
        var result = db.User
            .Where(u => u.DisplayName.Contains(filter))
            .ToList();
    }
}

Will generate this sql statement:

SELECT [Extent1].[DisplayName] AS [DisplayName]
FROM   (SELECT [User].[DisplayName] AS [DisplayName]
        FROM   [dbo].[User] AS [User]) AS [Extent1]
WHERE  [Extent1].[DisplayName] LIKE '%garlan%' /* @p__linq__0 */ ESCAPE '~'

The `ESCAPE ‘~’` could potentially cause performance degradation.

Solution to this issue is to pass in constant into Contains predicate. It’s easier said than done.
If we have method like `GetUser` above which take ‘filter’ parameter, it’s not possible to convert to constant.
To overcome this, we have to create method that return predicate with constant.

This solution is proposed here.

Summary is to create extension class.

using System;
using System.Linq.Expressions;

namespace Blahblahblah
{
    public static class PredicateConstantCreator
    {
        public static Expression EmbedConstant(this Expression expression, TConstant constant)
        {
            var body = expression.Body.Replace(expression.Parameters[1], Expression.Constant(constant));

            return Expression.Lambda(body, expression.Parameters[0]);
        }

        private static Expression Replace(this Expression expression, Expression searchEx, Expression replaceEx)
        {
            return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
        }

        internal class ReplaceVisitor : ExpressionVisitor
        {
            private readonly Expression from;
            private readonly Expression to;

            public ReplaceVisitor(Expression from, Expression to)
            {
                this.from = from;
                this.to = to;
            }

            public override Expression Visit(Expression node)
            {
                return node == this.from ? this.to : base.Visit(node);
            }
        }
    }
}

And use it like this:

Expression predicate = (item, filterTerm) => item.DisplayName.Contains(filterTerm);
var result = User
    .Where(predicate.EmbedConstant(filter))
    .ToList();

Linq will generate sql statement like this:

SELECT [Extent1].[DisplayName] AS [DisplayName]
FROM   (SELECT [User].[DisplayName] AS [DisplayName]
        FROM   [dbo].[User] AS [User]) AS [Extent1]
WHERE  [Extent1].[DisplayName] LIKE '%garlan%' /* @p__linq__0 */

Alternative solution is to add function to generated XML file in EDMX. This solution is lay out here.

Advertisements