Importing SQL Server's CONTAINS() as a model defined function

Question

I am trying to import SQL Server's CONTAINS() function in my Entity Framework model so that I can use it in my LINQ queries.

I have added this to my EDM:

<Function Name="FullTextSearch" ReturnType="Edm.Boolean">     <Parameter Name="Filter" Type="Edm.String" />     <DefiningExpression>         CONTAINS(*, Filter)     </DefiningExpression> </Function> 

Add created my method stub:

[EdmFunction("MyModelNamespace", "FullTextSearch")] public static bool FullTextSearch(string filter) {     throw new NotSupportedException("This function is only for L2E query."); } 

I try to call the function like this:

from product in Products where MyModel.FullTextSearch("FORMSOF(INFLECTIONAL, robe)") select product 

The following exception is raised:

The query syntax is not valid. Near term '*' 

I realize that the function I defined is not directly linked to the entity set being queried so that could also be a problem.

Is there any way to pull this off?

Answer 1

The function you have defined above uses Entity SQL, not Transact SQL, so I think the first step is to figure out whether CONTAINS(*,'text') can be expressed in Entity SQL.

Entity SQL doesn't support the * operator as described here: http://msdn.microsoft.com/en-us/library/bb738573.aspx and if I try

entities.CreateQuery<TABLE_NAME>("select value t from TABLE_NAME as t where CONTAINS(*, 'text')"); 

I get the same error you got above. If I try to explicitly pass the column it works:

entities.CreateQuery<TABLE_NAME>("select value t from TABLE_NAME as t where CONTAINS(t.COLUMN_NAME, 'text')"); 

But when I look at the SQL it translated it to a LIKE expression.

ADO.NET:Execute Reader "SELECT  [GroupBy1].[A1] AS [C1] FROM ( SELECT      COUNT(1) AS [A1]     FROM [dbo].[TABLE_NAME] AS [Extent1]     WHERE (CASE WHEN ([Extent1].[COLUMN_NAME] LIKE '%text%') THEN cast(1 as bit) WHEN ( NOT ([Extent1].[COLUMN_NAME] LIKE '%text%')) THEN cast(0 as bit) END) = 1 )  AS [GroupBy1]" 

If you cannot express the query using Entity SQL you'll have to use a Stored Procedure or other mechanism to use Transact SQL directly.