SQL function in lambda expression in EF "cannot be translated into a store expression"/ "cannot be resolved into a valid type or function"

Question

I have a SQL function which I want to use in a lambda expression using the Entity Framework. Let's assume the SQL server function is "fooFunction". I have mapped this function in the Conceptual Model of the EDMX as follows:

<Function Name="fooFunction" ReturnType="Edm.String">
   <Parameter Name="param1" Type="Edm.Int32" />
   <Parameter Name="param2" Type="Edm.String" />
   <DefiningExpression>
     dbo.fooFunction(param1, param2)
   </DefiningExpression>
 </Function>

I have a static class in which I have mapped this function to code as follows:

[EdmFunction("Model", "fooFunction")]
   public static string fooFunction(int param1, string param2)
   {
       throw new NotImplementedException("Direct calls are not supported.");
   }

However, when I try to call this method (on a Compiled Query), I get the "cannot be translated into a store expression" error:

CompiledQuery.Compile<...> ....  .Where(fooFunction(0,"")=="value")

I've been scratching my head for a while but I just don't get it.

Can anybody help me out here? Thanks!

Edit: whoops, I had a typo in my EdmFunction-attribute (whitespace):

[EdmFunction("Model", " fooFunction")]

However, now I get the "fooFunction cannot be resolved into a valid type or function."-error.

score 2 · Answer 1 · answered May 29 '12 at 10:00

Thanks for your contribution, Dennis. However, this is not the answer to the problem. I want to use a UDF, not a Stored Procedure.

Finally figured it out: I had to add a mapping to the SQL UDF in the storage model, as explained here: http://blogs.msdn.com/b/alexj/archive/2009/08/07/tip-30-how-to-use-a-custom-store-function.aspx

Code is similar to the mapping in the conceptual model:

<Function Name="fooFunc"
  IsComposable="true"
  Schema="dbo"
  Aggregate="false"
  BuiltIn="false"
  ReturnType="nchar">
  <Parameter Name="param1" Type="int" />
  <Parameter Name="param2" Type="nchar" />
</Function>

I had to tweak the ReturnType and Parameter Types a bit. I found out they must match the datatypes of the mapped UDF. (native SQL datatypes instead of .NET datatypes)

Unfortunately the link in your comment is 404's, but I found another stackoverflow answer that answers this question: https://stackoverflow.com/a/20709097/3303251 — Justin C, Apr 08 '21 at 17:26

score -1 · Answer 2 · answered May 29 '12 at 07:49

-1

You're confusing with Function (defined by an Entity SQL expression) with FunctionImport (mapped to a stored proc in data source by name), which have to be used in your case.

answered May 29 '12 at 07:49

Dennis

37,026
10
82
150

SQL function in lambda expression in EF "cannot be translated into a store expression"/ "cannot be resolved into a valid type or function"

2 Answers2