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")