RSS

Monthly Archives: October 2018

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
 
Leave a comment

Posted by on October 23, 2018 in General

 

Tags: , , , , ,

Karma: Only Run 1 Test

Useful hack to only run 1 test in Karma.

Instead of ‘it’, change test declaration to ‘fit’.
Example:

    fit('should create', () => {
        expect(component).toBeTruthy();
    });

This will force Karma to only run ‘should create’ test.

 
Leave a comment

Posted by on October 22, 2018 in General

 

Tags: , , , , , ,

How to Implement IExceptionLogger and IExceptionHandler in Web Api .Net

This works on Web Api 2+ and MVC 5+.

1. Create implementation of ExceptionHandler class. Note this class inherit from concrete class of ExceptionHandler, not the interface. Inherit from concrete class decrease effort to implement other methods.

using System.Threading;
using System.Threading.Tasks;
using System.Web.Http.ExceptionHandling;

namespace BlahBlahBlah.ExceptionHandler
{
    public class GlobalExceptionHandler : System.Web.Http.ExceptionHandling.ExceptionHandler
    {
        public override Task HandleAsync(ExceptionHandlerContext context, CancellationToken cancellationToken)
        {
            // Code to handle exception...

            return base.HandleAsync(context, cancellationToken);
        }
    }
}

2. Create implementation of ExceptionLogger class. Note this class inherit from concrete class of ExceptionLogger, not the interface. Inherit from concrete class decrease effort to implement other methods.

using System.Threading;
using System.Threading.Tasks;

namespace BlahBlahBlah.ExceptionHandler
{
    public class GlobalExceptionLogger : System.Web.Http.ExceptionHandling.ExceptionLogger
    {
        public override Task LogAsync(System.Web.Http.ExceptionHandling.ExceptionLoggerContext context, CancellationToken cancellationToken)
        {
            // Code to log exception...

            return base.LogAsync(context, cancellationToken);
        }
    }
}

3. Replace default Logger and Handler.

using System.Web.Http;
using System.Web.Http.ExceptionHandling;
using BlahBlahBlah.ExceptionHandler;

namespace BlahBlahBlah
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {            
            // Replace default exception handler and logger
            config.Services.Replace(typeof(IExceptionLogger), new GlobalExceptionLogger());
            config.Services.Replace(typeof(IExceptionHandler), new GlobalExceptionHandler());

            // Web API routes
            config.MapHttpAttributeRoutes();

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
        }
    }
}

If you wonder difference between IExceptionLogger and IExceptionHandler, this is from Microsoft docs:

We provide two new user-replaceable services, IExceptionLogger and IExceptionHandler, to log and handle unhandled exceptions. The services are very similar, with two main differences:

1. We support registering multiple exception loggers but only a single exception handler.
2. Exception loggers always get called, even if we’re about to abort the connection. Exception handlers only get called when we’re still able to choose which response message to send.

Both services provide access to an exception context containing relevant information from the point where the exception was detected, particularly the HttpRequestMessage, the HttpRequestContext, the thrown exception and the exception source (details below).

 
Leave a comment

Posted by on October 12, 2018 in General

 

Tags: , , , , , ,

 
%d bloggers like this: