Calling UDFs from Entity Framework - Not what you might have expected

Saturday, November 29 2008 - Data Access

Along with Stored Procedures, you can bring UDFs into an EF Entity Data Model which also surfaces as a function in SSDL.

Unlike Stored Procedures, however, a UDF is composable on the server side and this is reflected in its attributes.

<Function Name="ufnLBtoKG" 
          ReturnType="nvarchar"
          Aggregate="false" 
          BuiltIn="false"  
          NiladicFunction="false"
          IsComposable="true"
          ParameterTypeSemantics="AllowImplicitConversion"
          Schema="dbo">
  <Parameter Name="Pounds" Type="int" Mode="In" />
</Function>

But the differences don't end there.

Implementing a UDF and querying with a UDF is not documented anywhere that I can find except that I finally found a hint buried in an MSDN forum thread.

The compiler also gave me a few hints when I was doing things incorrectly.

1) UDFs do not get mapped back to the CSDL via function mapping. This is because of their composability. You need to call them directly from SSDL which is unlike any other querying in Entity Framework.

2) Like many of the mapped functions that come from stored procedures, you can only call these from Entity SQL.

Mapped functions that return an entity can be called from the objectcontext. Everything else must be called via Entity SQL.

Here is an Entity SQL expression that uses the ufnLBtoKG function.

SELECT c.LastName, c.WeightInPounds, MyModel.Store.ufnLBtoKG(c.WeightInPounds)
FROM MyEntities.Contacts AS c

The function is called using its strongly typed name but from the store layer, not the conceptual layer.

Since it took me a while to figure this out,  I thought I would save someone that aggravation.

If you follow what feels like the normal steps and map the ufnLBtoKG function back to the CSDL, when EF attempts to execute a query that uses it you get this exception message:

A FunctionImport is mapped to a storage function 'MyModel.Store.ufnLBtoKG' that can be composed.
Only stored procedure functions may be mapped.

That's pretty clear although it confused me at first because I didn't understand how to call the function yet but I finally got it worked out.

Tagged as:

#1 Ernesto on 2.13.2009 at 7:06 PM

Sorry... I still can't understand how is the way to add the UDF into the Entity, I have this block in my .edmx <Function Name="GetNewId" ReturnType="uniqueidentifier" Aggregate="false"BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" />I really need to use such function, but the compiler returns such message: A FunctionImport is mapped to a ......What do i need to do in order to include the funcion into the .edmx? How could I edit the SSDL? This application was made with LINQ to SQL, so now I want to migrate to Entity Framework.

#2 Mehmet on 8.03.2009 at 8:00 AM

I also didnt understand anything, how can i add a function to edmx and how can i use it from code?

#3 Mike on 8.14.2009 at 11:51 AM

Good thing this makes sense to the article writer because it makes no sense to me.

Note to author: You're not helping the world by writing vague blog entries and not answering questions.

#4 Julie on 8.14.2009 at 12:21 PM

Sorry I didn't notice when the other comments came in.

If you want the UDF to come into the EDMX file you have to select it in the EDM wizard along with other objects. UDFs are listed under stored procedures.

Once you've done that the UDF is in the EDMX along with the table descriptions and any other functions (e.g. representing stored procedures).

You do not need to do anything else to the UDF. This is wierd because with the functions from stored procs you DO.

You will not see it in the model in the designer. It is not part of an entity. It is a function that you can use when you write a query.

You cannot call these UDF functions from LINQ to Entities. You can only use them in Entity SQL.

See the above blog post for an example of what a function in SSDL looks like and how to call it in ENtity SQL.

This explanation is no different than the blog post except that I have not bothered with all of the extra explanation.

Hopefully that is more useful.

And Mike, I answer hundreds of comments on this blog and my other, hundreds of forum questions on MSDN and O'Reilly and hundreds of emails that come to me from strangers. So perhaps you'll cut me a little slack, eh?

#5 Mike on 8.14.2009 at 12:41 PM

Fair enough - I'm just frustrated with this problem. Sorry for being rude.

#6 Julie on 8.14.2009 at 12:46 PM

Thanks for that, Mike.

Does it make more sense now? If not, just let me know what's missing or where you are getting stuck.

#7 Mike on 8.14.2009 at 4:04 PM

I have a UDF called GetLocalDateTime(@UTCDateTime, @userId), which returns the user's local time as a datetime scalar.

I want to call it like a entity UDF, but can't due to the current limitation. From what I've read, I can either user the Framework Extensions, modify the .edmx file, or wait for .Net 4.

#8 Julie on 8.16.2009 at 9:21 AM

@Mike-

That would be a good reason to be frustrated as you are painted into a corner there with some unappealing solutions. I can't tell you more than what you've already learned. I'll guess you've been web searching and have already found things such as this MSDN forum thread: social.msdn.microsoft.com/.../860d6fd5-dc9d-4

#9 Abe Park on 10.30.2009 at 6:40 PM

Hi Julie,

Can you please explain the the query:

SELECT c.LastName, c.WeightInPounds, MyModel.Store.ufnLBtoKG(c.WeightInPounds)

FROM MyEntities.Contacts AS c

In your example, is ufnLBtoKG a table-function? Is that why you call is by MyModel.Store.ufnLBtoKG(..)?

If you could explain your example query a bit further, that would be great.

In my case, I have scalar function that I want to want to call from my C# code.

Thank you,

Abe

#10 Julie on 10.30.2009 at 6:48 PM

Hi Abe

ufnLBtoKG is a user defined function, not a table function. EFv1 does not support table functions.

Does the additional explanation in earlier comments help you understand this better?

Julie

#11 Abe Park on 11.05.2009 at 1:38 AM

Hi Julie,

Thank you. I was just starting out with the Entity Framework; so, I did not understand the concept of store layer versus conceptual layer and a lot of things in the Entity Framework.

But after reading the earlier comments and doing some reading, it's perfectly clear now.

Thanks again!

Abe

Leave a Comment