Is it possible to convince the Entity Framework to make an expression when calling a method from a select?

Question

I'm using Entity Framework 6 (POCO, code first approach) and I have the following situation...

I have an entity that has a property which is an enumeration (called Status) - the value in the database is an integer and what I want to do is show to the user a localized description of this value.

I, therefore, have code that looks like this (actually, it calls into a ResourceManager and there are more status values but it is indicative):

public static string ToDisplayable(Status status) 
{
    switch(status)
    {
        case Status.One:
            return "Status ONE";
        case Status.Two:
            return "Status TWO";
        default:
            return "Unknown Status";
     }
}

...

var results = someIqueryable
    .ToList()
    .Select(r => new 
    { 
        Status = r.Status, 
        DisplayableStatus = ToDisplayable(r.Status) 
    });

Now, that works fine, because of course the good ol' toList has meant that all my stuff's been pulled back from the database before the Select is called. If I remove the ToList I get the following exception of course:

An exception of type 'System.NotSupportedException' occurred in mscorlib.dll but was not handled in user code

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

OK, fair enough, it is not able to translate my function call to SQL and I don't really blame it.

The thing is I really, really, really don't want that ToList in there because later on some Where clauses may (or may not) be added and, besides, I just don't need all the columns on my entity.

I think, therefore, that my only option is to do this:

var results = someIqueryable
    .Select(r => new 
    { 
        Status = r.Status, 
        DisplayableStatus = r.Status ==  Status.One
            ? "Status ONE"
            :  r.Status ==  Status.Two
                ? "Status TWO"
                : "Unknown Status"
     });

That is kind of ugly and tedious (there are about 15 status values in my real code) and, worst of all, I cannot reuse it (but it does work at least).

The beauty of the simple method at the beginning was that, because there are other entities with Status columns, that have the same possible values I could just call the same method. Now if I add a new status value for example I have to go a huntin' through my code for all the places that do this translation. Horrible.

So, does anyone know how I can create a reusable piece of code that can be translated to a SQL expression that I can plug into a Select method?

Can I weave some magic with an Expression perhaps?

score 0 · Answer 1 · answered Dec 10 '13 at 11:24

0

Isn't translating the enum value to human readable text something that the display code should take care of? Return the Enum and translate the Enumeration to a displayable value just before binding the data to the View.

In the worst case you can always do:

var results = someIqueryable
    .Where(...)
    .Select(r => new Status = r.Status, /* other column you're after */)
    .ToList()
    .Select(r => new 
    { 
        Status = r.Status, 
        /* other column you're after */,
        DisplayableStatus = ToDisplayable(r.Status) 
    });

answered Dec 10 '13 at 11:24

jessehouwing

106,458
22
256
341

Yeah, thanks, yes, ideally. The thing is that there is code which just expects an IQueryable which is what I need to return from my method that contains the query. It then appends more Where clauses and it cannot do the ToDisplayable call (as it doesn't know about that method) and I don't want to ToList before returning out of my method because way more will be pulled from the database than is needed. I need to return an IQueryable which has not been evaluated but that contains the logic for translating the status to a string. If that makes sense. – kmp Dec 10 '13 at 11:31
It makes sense, but it is a bad situation to be in.. Why not move the `ToDisplayable` method to a location where you can access it? Or create a `StatusFormatter` class to put it in? – jessehouwing Dec 10 '13 at 12:04
Or pass back the `IQueryable` to your class to do the formatting... Or have the calling code provide the Expression for the extra Where clauses that you want to apply... – jessehouwing Dec 10 '13 at 12:05
Sadly the calling class is Telerik's Kendo grid doohickey and they provide an extension method on IQueryable that applies the filters so I have a method (mine) that returns IQueryable and then I call their extension method, which will be in charge of adding where's, takes, skips etc (whatever filter/page the user has selected) and it evaluates the query (does not return back an IQueryable, but a real List, sadly) so the best I can do I suppose is call my method, their extension method and then apply the select afterwards, at least then the worst that can happen is I have extra columns. Thanks – kmp Dec 10 '13 at 12:17
That would be my approach in that case. Plus filing a feature request to Telerik ;). – jessehouwing Dec 10 '13 at 14:46
yeah, cheers, I'll likely do that, but just asked this question in case there is some clever trick I can play to not have to – kmp Dec 10 '13 at 15:30

kmp · Accepted Answer · 2014-03-11T10:00:15.627

I have figured out a way to do this. The project LinqKit has exactly what I need and even has a handy Nuget package so I did:

Install-Package LinqKit

I think the code in this library that I am using originally came from Tomas Petricek. I wish it were inside the main Entity Framework project, but anyway...

Now I have that library referenced I can create a helper:

public static class StatusHelper
{
    public static readonly Expression<Func<Status, string>> ToDisplayable = s => 
        s == Status.One
            ? "Status ONE"
            : s == Status.Two
                ? "Status TWO"
                : "Unknown Status";
}

And then use that helper like so:

// It is important to assign to a local variable here and
// not attempt to use the StatusHelper in the query
// see - http://stackoverflow.com/q/22223944/1039947
// Not ideal, of course, but I can live with it
var toDisplayable = StatusHelper.ToDisplayable;

var results = someIqueryable
    .AsExpandable()  // <-- This is needed for LinqKit
    .Select(x => new 
    { 
        Status = x.Status, 
        DisplayableStatus = toDisplayable.Invoke(x.Status)
    })
    .ToList();

As you can see, I can Invoke the expression in the Select and exactly the right thing happens in the query against the database.

Like this, I can easily share my little method around. I am, therefore, a happy bunny and, as you can see, I do not need to evaluate the query early either.

Is it possible to convince the Entity Framework to make an expression when calling a method from a select?

2 Answers2