如何将SQL用户定义的函数添加到实体框架？

Can I add a SQL function to my .edmx file like I do in .dbml? If I can, how to do that? If I can not, is there any workaround?

我可以像在.dbml中一样在我的.edmx文件中添加SQL函数吗？如果可以的话，怎么做？如果我不能，是否有任何解决方法？

I tried to Google but can't find any concrete answer about how to do that.

我试图谷歌，但没有找到任何具体的答案，如何做到这一点。

Based on the given answer I have created a Stored procedure and tried to add a 'import function', but it says 'the stored procedure returns no column' . Where am I doing wrong? The function:

基于给定的答案，我创建了一个存储过程，并尝试添加“导入函数”，但它表示“存储过程不返回列”。我哪里做错了？功能：

ALTER FUNCTION [dbo].[fn_locationSearch](@keyword varchar(10))
RETURNS TABLE
AS
RETURN
(
SELECT CustomerBranch.ID,CustomerBranch.BranchName,CustomerBranch.Longitude,CustomerBranch.Latitue,CustomerBranch.Telephone,CustomerBranch.CategoryID,CustomerBranch.Description

FROM FREETEXTTABLE (CustomerOffer,*,@keyword) abc INNER JOIN OffersInBranch
ON abc.[key]=OffersInBranch.OfferID INNER JOIN CustomerBranch ON     OffersInBranch.BranchID=CustomerBranch.ID
UNION
SELECT    CustomerBranch.ID,CustomerBranch.BranchName,CustomerBranch.Longitude,CustomerBranch.Latitude,CustomerBranch.Telephone,CustomerBranch.CategoryID,CustomerBranch.Description
FROM CustomerBranch WHERE FREETEXT(*,@keyword)
)

The Stored procedure:

存储过程：

ALTER PROCEDURE USP_locationSearch
(@keyword varchar(10))
AS
BEGIN
SELECT * from dbo.fn_locationSearch(@keyword)
END

2 个解决方案

#1

There is no built in support for SQL User Defined Functions in Entity Framework, your best approach would be to create a stored procedure which wraps the function call and returns its output, then add that procedure to your EF model.

在Entity Framework中没有对SQL用户定义函数的内置支持，最好的方法是创建一个包装函数调用并返回其输出的存储过程，然后将该过程添加到EF模型中。

#2

I solved the issue. What I did is ,I put the result of my Stored procedure into a Table variable and select from there.

我解决了这个问题。我做的是，我将我的存储过程的结果放入一个表变量并从那里选择。

ALTER PROCEDURE [dbo].[USP_locationSearch]
(@keyword varchar(10))
AS
BEGIN
DECLARE @locationtable TABLE
(
ID int,
BranchName varchar(150),
Longitude varchar(150),
Latitude varchar(150),
Telephone varchar(50),
CategoryID int,
Description varchar(500)
)
INSERT INTO @locationtable SELECT * from dbo.fn_locationSearch(@keyword)
SELECT * FROM @locationtable
END

Then refresh the stored procedure in the entity framework. Then add 'function input'. everything went cool.

然后刷新实体框架中的存储过程。然后添加'功能输入'。一切都很酷。

more details on this issue can be found here

关于这个问题的更多细节可以在这里找到

#1