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

问题描述

在我的数据库中，我定义了一些函数，假设它称为 fnTest 。
是否可以从我的LINQ / EF查询中调用该函数？像这样：

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

并手动编写查询？

推荐答案

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

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

然后在.edmx文件中，声明如下函数：

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.");
}

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

You can now use this method in standard LINQ queries.

进一步阅读

• How to: Call Custom Database Functions

这篇关于如何从EF LINQ查询中调用数据库函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！