No columns when binding to complex type in Entity Framework
- September 27th, 2010
- Write comment
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.