Calling User-Defined Functions (UDFs) in Entity Framework

Calling User-Defined Functions (UDFs) in Entity Framework

Yesterday I answeredCalling User-Defined Functions (UDFs) in Entity Framework
a question in Entity Framework Forum  in
regard of how to use
User-Defined Functions
(UDFs) in Entity Framework.
This post holds the answer.

The Problem

I have a UDF in my database that I want to use with Entity Framework.
When you use the Entity Data Model Wizard you can surface the function
in the SSDL. When you will try map the created function element to a
FunctionImport you’ll get the following exception:
”Error 2054: A FunctionImport is mapped to a storage function
‘MyModel.Store.FunctionName that can be composed. Only stored
procedure functions may be mapped.”

So how can we use the UDF?

Calling User-Defined Functions (UDFs) in Entity Framework

The only way to call the function is using Entity SQL. Since the function mapping
sits in the SSDL then it can be used by Entity SQL in order to get back the needed
results. So if we have the following UDF:

CREATE FUNCTION dbo.GetPersonType(@PersonID int)
RETURNS tinyint
As
BEGIN
DECLARE @personType tinyint
SELECT @personType = PersonType from Person where PersonID = @PersonID
RETURN @personType
END

We will be able to use it after mapping it into the EDM using the EDM Wizard.

The following code show how to use the imported function:

using (var context = new SchoolEntities())
{
    var sql = "SELECT VALUE SchoolModel.Store.GetPersonType(p.PersonID) FROM SchoolEntities.People AS p";
    var query = context.CreateQuery<byte>(sql);
    foreach (var type in query)
    {
        Console.WriteLine(type);
    }
}
Console.ReadLine();

Summary

Lets sum up, there is no way to use UDF as FunctionImport in Entity Framework

currently. The work around is to use the imported functions with Entity SQL.

The post demonstrated how to do such a thing.

Another solution to use UDF in your model is by using DefiningQuery elements

which you can read about this element .

 source:http://blogs.microsoft.co.il/gilf/2009/10/20/calling-user-defined-functions-udfs-in-entity-framework/