Calling User-Defined Functions (UDFs) in Entity Framework

Calling User-Defined Functions (UDFs) in Entity Framework

Yesterday I answered
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 here.