FREETEXT search on a ntext column not working

[sourabh-mehta]

File a bug

The SQL statement generated by the EF Core (displayed in the logs) for EF.Functions.FreeText function is correct and executes successfully. However, the actual statement that is sent to SQL server is different and throws the error.

freeText parameter type for ntext column in SQL statement is set to a size of 4000 but the SQL statement that is executed on SQL server parameter type is ntext and throws exception.

Include your code

Database Table: ComplaintRecommendation

var searchText = "woman";
Expression<Func<ComplaintRecommendation, bool>> predicate = x => (x.Title != null && EF.Functions.FreeText(x.Title, searchText)) ||
                (x.Recommendation != null && EF.Functions.FreeText(x.Recommendation, searchText));

Context.Set<ComplaintRecommendation>().AsNoTracking().Where(predicate).ToList();

Include stack traces

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (19ms) [Parameters=[@__searchText_1='woman' (Size = 256), @__searchText_1_1='woman' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT [c].[Id], [c].[Recommendation], [c].[Title]
      FROM [ComplaintRecommendation] AS [c]
      WHERE (([c].[Title] IS NOT NULL) AND FREETEXT([c].[Title], @__searchText_1)) OR (([c].[Recommendation] IS NOT NULL) AND FREETEXT([c].[Recommendation], @__searchText_1_1))
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'ichs.infrastructure.Data.ApplicationDbContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): The argument type "ntext" is invalid for argument 2 of "FREETEXT".
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
      ClientConnectionId:2e66505f-2b47-40bb-a931-ba260ec16ae9
      Error Number:4110,State:6,Class:16

The parameter @__searchText_1_1 has size set to 4000 and above statement works if executed on the SQL Server

SQL Profiler Trace

Include provider and version information

EF Core version: 7.0.10
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 11
SQL Server: Microsoft SQL Server 2022 16.0.1050.5 (X64) Developer Edition (64-bit)
IDE: Visual Studio 2022 17.6.5

[ajcvickers]

Note for triage: If the column is ntext but not specified as such in the model, then we create an nvarchar parameter which SQL Server accepts. If we try to match the parameter type to the column type, then SQL Server rejects it.

using (var context = new SomeDbContext())
{
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();

    context.Database.ExecuteSqlRaw(@"CREATE FULLTEXT CATALOG ft AS DEFAULT");
    context.Database.ExecuteSqlRaw(@"CREATE FULLTEXT INDEX ON dbo.Name(Title, Recommendation) KEY INDEX PK_Name");
}

using (var context = new SomeDbContext())
{
    var searchText = "woman";
    var results = context.Set<Name>().AsNoTracking().Where(x => (x.Title != null && EF.Functions.FreeText(x.Title, searchText)) ||
                                                  (x.Recommendation != null &&
                                                   EF.Functions.FreeText(x.Recommendation, searchText))).ToList();
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=AVICKERS420L;Database=AllTogetherNow;Integrated Security=True;Connect Timeout=60;ConnectRetryCount=0;TrustServerCertificate=True")
"C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==",
            //         "AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Name>(b =>
        {
            b.Property(e => e.Recommendation).HasColumnType("ntext");
            b.Property(e => e.Title).HasColumnType("nvarchar(256)");
        });
    }
}

public class Name
{
    public Guid Id { get; set; }
    public string? Recommendation { get; set; }
    public string? Title { get; set; }
}

[ajcvickers]

Note from triage: it is possible that the parameter inference is being used for the second parameter as well as the first, but the second parameter should always be nvarchar().

[ajcvickers]

Note from triage: this doesn't meet the servicing bar.