How to call DB function from EF LINQ query?

Question

In my database I have define some function, let's say it's called fnTest. Is it possible to call that function from my LINQ/EF query? Something like this:

var param3, param4;
var res = (from x in db.Something
           where x.field1 > 0
           orderby fnTest(x.f1, x.f2, param3, param4)
           select x).Take(20);

As you can see, I need that function to execute on DB side because I need to sort data using value that it returns. First two parameters are fields from the table, and second two parameters are some numbers that will change in program, but will be constant for each query.

Is it possible to somehow call function that is already created in the DB? Or do I need to use something like this:

((IObjectContextAdapter) context).ObjectContext.CreateQuery

and write the query manually?

Answer 1

Use sql queries for entities. For exaples look on msdn: http://msdn.microsoft.com/en-us/data/jj592907.aspx

Answer 2

First off, fnTest will have to be created as a user-defined function in the database first:

CREATE FUNCTION [fnTest] (@fi int, @f2 int, @param3 int, @param4 int)
RETURNS int
AS ...

Then in your .edmx file, declare the function like this:

<Function Name="fnTest" ReturnType="int" Schema="dbo" >
    <Parameter Name="f1" Mode="In" Type="int" />
    <Parameter Name="f2" Mode="In" Type="int" />
    <Parameter Name="param3" Mode="In" Type="int" />
    <Parameter Name="param4" Mode="In" Type="int" />
</Function>

Now you can bind this function to a method in your model like this:

[EdmFunction("MyNamespace", "fnTest")]
public static int fnTest(int f1, int f2, int param3, int param4)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

You can now use this method in standard LINQ queries.

Further Reading

• How to: Call Custom Database Functions