EF: intColumn.ToString() doesn't work in LINQ to Entities, SqlFunctions.StringConvert outside

As you may already know LINQ to Entities will not let you do something like

  "Blah blah blah " + t.charColumn + " (" + t.intColumn.ToString() + ")"

in a query. What quite a few do not know is that you can instead use 

  "Blah blah blah " + t.charColumn + " (" + System.Data.Objects.SqlClient.SqlFunctions.StringConvert(t.intColumn) + ")"

there is a catch though ... this will ONLY work inside a query! So if you happen to have an entity already, you can't use this ... it would probably be too hard to implement, much harder than throwing an exception ...

Why do I care? Generated code. Suppose you have quite a few lookup tables (countries, types of this, types of that, regions, ... anything really) and you need to populate the pulldowns. Sometimes it's enough to display one property, sometimes you want to do something a bit more complicated ... like include the ID in the displayed text. To simplify things for the developers you extend the EF designer so that they could specify that an entity may be turned into a dropdown and what's the property/expression to display. And the tweaked .tt file generates the interface implementation and the extension methods that turn an IQueryable of entities to an IEnumerable<SelectListItems> etc. etc. etc. With the extension methods written so that we do not fetch the whole entities, but rather Select() just what we need. 

And here comes the catch of course. The same code to compute the text to display is used in

 query.Select(e => new {Value = e.Id, Text = HERE}).AsEnumerable().Select(s => new SelectListItem{Id = s.Id, Text = s.Text})

and in

 string IListItem.Text { { get { return HERE } }

where the later is used when I already have an entity and need to display the text as in the drop down.

And seems if I need to include the ID in the text there is no solution that'd work both in LINQ to Entities and outside.

Or am I wrong?

Yes, I am wrong. There is a solution. The same one I used to allow me to use LIKE in LINQ to Entities queries. 

If I add this into the .edmx into <edmx:Runtime><edmx:ConceptualModels><Schema> :

<Function Name="Int32_SqlToString" ReturnType="Edm.String" ef4ex:RenameTo="SqlToString">
  <Parameter Name="val" Type="Edm.Int32" />
  <DefiningExpression>
   Cast(val as Edm.String)
  </DefiningExpression>
  <ef4ex:CodeBlock>
   return val.ToString();
  </ef4ex:CodeBlock>
 </Function>

and 

[System.Data.Objects.DataClasses.EdmFunction( "Xxxx.Models", "Int32_SqlToString")]
 public static String SqlToString(this Int32 val) {
  return val.ToString();
 }

into a static class (it's actually generated using the data from the ef4ex tags and attributes), then I can use

 intColumn.SqlToString()

anywhere.

The only problem is that in a project where I'd need two different models based on two .edmx files I'd have to define the function twice and then could only import one or the other namespace and there's be nothing preventing me from using the wrong .SqlToString() triggering a run time error.

To solve that I would have to be allowed to put several [System.Data.Objects.DataClasses.EdmFunction()] attributes to one extension method and the LINQ to Entities would have to select the right EntitySQL implementation based on the namespaceName.

It's a shame there is (as far as I know) no way to attach the EdmFunction mapping to a method written by someone else in a way the LINQ to Entities provider would understand and thus give it a hint how to convert the method into the SQL :-(

The specified method 'xxx' on the type 'yyy' cannot be translated into a LINQ to Entities store expression because the instance over which it is invoked is not the ObjectContext over which the query in which it is used is evaluated.

I have a method defined in the partial class for one of the entities like this:

public bool IsAnonymous() {
  return (this.Roles_ & UserRoles.Registered) == 0;
 }

This of course doesn't work within LINQ to Entities queries, because the EF provider for LINQ doesn't know how to translate that to SQL so that it could send that to the database. No problem according to the docs. You can define a "Model Defined Function" and tell the EF to use that:

        <Function Name="IsAnonymous" ReturnType="Edm.Boolean" >
          <Parameter Name="user" Type="Kosmas.Models.User"/>
          <DefiningExpression>
            BitWiseAnd(user.Roles, 1) = 0

         </DefiningExpression>

        </Function>

and

[System.Data.Objects.DataClasses.EdmFunction("Kosmas.Models", "IsAnonymous")]
 public bool IsAnonymous() {
  return (this.Roles_ & UserRoles.Registered) == 0;
 }

Or can you?


Well you can't. If you try this you get a very informative error: "The specified method 'Boolean IsAnonymous()' on the type 'Kosmas.Models.User' cannot be translated into a LINQ to Entities store expression because the instance over which it is invoked is not the ObjectContext over which the query in which it is used is evaluated."


A quick Google search did not get anything useful. The only suggestions were to either change the method so that instead of a User instance I call it on the context (making the syntax rather ... silly). Or use Entity SQL (which I'd rather not either).


So I tried whether the example in Programming Entity Framework book actually works and noticed one difference. In the example they were adding the [EdmFunction] attribute to an extension method, while I'm adding it to a plain old ordinary one.


OK, let'ts try that. Let's remove the IsAnonymous method from the partial class and add

public static class Functions {
  [System.Data.Objects.DataClasses.EdmFunction("Kosmas.Models", "IsAnonymous")]
  public static bool IsAnonymous(this User obj) {
   return (obj.Roles_ & UserRoles.Registered) == 0;
  }
 }
et voila, it works.


I would not call this bug ... if it wasn't. But it is!


Anyway, if you do get the nonsensical error message, check whether the method is an ordinary method or an extension one. And see if you can change it from one to the other.