CODEWITHSUNDEEP
c#linqentity-framework
andrjfrks
andrjfrks

Reputation: 397

Using my own method with LINQ to Entities

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.

Upvotes: 19

Views: 8425

Answers (2)

Sven
Sven

Reputation: 22703

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.

Upvotes: 15

Botz3000
Botz3000

Reputation: 39650

LINQ to Entities tries to translate every called method into a part of the database query that is executed. When trying to translate your method, it fails because it doesn't know how to represent it in SQL.

You can get around it by either

  • Executing the query and filter it after materializing the results (bad idea though if you are handling large datasets), or
  • Use only supported methods (better approach IMO). You can find a list of supported methods here:
    CLR Method to Canonical Function Mapping
    As the methods String.Replace() and String.ToUpper() are supported, it should be easy to replace your custom method with it.

Upvotes: 7

Related Questions