Using MySQL Full-Text Search in Entity Framework

Introduction

MySQL database supports an advanced functionality of full-text search (FTS) and full-text indexing 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 full-text search functionality in our Devart dotConnect for MySQL ADO.NET Entity Framework provider.
This article deals with the following:


Full-Text Search in LINQ to Entities

The MySqlTextFunctions class located in the Devart.Data.MySql.Entity.dll assembly is used to form full-text search specific queries in LINQ to Entities. It has 6 methods, each having 5 overloadings for forming queries on one, two, three, four and five columns.

Method returning
Double
Method returning
Boolean
Description Generated SQL
MatchAgainst MatchAgainstAsBool Natural language
full-text search
MATCH (col1,col2,…)
AGAINST (expr)
MatchAgainstWithQuery
Expansion
MatchAgainstWithQuery
ExpansionAsBool
Full-text search
with query
expansion
MATCH (col1,col2,…)
AGAINST (expr WITH QUERY EXPANSION)
MatchAgainstInBoolean
Mode
MatchAgainstInBoolean
ModeAsBool
Boolean full-text
search
MATCH (col1,col2,…)
AGAINST (expr IN BOOLEAN MODE)

Methods that return System.Boolean are added for a more convenient writing of LINQ to Entities queries in the form that is more like the common practice of writing SQL queries, when a particular value returned by the MATCH (…) AGAINST (…) expression is of no importance. I.e. when it’s necessary to write an analogue of the query:

you can use:

If you have to write a query like:

you should use a regular Double-based form of the method:

When returning the results of the MATCH (…) AGAINST (…) execution is required, then the SQL query of this kind:

can be written like this:

Full-Text Indexes in Code-First Migrations

To customize Code-First Migrations for the purpose of creating full-text indexes, the MySqlCreateTableConfiguration and MySqlCreateIndexConfiguration classes are used, which are located in the Devart.Data.MySql.Entity.Migrations.dll assembly.
MySqlCreateIndexConfiguration is designed to customize the CreateIndex operation. This class allows specifying that the index being created is full-text (the IsFulltext property) and setting a custom parser for the full-text index (the Parser property).
MySqlCreateTableConfiguration is designed to customize the CreateTable operation. This class allows specifying the MySQL storage engine for the table being created (the StorageEngine property) in order to avoid dependency on the default storage engine set in the particular database. This is an important aspect of full-text index creation, as they are supported for MyISAM (all MySQL 5.x versions) and InnoDB (only starting from MySQL 5.6).
Examples of customizations are given in the tutorial below.

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, full-text search 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 consider the Database-First approach in this tutorial.

Prerequisites

Presetting Database and Model under the Database-First Approach

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

  • Devart.Data;
  • Devart.Data.MySql;
  • Devart.Data.MySql.Entity;
  • Devart.Data.MySql.Entity.Migrations.

3. Create an empty Entity Framework model via Entity Developer. In the Solution Explorer window, call the context menu of the project, and 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 MySqlFullTextSearch.
2. In the project, add references to the following assemblies:

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

3. Add the latest public version of EF Code-First to the project: run Package Manager Console via the Visual Studio Tools menu -> 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, 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.
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. 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 Full-Text Index on Single Column

Presetting Database and Model under the Database-First Approach

1. Create the Books table and a full-text index on the Text column:

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

Presetting Database and Model under the Code-First Approach

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

Here we don’t specify any attributes for the ID property, as it is a Int32 property with the name ‘Id’, it will be automatically mapped as a server-generated auto-incremental primary key.
For the Author property we set the VARCHAR(200) data type.
For the Name property we set the VARCHAR(300) data type.
For the Content property we don’t set any length restrictions, so this string property will be mapped to LONGTEXT.
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 by specifying the storage engine type for the table, and creating a full-text index on the Content column, without specifying explicitly the name of the generated 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 Natural Language and Boolean Mode Queries

In this tutorial, we have turned on MySqlMonitor to view executed DDL and DML statements in the Devart dbMonitor application. However, keep in mind that the use of MySqlMonitor 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 and forming full-text search queries.

Using Full-Text Index on Multiple Columns

Presetting Database and Model under the Database-First Approach

1. Add a multiple-column full-text index for the existing Books table

2. There is no need to perform Update From Database for the EF model, as the XML model doesn’t store information about indexes.

Presetting Database and Model under the Code-First Approach

1. To generate an empty migration run, the following command in Package Manager Console:

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

2. Complement the migration with commands for creating (and deleting) the full-text index:

3. Update the database by applying the migration, for this, run the following command in Package Manager Console:

As a result, the following command is executed:

and the command for filling the HistoryTable system table with the information about the applied migration.

Full-Text Search Sample Using Multiple Columns

Write the code forming queries to several columns, using the natural language query and the natural language with query expansion.

Conclusion

Thus, we have shown how to use MySQL full-text search 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 full-text indexing and searching in our provider.
You can download the archive with the complete version of the sample from this tutorial here.

Comments are closed.