如何SQL用户定义函数添加到实体框架？ [英] How to add SQL user-defined function to entity framework?

问题描述

我可以添加一个SQL函数到我的.edmx文件就像我在做的.dbml？如果可以，该怎么做？如果我不能，有没有什么解决方法吗？

我试图谷歌，但无法找到该怎么做任何具体的答案。

根据我创建了一个存储过程并尝试添加导入功能给出的答案，但它说的存储过程返回没有列'。我在哪里做错了吗？
功能：

  ALTER FUNCTION [DBO]。[fn_locationSearch]（@关键字VARCHAR（10））
退货表
如
返回
（
选择CustomerBranch.ID,CustomerBranch.BranchName,CustomerBranch.Longitude,CustomerBranch.Latitue,CustomerBranch.Telephone,CustomerBranch.CategoryID,CustomerBranch.Description从FREETEXTTABLE（CustomerOffer，*，@关键字）ABC INNER JOIN OffersInBranch
在ABC [关键] = OffersInBranch.OfferID INNER JOIN CustomerBranch ON OffersInBranch.BranchID = CustomerBranch.ID
联盟
选择CustomerBranch.ID,CustomerBranch.BranchName,CustomerBranch.Longitude,CustomerBranch.Latitude,CustomerBranch.Telephone,CustomerBranch.CategoryID,CustomerBranch.Description
从CustomerBranch WHERE FREETEXT（*，@关键字）
）
 

存储过程：

  ALTER PROCEDURE USP_locationSearch
（@Keyword VARCHAR（10））
如
开始
从dbo.fn_locationSearch SELECT *（@Keyword）
结束
 

解决方案

有没有内置支持实体框架SQL用户定义函数，你最好的办法是创建一个存储过程，它封装了函数调用，并返回其输出，然后添加程序到你的EF模型。

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?

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

解决方案

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.

这篇关于如何SQL用户定义函数添加到实体框架？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！