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?

Possible duplicate? http://stackoverflow.com/questions/224475/is-it-possible-to-use-full-text-search-fts-with-linq — Pondlife, Jan 26 '12 at 09:47
The suggested solution in this question is to import a table-valued function, which works for LINQ to SQL but not (currenty) for Entity Framework — Xavier Poinas, Jan 26 '12 at 10:05

score 3 · Accepted Answer · answered Jun 29 '12 at 02:56

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.

score 1 · Answer 2 · answered May 11 '12 at 16:36

1

This is way beyond me but could you try

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

My reasoning is that in SQL Server it is expecting two parameters.

answered May 11 '12 at 16:36

Grixxly

464
1
4
17

score 1 · Answer 3 · answered May 23 '12 at 16:45

I inserted a little function into my code, in a class which inherits from the Context class, which points to my SQL function supporting Full Text searching, my solution is a little more closed ended to yours (not allowing the specification of the type of text search), it returns an IEnumerable, essentially a list of primary keys matching the searching criteria, something like this;

public class myContext : DataContext
{

     protected class series_identity
     {
            public int seriesID;

            series_identity() { }
     };

            [Function(Name = "dbo.fnSeriesFreeTextSearchInflectional", IsComposable = true)]
            protected IQueryable<series_identity> SynopsisSearch([Parameter(DbType = "NVarChar")] string value)
            {
                return this.CreateMethodCallQuery<series_identity>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), value);
            }

            public IEnumerable<int> Search(string value)
            {
                var a = from t1 in SynopsisSearch(value)
                        select t1.seriesID;

                return a;
            }
};

usage is something like;

myContext context = new myContext();

IEnumerable<int> series_identities = (from t1 in context.Search("some term")
                                                  select t1).Distinct();

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

3 Answers3