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

发布于 2024-12-14 10:09:51 字数 1064 浏览 0 评论 0原文

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

我尝试谷歌，但找不到任何关于如何做到这一点的具体答案。

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

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

存储过程：

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

灯角 2024-12-21 10:09:51

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

回复收藏 0 原文

余罪 2024-12-21 10:09:51

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

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

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

有关此问题的更多详细信息，请参阅此处

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

回复收藏 0 原文

~没有更多了~