MiniProfiler icon indicating copy to clipboard operation
MiniProfiler copied to clipboard

EF Model First - table valued function parameters called with nvarchar(max), breaks full text search

Open Jerph opened this issue 11 years ago • 0 comments

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

Jerph avatar Apr 03 '13 18:04 Jerph