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

Question

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

keithwarren7 · Accepted Answer · 2011-11-08 13:50:28Z

9

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.

answered Nov 8, 2011 at 13:50

keithwarren7

14.3k8 gold badges56 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Reza.Hoque Over a year ago

Thanks. In that case will I be able to write query, like var result=from p in dc.mytable join q in dc.myUSPwithFunc('id') ...??

keithwarren7 Over a year ago

Yes, when you add your stored procedure you will need to add a function import and the return type will be a complex type, the EF designer has the ability to generate a new complex type for you. The 'complex type' is really a new class that is based on the fields returned by your procedure.

Reza.Hoque Over a year ago

Hi. I am just not getting through. :( I have edited my post and added the code i am trying to write as per your answer. Can you plz have a look?

Reza.Hoque · Accepted Answer · 2011-11-09 10:36:09Z

3

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

answered Nov 9, 2011 at 10:36

Reza.Hoque

2,75010 gold badges52 silver badges81 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related