Calling User-Defined Functions (UDFs) in Entity Framework

October 20, 2009

one comment

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 here.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

one comment

  1. Steve CallNovember 15, 2009 ב 14:15

    Thanx for this well written post. But I had trouble navigating past your site as I kept getting 502 bad gateway error. Just thought to let you know.

    Reply