CODEWITHSUNDEEP
sqlsql-serverfull-text-search
Sergey Litvinov
Sergey Litvinov

Reputation: 7458

Wrap SQL CONTAINS as an expression?

I have a question. I working on one site on Asp.Net, which uses some ORM. I need to use a couple of FullTextSearch functions, such as Contains. But when I try to generate it with that ORM, it generates such SQL code

    SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name] 
    FROM [dbo].[SomeTable] AS [Extent1]
    WHERE (Contains([Extent1].[Name], N'qq')) = 1

SQL can't parse it, because Contains doesn't return bit value. And unfortunately I can't modify SQL query generation process, but I can modify statements in it.

My question is - is it possible to wrap call of CONTAINS function to something else? I tried to create another function, that will SELECT with contains, but it requires specific table\column objects, and I don't want to do one function for each table..

EDIT

I can modify result type for that function in ORM. In previous sample result type is Bit. I can change it to int,nvarchar,etc. But as I understood there is no Boolean type in SQL, and I can't specify it.

Upvotes: 4

Views: 391

Answers (1)

anon
anon

Reputation:

Can't you put this in a stored procedure, and tell your ORM to call the stored procedure? Then you don't have to worry about the fact that your ORM only understands a subset of valid T-SQL.

I don't know that I believe the argument that requiring new stored procedures is a blocker. If you have to write a new CONTAINS expression in your ORM code, how much different is it to wrap that expression in a CREATE PROCEDURE statement in a different window? If you want to do this purely in ORM, then you're going to have to put pressure on the vendor to pick up the pace and start getting more complete coverage of the language they should fully support.

Upvotes: 2

Related Questions