Home / Community & Support / Knowledge Base / Telerik OpenAccess ORM / General / Using full text search features with LINQ queries

Using full text search features with LINQ queries

Article Info

Rating: Not rated

Article information

Article relates to

 Telerik OpenAccess ORM

Created by

 Ralph Waldenmaier

Last modified

 April 06, 2012

Last modified by

 Pencho Popadiyn

DESCRIPTION

The various backends that are supported by Telerik OpenAccess ORM have several database specific features that are accessible for the user through specific SQL statements. These statements could usually not be executed with LINQ expressions. In this article we will look into some commonly used backends, to execute a sql statement that uses the full text search capabilities of the respective backend. 

SOLUTION

Each backend has its own syntax and mechanisms to provide the full text search features and we need to be able to execute these statements. Since there is no solution provided with LINQ out of the box, Telerik OpenAccess ORM offers an extension method called SQL, which allows us to insert our own specific sql-code to the resulting sql statement produced by the runtime.

The SQL extension method is in the Telerik.OpenAccess namespace.

MS SQL Server
For SQL Server, the CONTAINS function can be used to do a full text search query.
Assume that we want to execute the following SQL statement:

select
*
from
Orders
where
contains(ShipName, 'Vins*')

Since, as mentioned earlier, there is no contains() method with the same meaning in LINQ as in SqlServer, we need to use the SQL extension method as shown with the following snippet.

using (EntitiesModel ctx = new EntitiesModel())
{
    string fulltextCondition = "\"Vins*\"";
    var q = from o in ctx.Orders where "CONTAINS({0},{1})".SQL<bool>(o.ShipName, fulltextCondition) select o;
    var result = q.ToList();
}

 The string, which is prepended before the SQL extension method, will be added to the generated sql statement. The placeholders in this example are filled up with the values that were passed to the SQL extension method. Whenever possible, instead of writing the provided values to the sql statement, Telerik OpenAccess ORM uses binding variables.

Oracle
With Oracle as your backend, it is basically the same as with MS SQL Server, just the statement is a bit different.

select *
from
"ordr"
WHERE CONTAINS("ship_name", 'Vins', 1) > 0

This could be executed with the following LINQ query.

using (EntitiesModel ctx = new EntitiesModel())
{
    string fulltextCondition = "'Vins'";
    var q = from o in ctx.Ordrs where "CONTAINS({0},{1}, 1) > 0".SQL<bool>(o.Ship_name, fulltextCondition) select o;
    var result = q.ToList();
}

See the Oracle Fulltext link below, for more details on how to use the full text features of Oracle.

MySql
For MySql the statement to execute a full text query looks like the following statement.

SELECT * FROM articles
WHERE MATCH (title, body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);

For this statement we need to create a LINQ query that looks like this.

using (EntitiesModel ctx = new EntitiesModel())
{
    string fulltextCondition = "'database'";
    var q = from o in ctx.Articles where "MATCH({0}, {1}) AGAINST({2} IN NATURAL LANGUAGE MODE)".SQL<bool>(o.Title, o.Body, fulltextCondition) select o;
    var result = q.ToList();
}

In this case we are using the example provided by the MySql documentation. See the links below for more details. We pass 3 parameters to the SQL extension method based on the full text definition of the table.

PostgreSql
For PostgreSql we need to execute a query like the following:

SELECT *
FROM Orders
WHERE to_tsvector(ShipName) @@ to_tsquery('Vins');

Again here we have a LINQ query:

using (EntitiesModel ctx = new EntitiesModel())
{
    string fulltextCondition = "'Vins'";
    var q = from o in ctx.Ordrs where "to_tsvector({0}) @@ to_tsquery({1})".SQL<bool>(o.Ship_name, fulltextCondition) select o;
    var result = q.ToList();
}

CONCLUSION
The SQL extension method is not limited to make use of only the full text features of the various database backend, but will give you also the possibility to execute other unmapped functions.

REFERENCES

Comments

There are no comments yet.
If you'd like to comment on this KB article, please, send us a Support Ticket.
Thank you!

Please Sign In to rate this article.