How to call Scalar-valued function from LINQ to Entities server-side

Viewed 12091

I have a Scalar-valued function in my DB:

ALTER FUNCTION [dbo].[fx_fooFunct]
  (@MyParam varchar(max))
RETURNS varchar(max)
AS
BEGIN
  return @MyParam
END

I want to call this function from a LINQ to Entities query and get the result into a variable:

let result = this.ObjectContext.ExecuteFunction<string>("SELECT dbo.fx_fooFunct(@MyParam)", new ObjectParameter("MyParam", "hello world")).FirstOrDefault()

But, when I execute the code, I get this error:

LINQ to Entities does not recognize the method 'System.Data.Objects.ObjectResult`1[System.String] ExecuteFunction[String](System.String, System.Data.Objects.ObjectParameter[])' method, and this method cannot be translated into a store expression.

Other Info:

This is part all of a query running on the server.
Returning all the data and using LINQ to Objects is not an option due to performance.

I'm not sure that I have the return type of the ExecuteFunction correct, but I'm not sure what else it could be... What am I doing wrong?

Edit
With the help of Ladislav Mrnka's answer, here is the solution:

Create helper method exposing the SQL function:

public class CustomSqlFunctions
{
    [EdmFunction("MyModel.Store", "fx_fooFunct")]
    public static string FooFunct(string myParam)
    {
        throw new NotSupportedException("Direct calls not supported");
    }
} 

The LINQ should now read:

let result = CustomSqlFunctions.FooFunct("hello world")
2 Answers
Related