.NET with BI development tips, tricks and experiences.

The day before yesterday I had my problem with getting my stored proc recognised in the Entity Framework (EF) designer in Visual Studio 2010 (VS2010). Yesterday my next step was to get one of my stored procedures (sprocs) that use Full Text Search (FTS) bound to a complex type. So I could get all the results and aggregations in one round trip to the database. I started with the simplest sproc I could:

CREATE PROCEDURE [dbo].[P_ParentFullTextSearch]
@queryString NVARCHAR(250)
AS
BEGIN
SELECT P.[ID], P.[Name],
(SELECT COUNT(1)
FROM  dbo.[Children] C
WHERE P.[ID] = [C.ParentID]) AS ChildrenCount
FROM   dbo.[Parent] P
WHERE  FREETEXT(P.[Name], @queryString)
AND    P.IsDeleted = 0
END


I granted EXECUTE and CONTROL permissions to the EF connection string SQL Server login on the proc and could get it picked up in the Add Function Import but could not retrieve any column information. Frustrating…

I tried a number of things on the MSDN forums like SET NOCOUNT OFF and adding the VIEW DEFINITION permission. None worked. After running a trace (the first thing I should have done) in the SQL Server Profiler I found clicking Get Column Information was executing:


SET FMTONLY OFF;
SET FMTONLY ON;
EXEC [db_name].[dbo].P_ParentFullTextSearch @queryString=null;
SET FMTONLY OFF;


The problem comes from the fact you CANNOT use nulls in FTS predicates – DOH should have noticed that. This causes the metadata query to fall over. Unfortunately the error you get when running the SQL does not bubble up into the Add Function Import window.

A simple NULL check resolved the issue:

CREATE PROCEDURE [dbo].[P_ParentFullTextSearch]
@queryString NVARCHAR(250)
AS
BEGIN
IF @queryString IS NULL
BEGIN
SELECT    (CAST NULL AS INT) AS [ID],
(CAST NULL AS NVARCHAR(50)) AS [Name],
(CAST NULL AS INT) AS [ChildrenCount]
RETURN
END
SELECT P.[ID], P.[Name],
(SELECT COUNT(1)
FROM dbo.[Children] C
WHERE P.[ID] = [C.ParentID]) AS ChildrenCount
FROM dbo.[Parent] P
WHERE FREETEXT(P.[Name], @queryString)
AND P.IsDeleted = 0
END

Behold! Metadata and a sproc bound to a complex type.

Unfortunately I could not see the stored procedure in the list of items to be added when refreshing the model from the database. I have been implementing a stored procedure/inline-table function to facilitate a Full Text Search (FTS) in SQL Server 2008 using ADO .NET Entity Framework (EF).

The cause was that the EF connection did not have Control/Execute permissions on the function/procedure. Grant these permissions to the user utilised in your EF connection string and it will pop-up in the list of stored procedures to be added when you go back to update the model.

Once that is done you can then map the proedure/function results to your entities or a complex type. This is accomplished by selecting Add function import when right-clicking anywhere in the EDMX Designer View or the stored procedure in the Model View.