MiniProfiler
MiniProfiler copied to clipboard
EF Model First - table valued function parameters called with nvarchar(max), breaks full text search
I haven't tried Code-First or other UDF variations on this, but here's my particular problem:
I'm using EF Model First (.edmx) with SQL Server. I have a TVF with an nvarchar
parameter to do full text searching. It works fine without the EF Profiler. When I use the profiler, the generated SQL calls the parameter as nvarchar(max)
(instead of nvarchar(4000)
), which is incompatible with freetexttable() and throws an error. Here are the SQL outputs from a test function captured with SQL Server Profiler:
CREATE FUNCTION [dbo].[KeywordSearch]
(
@keywords nvarchar(4000) = null
)
RETURNS TABLE
AS
RETURN
SELECT 0 as col
GO
--SQL as called without MiniProfilerEF
exec sp_executesql N'SELECT
1 AS [C1],
[Extent1].[col] AS [col]
FROM [dbo].[KeywordSearch](@keywords) AS [Extent1]',N'@keywords nvarchar(4000)',@keywords=N'test'
--SQL as called *with* MiniProfilerEF
exec sp_executesql N'SELECT
1 AS [C1],
[Extent1].[col] AS [col]
FROM [dbo].[KeywordSearch](@keywords) AS [Extent1]',N'@keywords nvarchar(max) ',@keywords=N'test'
I have this reproducible as a VS console app project here: https://dl.dropbox.com/u/16940699/MiniProfilerTvfParameterTest.zip
I believe this is the same issue as http://stackoverflow.com/questions/15100640/changing-my-query-and-causing-sql-exception
Interestingly, LinqPad shows the same behavior, and there's a related question here: http://stackoverflow.com/questions/13031103/when-using-entity-framework-with-linqpad-or-efproviderwrappertoolkit-table-valu