Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
485 views
in Technique[技术] by (71.8m points)

c# - How to call DB function from EF LINQ query?

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...