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?

Japha

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.

Japha

Or you can just use a table-valued function that returns only one row value instead. That will suffice in quite a few situations. – Suckow

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));

Bobsledding

You are creating the list twice. – Emmett

It was like description but you're right. I fixed this. – Bobsledding

Answer 3

calling user defined static type SQL function

You can use ExecuteStoreQuery,

• first parameter takes the SQL function calling statement in string format
• second parameter takes an object of SqlParameter class in which you pass your function parameter name with its value. as shown in the below method

public  string GetNumberOFWorkDays(Guid leaveID)
{
  using (var ctx  = new INTERNAL_IntranetDemoEntities())
  {
    return ctx.ExecuteStoreQuery<string>(
                  "SELECT [dbo].[fnCalculateNumberOFWorkDays](@leaveID)",
                  new SqlParameter { ParameterName = "leaveID", Value = leaveID }
               ).FirstOrDefault();
   }
}

Tierza

Please give your answer in details. Because only code based answers are not appreciated. – Footlambert

this is what i am looking for, how to call user define functions – Baler

Answer 4

Calling a function in EF4 with ODPNET beta 2 (Oracle), which returns a NUMBER:

using (var db = new FooContext())
{
    var queryText = "SELECT FooModel.Store.MY_FUNC(@param) FROM {1}"
    var result = db.CreateQuery<DbDataRecord>(queryText,new ObjectParameter("param", paramvalue));
    return result.First().GetDecimal(0);
}

I'm adding it here because based on Evil Pigeon's code I could figure it out for Oracle. (Also upvoted his answer).

Central

Answer 5

ObjectResult result = context.ExecuteStoreQuery("select EmployeeName from User where Id = {0}", 15);

http://msdn.microsoft.com/en-us/library/ee358758.aspx

Stutman

Answer 6

You might find this helpful. It would appear that you can only call them via eSQL directly and not using Linq.

Wiggler

I found that article a couple of hours ago, the comments below the article echo my frustration. I have had some success using Entity SQL, although I get a syntax error when I exclude the FROM clause (which is not required for scalar functions). – Japha

Answer 7

thought id add a note for anybody else that come across this, if your sql function parameter is nullable you have to call the function in sql with parameter value of "default". To call a function like that using Entity Framwork try

bool result = FooContext.CreateQuery<bool>(
"SELECT VALUE FooModel.Store.UserDefinedFunction(null) FROM {1}"
).First();

Popgun