Hello, is there any way to instruct LINQ to run a Fulltext Index Query (such as CONTAINS)? Any text search queries LINQ is currently building for me are only ending up with the LIKE operator. Do I need to create a stored procedure for this?
An example would be fantastic! Thank you!
Since LINQ is a general-purpose query abstraction layer, you can't directly perform server specific implementations through LINQ. You may be able to do this with EF using a view in the store model.
That being said, you may be able to do this with LINQ to SQL by using context.ExecuteQuery(string). Remember if you use ExecuteQuery, make sure to pass in variables as paramteters rather than simple string concatenation, as your query is passed directly to the server and thus could result in SQL Injection if not handled properly. Here's an example avoiding SQL Injection:
Dim searchName = "foo"
Dim SQL As String = "Select ID, LastName, FirstName, WebSite, TimeStamp " & _
"From dbo.Author " & _
"Where Contains (LastName, {0})"
Dim authors As IEnumerable(Of Author) = context.ExecuteQuery(Of Author)(SQL, searchName)
I'd also suggest looking at Lucene and the Linq to Lucene implementation. In my experience it provides MUCH better performance than searching against SQL full text indexes (esp. in SQL 2008).
Hello
You can do this by using a table valued function, I go through the details in this blog post