Implement SQL Server full-text search TVFs #37578
Open
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
6 tasks
There was a problem hiding this comment.
Pull request overview
This pull request implements support for SQL Server full-text search table-valued functions (FREETEXTTABLE and CONTAINSTABLE), allowing users to query full-text indexes and access ranking information for search results. The implementation follows the pattern established by the VectorSearch feature (PR #37536) and includes comprehensive test coverage.
Changes:
- Adds
FreeTextTableandContainsTableextension methods that return queryable results with Key and Rank columns - Implements query translation in
SqlServerQueryableMethodTranslatingExpressionVisitorto convert these method calls into appropriate SQL - Refactors
TableValuedFunctionExpressionto acceptIReadOnlyList<Expression>instead of justSqlExpression, enabling passing of table expressions and array expressions as arguments - Moves existing full-text predicate tests from NorthwindDbFunctionsQuerySqlServerTest to a new dedicated test file
Reviewed changes
Copilot reviewed 24 out of 25 changed files in this pull request and generated 4 comments.
Show a summary per file
| File | Description |
|---|---|
| test/EFCore.SqlServer.FunctionalTests/Query/Translations/VectorTranslationsSqlServerTest.cs | Adds VectorSearch tests; contains bug with experimental attribute using "FOO" |
| test/EFCore.SqlServer.FunctionalTests/Query/Translations/FullTextSearchTranslationsSqlServerTest.cs | New comprehensive test file for full-text search TVFs and predicates |
| test/EFCore.SqlServer.FunctionalTests/Query/NorthwindDbFunctionsQuerySqlServerTest.cs | Removes full-text search tests (moved to dedicated file) |
| test/EFCore.SqlServer.FunctionalTests/Query/NorthwindDbFunctionsQuerySqlServer160Test.cs | Removes full-text search tests (moved to dedicated file) |
| test/EFCore.SqlServer.FunctionalTests/Query/GearsOfWarQuerySqlServerTest.cs | Removes binary column full-text tests (moved to dedicated file) |
| src/Shared/EFDiagnostics.cs | Adds EF9105 diagnostic ID for experimental VectorSearch feature |
| src/EFCore/Query/Internal/NavigationExpandingExpressionVisitor.cs | Changes to delegate unknown methods to provider-specific visitors instead of throwing |
| src/EFCore.Sqlite.Core/Query/Internal/SqliteQueryableMethodTranslatingExpressionVisitor.cs | Refactors to use JsonEachExpression.Json property |
| src/EFCore.Sqlite.Core/Query/Internal/SqliteQuerySqlGenerator.cs | Updates to use JsonEachExpression.Json property |
| src/EFCore.Sqlite.Core/Query/Internal/SqlExpressions/JsonEachExpression.cs | Refactors from JsonExpression property to Json property for consistency |
| src/EFCore.SqlServer/Query/Internal/SqlServerTypeMappingPostprocessor.cs | Updates to use SqlServerOpenJsonExpression.Json property |
| src/EFCore.SqlServer/Query/Internal/SqlServerSqlTreePruner.cs | Updates to use SqlServerOpenJsonExpression.Json property |
| src/EFCore.SqlServer/Query/Internal/SqlServerSqlNullabilityProcessor.cs | Adds handling for full-text TVFs to remove null topN arguments |
| src/EFCore.SqlServer/Query/Internal/SqlServerQueryableMethodTranslatingExpressionVisitor.cs | Implements translation for VectorSearch, FreeTextTable, and ContainsTable; contains array type bugs |
| src/EFCore.SqlServer/Query/Internal/SqlServerQuerySqlGenerator.cs | Adds SQL generation for VECTOR_SEARCH, FREETEXTTABLE, and CONTAINSTABLE |
| src/EFCore.SqlServer/Query/Internal/SqlServerJsonPostprocessor.cs | Updates to use SqlServerOpenJsonExpression.Json property |
| src/EFCore.SqlServer/Query/Internal/SqlExpressions/SqlServerOpenJsonExpression.cs | Refactors from JsonExpression property to Json property for consistency |
| src/EFCore.SqlServer/Properties/SqlServerStrings.resx | Adds VectorSearchRequiresColumn error message |
| src/EFCore.SqlServer/Properties/SqlServerStrings.Designer.cs | Generates accessor for VectorSearchRequiresColumn |
| src/EFCore.SqlServer/Extensions/SqlServerQueryableExtensions.cs | New file with VectorSearch, FreeTextTable, ContainsTable methods and result types |
| src/EFCore.SqlServer/Extensions/SqlServerDbFunctionsExtensions.cs | Minor documentation formatting fix |
| src/EFCore.SqlServer/EFCore.SqlServer.csproj | Suppresses EF9105 experimental warning |
| src/EFCore.Relational/Query/SqlExpressions/TableValuedFunctionExpression.cs | Changes Arguments type from SqlExpression to Expression to support non-SQL expressions |
| src/EFCore.Relational/Query/RelationalSqlTranslatingExpressionVisitor.cs | Adds member access reduction for NewExpression |
| src/EFCore.Relational/Query/RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.cs | Removes assertion that prevented reading non-property non-nullable columns |
Files not reviewed (1)
- src/EFCore.SqlServer/Properties/SqlServerStrings.Designer.cs: Language not supported
test/EFCore.SqlServer.FunctionalTests/Query/Translations/VectorTranslationsSqlServerTest.cs
Outdated
Show resolved
Hide resolved
src/EFCore.SqlServer/Query/Internal/SqlServerQueryableMethodTranslatingExpressionVisitor.cs
Show resolved
Hide resolved
src/EFCore.SqlServer/Query/Internal/SqlServerQueryableMethodTranslatingExpressionVisitor.cs
Show resolved
Hide resolved
src/EFCore.SqlServer/Extensions/SqlServerQueryableExtensions.cs
Outdated
Show resolved
Hide resolved
FREETEXTTABLE, CONTAINSTABLE Closes dotnet#11487
roji
force-pushed
the
SqlServerFullTextTVFs
branch
from
f6f729d to
6024214
Compare
Member
Author
|
/azp run |
|
Azure Pipelines successfully started running 1 pipeline(s). |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
1 participant
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Note: this PR is based on top of #37536, skip first commit
FREETEXTTABLE, CONTAINSTABLE
Closes #11487
Thanks to @Abde1rahman1 for a first implementation attempt in #37489, which inspired this.