CODEWITHSUNDEEP
asp.netsql-serverlinqentity-framework-4
Reza.Hoque
Reza.Hoque

Reputation: 2750

How to add SQL user-defined function to entity framework?

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

Upvotes: 7

Views: 13178

Answers (2)

Reza.Hoque
Reza.Hoque

Reputation: 2750

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

Upvotes: 3

keithwarren7
keithwarren7

Reputation: 14280

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.

Upvotes: 9

Related Questions