Using my own method with LINQ to Entities

Question

I have a project with LINQ and I want to use my own method in it. This NoWhiteSpaces method should return upper string with no spaces.

public static class LittleExtensions
{
    public static string NoWhiteSpaces(this String s)
    {
        return Regex.Replace(s, @"\s", string.Empty).ToUpper();
    }
}

When I want to use this method with LINQ, like this:

static void GetHaendler()
    {
        using (var riaService = new gkmRia())
        {
            var hladany = "someone";
            var haendlers = from hndlr in riaService.GetGkmHaendlerOutlet()
                            where hndlr.NameOutlet.NoWhiteSpaces() == hladany.NoWhiteSpaces()
                            select hndlr;
            Console.Write(haendlers.First().NameOutlet);
        }
    }

I get this error message:

LINQ to Entities does not recognize the method 'System.String NoWhiteSpaces(System.String)' method, and this method cannot be translated into a store expression.

Any solution? Thank you for your time.

Answer 1

It is not possible to use custom methods or properties with LINQ to Entities, since LINQ to Entities needs to be able to translate the expression into a SQL statement which it can't do with your method.

A way to get around is to bypass LINQ to Entities and instead use LINQ to Objects by using Enumerable instead of Queryable (note the AsEnumerable() in the below code):

static void GetHaendler()
{
    using (var riaService = new gkmRia())
    {
        var hladany = "someone";
        var haendlers = from hndlr in riaService.GetGkmHaendlerOutlet().AsEnumerable()
                        where hndlr.NameOutlet.NoWhiteSpaces() == hladany.NoWhiteSpaces()
                        select hndlr;
        Console.Write(haendlers.First().NameOutlet);
    }
}

Note that this causes the query to execute in your application rather than in the database, so there may be some performance implications. If possible, it may be preferable to alter the query so it can be expressed using methods supported by the Entity Framework.

An alternative expression that is supported by LINQ to Entities could be as follows:

var hladany = "someone".NoWhiteSpaces();
var haenflers = from hndlr in riaService.GetGkmHaendlerOutlet().
                where hndlr.NameOutlet.Replace(" ", "").Replace("\t", "") == hladany
                select hndlr;

This example only handles spaces and tabs (the regex handles different whitespace characters as well), but I don't know your exact requirements so that may be sufficient. You can always chain more Replace calls to do additional replacements.