如何从EF LINQ查询中调用DB函数？

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:

在我的数据库中,我定义了一些函数,假设它叫做fnTest。是否可以从我的LINQ / EF查询中调用该函数?像这样的东西:

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.

如您所见,我需要在DB端执行该函数,因为我需要使用它返回的值对数据进行排序。前两个参数是表中的字段,后两个参数是一些将在程序中更改的数字,但对于每个查询将是常量。

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

是否有可能以某种方式调用已在DB中创建的函数?或者我需要使用这样的东西:

((IObjectContextAdapter) context).ObjectContext.CreateQuery

and write the query manually?

并手动编写查询?

2 个解决方案

#1

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

首先,必须首先在数据库中将fnTest创建为用户定义的函数:

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

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

然后在.edmx文件中,声明如下函数:

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

您现在可以在标准LINQ查询中使用此方法。

Further Reading

How to: Call Custom Database Functions

如何:调用自定义数据库函数

#2

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

对实体使用sql查询。有关exaples的信息,请访问msdn:http://msdn.microsoft.com/en-us/data/jj592907.aspx

#1