Geek Answers Handbook

How to call DB function from EF LINQ query?

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

 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 this function to execute on the database side, because I need to sort the data using the value that it returns. The first two parameters are the fields from the table, and in the second there are some numbers that will change in the program, but will be constant for each request.

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

 ((IObjectContextAdapter) context).ObjectContext.CreateQuery

and write a request manually?

+6
source share
2 answers

First, fnTest must first be created as a user-defined function in the database:

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

Then, in your .edmx file, declare the function as follows:

 <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 as follows:

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

Now you can use this method in standard LINQ queries.

additional literature

+6
source

Use sql queries for objects. For exaples, see msdn: http://msdn.microsoft.com/en-us/data/jj592907.aspx

+1
source

More articles:


All Articles