Using Oracle Full-Text Search in Entity Framework

Introduction

Oracle database supports an advanced functionality of full-text search (FTS) called Oracle Text, which is described comprehensively in the documentation:

We decided to meet the needs of our users willing to take advantage of the full-text search in Entity Framework and implemented the basic Oracle Text functionality in our Devart dotConnect for Oracle ADO.NET Entity Framework provider.
For working with Oracle Text specific functions in LINQ to Entities queries, the new OracleTextFunctions class is used, which is located in the Devart.Data.Oracle.Entity.dll assembly. It enables working with such Oracle Text functions as:

  • CONTAINS
  • CATSEARCH
  • MATCHES
  • SCORE
  • MATCH_SCORE

To call Oracle Text specific stored procedures of the CTX_DDL package, the OracleCtxDdlPackage class is used, which is located in the Devart.Data.Oracle.Entity.dll assembly.
To customize Code-First Migrations with the purpose of creating and deleting Oracle Text specific indexes, the CreateIndexConfiguration and DropIndexConfiguration classes are used, which are located in the Devart.Data.Oracle.Entity.Migrations.dll assembly.
This article deals with the following:


Tutorial Overview

In this tutorial you will learn how to create an application which will use the following technologies:

Follow the link at the end of this article to download the complete application sample.
In spite of the fact that in this sample we show work with Code-First, Oracle Text queries can be similarly written for reqular XML mapping using EDMX/EDML models developed within Database-First or Model-First approaches. So we will also briefly consider the Database-First approach in this tutorial.
We decided to take the standard Oracle tutorial as a basis, it is available at Getting Started with Oracle Text.

Prerequisites

Presetting Database and Model under the Database-First Approach

1. Create a console C# project in Visual Studio 2010 SP1 and call it OracleFullTextSearch.
2. In the project, add references to the following assemblies:

  • Devart.Data;
  • Devart.Data.Oracle;
  • Devart.Data.Oracle.Entity;
  • Devart.Data.Oracle.Entity.Migrations.

3. Also, an Oracle user is required, having the CTXAPP role and the EXECUTE grant for CTXSYS packages.
One of the ways to create such user is to execute the following Oracle statements via any tool designed for working with Oracle database:

4. Create an empty Entity Framework model for this user via Entity Developer. In the Solution Explorer window, call the context menu of the project, then select Add -> New Item -> Devart Entity Model. Follow the wizard instructions and specify a valid connection string. The file of the model and the generated code will be added to the project.

Presetting Database and Model under the Code-First Approach

1. Create a console C# project in Visual Studio 2010 SP1 and call it OracleFullTextSearch.
2. In the project, add references to the following assemblies:

  • System.Data.Entity;
  • Devart.Data;
  • Devart.Data.Oracle;
  • Devart.Data.Oracle.Entity;
  • Devart.Data.Oracle.Entity.Migrations.

3. Add the latest public version of EF Code-First to the project, for this run Package Manager Console via the Visual Studio menu: Tools -> Library Package Manager -> Package Manager Console, and in the displayed window run the following command:

As a result, the EntityFramework.dll assembly is added to the project, App.config is supplemented with records and the packages.config file is created.
4. Add a new MyContext.cs file to the project and place an empty model in it:

Here, in order not to overload the sample with quoted identifiers, we have also set the DisableQuoting configuration option value of the EF provider to True, to get DDL and DML unquoted identifiers during SQL generation. It is not necessary to do this in user applications.
Besides, we have set the UseNonUnicodeStrings option value to True, to avoid specifying explicitly string data type [Column(TypeName=”varchar2″)] for entity string properties, so that specifying just the length limit [MaxLength(200)] would be enough.
We have also added the CtxDdl property of the OracleCtxDdlPackage type for working with stored procedures from the CTX_DDL package.
5. Run the following command in Package Manager Console:

As a result, the Configuration class is added to the project.
6. Complement the existing Configuration class constructor

by specifying the SQL generator:

7. Also, an Oracle user is required, having the CTXAPP role and the EXECUTE grant for CTXSYS packages.
To create such user, you can e.g. execute the following Oracle statements via any tool designed for working with Oracle database:

8. Setting the connection string for our class of the MyContext model will be performed in the config file of the application. Add the connection string to the App.config file:

Using CONTEXT Index

Presetting Database and Model under the Database-First Approach

1. Create the DOCS table and the IDX_DOCS CONTEXT index.

2. Use the Update From Database Wizard to add the DOCS table to the EF model.

Presetting Database and Model under the Code-First Approach

1. Add the Doc.cs file to the project:

Here we specify that the Id property is a primary key, but its value will not be server-generated.
For the Text property we set the VARCHAR(200) data type.
2. Add the following line to the MyContext class:

3. To generate migration, run the following command in Package Manager Console:

As a result, a migration class with the following content is added to the project:

4. Complement the migration with commands for creating (and deleting) the CTXSYS.CONTEXT index:

5. Update the database using the migration, by running the following command in Package Manager Console:

As a result, commands for model objects creation will be executed

Also, commands for creating system MigrationHistory table and filling it with migration data will be executed.

Full-Text Search Sample Using CONTAINS and SCORE Functions

In this tutorial, we have turned on OracleMonitor to view executed DDL and DML statements in the Devart dbMonitor application. However, keep in mind that the use of OracleMonitor is feasible for the purpose of testing and debugging but should be limited in production environments, since monitoring can decrease the performance of your application.
To use monitoring, add this code to your application

and run dbMonitor.
Let’s write the code filling the table with data, updating the index and forming queries with the help of the CONTAINS and SCORE functions.

Using CTXCAT Index

Presetting Database and Model under the Database-First Approach

1. Create the AUCTION table, the AUCTION_ISET index set and the AUCTION_TITLEX CTXCAT index.

2. Use the Update From Database Wizard to add the AUCTION table to the EF model.

Presetting Database and Model under the Code-First Approach

1. Add the Auction.cs file to the project:

2. Add the following line to the MyContext class:

3. To generate migration, run the following command in Package Manager Console:

As a result, a migration class with the following content is added to the project:

4. Complement the migration with commands for creating (and deleting) the CTXSYS.CTXCAT index and the corresponding index set:

5. Update the database using the migration, by running the following command in Package Manager Console:

As a result, commands for model objects creation

and the command for filling the system HistoryTable table with the implemented migration data will be executed.

Full-text search sample using CATSEARCH function

Let’s write the code filling the AUCTION table with data and forming Oracle Text queries with the help of the CATSEARCH function. There is no need to update the index explicitly, as the CTXCAT index is updated automatically.

Using CTXRULE Index

Presetting Database and Model under the Database-First Approach

1. Create the QUERIES table and the CTXRULE index for its column

2. Use the Update From Database Wizard to add the QUERIES table to the EF model.

Presetting Database and Model under the Code-First Approach

1. Add the Query.cs file to the project:

2. Add the following line to the MyContext class:

3. Generate migration by running the following command in Package Manager Console:

As a result, a migration class with the following content is added to the project:

4. Complement the migration with commands for creating (and deleting) the CTXSYS.CTXRULE index:

5. Update the database using the migration, by running the following command in Package Manager Console:

As a result, commands for model objects creation

and the command for filling the system HistoryTable table with the implemented migration data will be executed.

Full-Text Search Sample Using the MATCHES Function

Let’s write the code filling the table with data, updating the index and forming queries with the help of the MATCHES function.

Conclusion

Thus, we have shown how to use Oracle Text functionality in Entity Framework applications, including the development process, based on Code-First Migrations. We are looking forward to receiving feedback from you via comments, the Entity Framework forum and our contact form, if it’s necessary to rework or improve support of Oracle Text in our provider.

You can download the archive with the complete version of the sample from this tutorial here.

Comments are closed.