Calling user defined functions in Entity Framework 4

Question

I have a user defined function in a SQL Server 2005 database which returns a bit. I would like to call this function via the Entity Framework. I have been searching around and haven't had much luck.

In LINQ to SQL this was obscenely easy, I would just add the function to the Data context Model, and I could call it like this.

bool result = FooContext.UserDefinedFunction(someParameter); 

Using the Entity Framework, I have added the function to my Model and it appears under SomeModel.Store\Stored Procedures in the Model Browser.

The model has generated no code for the function, the XML for the .edmx file contains:

<Function Name="UserDefinedFunction" ReturnType="bit" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">     <Parameter Name="someParameter" Type="int" Mode="In" /> </Function> 

The closest I could get was something like this:

bool result = ObjectContext.ExecuteFunction<bool>(     "UserDefinedFunction",     new ObjectParameter("someParameter", someParameter) ).First(); 

But I got the following error message:

The FunctionImport 'UserDefinedFunction' could not be found in the container 'FooEntities'.

Names have been changed to protect the innocent.

tldr: How do I call scalar valued user defined functions using Entity Framework 4.0?

Answer 1

I have finally worked it out :D For scalar functions you can append the FROM {1} clause.

bool result = FooContext.CreateQuery<bool>(     "SELECT VALUE FooModel.Store.UserDefinedFunction(@someParameter) FROM {1}",     new ObjectParameter("someParameter", someParameter) ).First(); 

This is definitely a case for using LINQ to SQL over EF.

Answer 2

If you want to call table-valued function in MS SQL via Entity Framework; You can use this:

var retval = db.Database.SqlQuery<MyClass>(String.Format(@"select * from dbo.myDatabaseFunction({0})", id));