BrunoPugliese BrunoPugliese - 20 days ago 6
C# Question

Table Valued Function and Entity Framework

I'm trying to execute an TVF with Entity Framework and for some reason it just doesn't work.
Maybe anyone out there can help me see the problem.

Here are the code samples:

That's the function:

CREATE FUNCTION [dbo].[udf_profileSearch]
(@keywords NVARCHAR(3000))
RETURNS @results TABLE
(
[Id] [int] NULL,
[SubCategoryId] [int] NULL,
[UserId] [int] NULL,
[SmallDescription] [nvarchar](250) NULL,
[DetailedDescription] [nvarchar](500) NULL,
[Graduation] [nvarchar](140) NULL,
[Experience] [nvarchar](500) NULL,
[IsChat] [bit] NULL,
[IsEmail] [bit] NULL,
[MinuteCost] [decimal](18, 2) NOT NULL,
[TestimonyRate] [int] NULL,
[TestimonyQuantity] [int] NULL,
[StatusId] [int] NULL
)

AS
BEGIN
IF(@keywords != '')
BEGIN
insert @results
SELECT p.Id, p.SubCategoryId, p.UserId, p.SmallDescription, p.DetailedDescription, p.Graduation,
p.Experience, p.IsChat, p.IsEmail, p.MinuteCost, p.TestimonyRate, p.TestimonyQuantity,
p.StatusId FROM
Profile p inner join ProfileSearchKeyword psk
ON p.Id = psk.ProfileId
WHERE CONTAINS(psk.*,@keywords)
END
ELSE
BEGIN
insert @results
SELECT p.* FROM
Profile p inner join ProfileSearchKeyword psk
ON p.Id = psk.ProfileId
END
RETURN
END


I have this in my DbContext file (named EAjudaContext)

[EdmFunction("eAjudaConnection", "udf_profileSearch")]
public virtual IQueryable<Profile> udf_profileSearch(string keywords)
{
var keywordsParameter = keywords != null ?
new ObjectParameter("keywords", keywords) :
new ObjectParameter("keywords", typeof(string));

return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<Profile>("eAjudaConnection.udf_profileSearch(@keywords)", keywordsParameter);
}


That's how I'm calling the func via LINQ

var result = from ps in eAjudaCtx.udf_profileSearch("query") select ps


And I get this error:

'eAjudaConnection.udf_profileSearch' cannot be resolved into a valid type or function.


Any ideas on what I'm missing?
I've tried pretty much every tip I found on google, but none solved my problem.

If you need to see any piece of code not included here, just ask and I'll add it.

Answer

Here is a very good article on the newer features of Entity Framework that provide direct support for Table Valued UDFs. MSDN blog on Table-Valued Function Support in Entity Framework.

For greater depth, this article provides significant detail. EDM and store functions exposed in LINQ.

One of the great advantages of the recent support for Table-Valued UDFs involves support for Full-Text Search capabilities. Read more about that here: Full text search features involving database objects.