Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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")
like image 885
mcqwerty Avatar asked Aug 09 '11 15:08

mcqwerty


People also ask

How do you execute a scalar valued function?

Scalar-valued functions can be executed by using the EXECUTE statement. If you EXECUTE a function rather than use it in a SELECT statement or constraint, you can leave out the schema name in the function name, and it will look in the dbo schema followed by the users default schema.

How do you call a function in Entity Framework?

Step 1: Create an entity class which inherits “DbContext” class. Step 2: The following is the structure of the database with table and stored procedure. Step 3: Create a class to store the returned tabular value. Step 4: Create an object for the entity above and method to call a function.


1 Answers

Do you have your function mapped in EDMX? I guess you don't.

Run Update from database wizard in the designer and under stored procedures select your SQL function to import and follow this article to create helper method marked with EdmFunctionAttribute to expose the SQL function for LINQ-TO-Entities.

Note: SQL functions are not supported in code-first / fluent-API. You need to use mapping with EDMX.

ExecuteFunction is used to call features mapped in EDMX - it expects name of the mapped feature (function imports of stored procedures). MSDN says that it can also call mapped functions but I don't know how - it calls function import and SQL function import doesn't have any.

like image 75
Ladislav Mrnka Avatar answered Oct 03 '22 05:10

Ladislav Mrnka