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?

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

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

Solution 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();
   }
}

Solution 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).

Solution 5

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

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

View more solutions

Comments

[Evil Pigeon]

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

[Sulthan Allaudeen]

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

[devinbost]

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

[saqibahmad]

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

[Dave]

You are creating the list twice.

[Kircali]

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