Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
Jerph opened this issue Apr 3, 2013 · 0 comments

Comments

@Jerph
Copy link

Jerph commented Apr 3, 2013

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant