Using MySQL Full-Text Search in Entity Framework

July 10, 2012

9256

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
Full-Text Indexes in Code-First Migrations
Tutorial Overview
Prerequisites
Using Full-Text Index on Single Column
Using Full-Text Index on Multiple Columns
Conclusion

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:

SELECT *
  FROM MyTable
 WHERE MATCH (Column1) AGAINST ('MyExpression')

you can use:

  var query = ctx.MyTables
    .Where(t => MySqlTextFunctions.MatchAgainstAsBool(t.Column1, "MyExpression"));

If you have to write a query like:

SELECT *
  FROM MyTable
 WHERE MATCH (Column1) AGAINST ('MyExpression' WITH QUERY EXPANSION) > 0.5

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

  var query = ctx.MyTables
    .Where(t => MySqlTextFunctions.MatchAgainstWithQueryExpansion(t.Column1, "MyExpression") > 0.5);

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

SELECT t.*, MATCH (Column1) AGAINST ('+MyExpression1 -MyExpression2' IN BOOLEAN MODE) "Score"
  FROM MyTable t
 WHERE MATCH (Column1) AGAINST ('+MyExpression1 -MyExpression2' IN BOOLEAN MODE)

can be written like this:

 var query = ctx.MyTables
    .Where(t => MySqlTextFunctions.MatchAgainstInBooleanModeAsBool(t.Column1, "+MyExpression1 -MyExpression2"))
    .Select(t => new {
       MyTable = t,
       Score = MySqlTextFunctions.MatchAgainstInBooleanMode(t.Column1, "+MyExpression1 -MyExpression2")
    });

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:

MySQL Full-Text Indexing and Full Text Search;
Entity Framework;
Code-First using the Data Annotation mapping;
Code-based Code-First Migrations;
Query execution monitoring via Devart dbMonitor.

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:

Install-Package EntityFramework

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:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using Devart.Data.MySql;

namespace MySqlFullTextSearch {

  public class MyContext: DbContext {

    public MyContext() {
    }

    public MyContext(MySqlConnection connection)
      : base(connection, false) {
    }

    static MyContext() {

      var config = Devart.Data.MySql.Entity.Configuration.MySqlEntityProviderConfig.Instance;
      config.Workarounds.DisableQuoting = true;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {

    }

  }
}

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:

Enable-Migrations

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

    public Configuration()
    {
      AutomaticMigrationsEnabled = false;
    }

by specifying the SQL generator:

    public Configuration()
    {
      AutomaticMigrationsEnabled = false;

      SetSqlGenerator(Devart.Data.MySql.Entity.Migrations.MySqlConnectionInfo.InvariantName,
              new Devart.Data.MySql.Entity.Migrations.MySqlEntityMigrationSqlGenerator());
    }

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:

<connectionstrings>
  <add name="MyContext" connectionString="User Id=my_user;Password=user_password;Host=my_server;Port=3306;Database=my_database;" providerName="Devart.Data.MySql" />
</connectionstrings>

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:

CREATE TABLE Books ( 
  Id int AUTO_INCREMENT UNIQUE NOT NULL,
  Author varchar(200) NULL,
  Name varchar(300) NULL,
  Content longtext NULL,
  PRIMARY KEY (Id),
  FULLTEXT INDEX (Content)
) ENGINE = MyISAM

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:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;

namespace MySqlFullTextSearch {

  public class Book {

    //[Key]
    public int Id { get; set; }

    [MaxLength(200)]
    public string Author { get; set; }

    [MaxLength(300)]
    public string Name { get; set; }

    public string Content { get; set; }

  }
}

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:

public DbSet<Book> Books { get; set; }

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

Add-Migration AddBook

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

    public partial class AddBook : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Books",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Author = c.String(maxLength: 200),
                        Name = c.String(maxLength: 300),
                        Content = c.String(unicode: false),
                    })
                .PrimaryKey(t => t.Id);
        }

        public override void Down()
        {
            DropTable("Books");
        }
    }

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:

    using Devart.Data.MySql.Entity;
    using Devart.Data.MySql.Entity.Migrations;

    public partial class AddBook : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Books",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Author = c.String(maxLength: 200),
                        Name = c.String(maxLength: 300),
                        Content = c.String(unicode: false),
                    },
                anonymousArguments: new MySqlCreateTableConfiguration() {
                        StorageEngine = MySqlStorageEngine.MyISAM,                   
                    })
                .Index(c => c.Content,
                    anonymousArguments: new MySqlCreateIndexConfiguration() {
                        IsFulltext = true
                    })
                .PrimaryKey(t => t.Id);
        }

        public override void Down()
        {
            DropTable("Books");
        }
    }

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

Update-Database -Verbose

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

CREATE TABLE Books ( 
  Id int AUTO_INCREMENT UNIQUE NOT NULL,
  Author varchar(200) NULL,
  Name varchar(300) NULL,
  Content longtext NULL,
  PRIMARY KEY (Id),
) ENGINE = MyISAM;

CREATE FULLTEXT INDEX IX_Books_Text ON Books (Text);

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

var monitor = new MySqlMonitor() { IsActive = true };

and run dbMonitor.
Let’s write the code filling the table with data and forming full-text search queries.

using (var ctx = new MyContext()) {

  // Load information into the Books table
  ctx.Books.Add(new Book() {
    Author = "William Shakespeare",
    Name = "The Tragedy of King Richard the Second",
    Content = @"ACT 1
SCENE I. London. A Room in the palace.
[Enter KING RICHARD, attended; JOHN OF GAUNT, with other NOBLES.]
KING RICHARD.
  Old John of Gaunt, time-honoured Lancaster,
  Hast thou, according to thy oath and band,
  Brought hither Henry Hereford thy bold son,
  Here to make good the boisterous late appeal,
  Which then our leisure would not let us hear,
  Against the Duke of Norfolk, Thomas Mowbray?
..."
  });
  ctx.Books.Add(new Book() {
    Author = "William Shakespeare",
    Name = "The Life and Death of King John",
    Content = @"ACT 1.
SCENE I. Northampton. A Room of State in the Palace.
[Enter KING JOHN, QUEEN ELINOR, PEMBROKE, ESSEX, SALISBURY, and others, with CHATILLON.]
KING JOHN.
  Now, say, Chatillon, what would France with us?
CHATILLON.
  Thus, after greeting, speaks the King of France,
  In my behaviour, to the majesty,
  The borrow'd majesty of England here.
..."
  });
  ctx.Books.Add(new Book() {
    Author = "William Shakespeare",
    Name = "The Life of Henry the Fifth",
    Content = @"ACT 1.
SCENE I. London. An ante-chamber in the King's palace.
[Enter the Archbishop of Canterbury and the Bishop of Ely.]
CANTERBURY.
  My lord, I'll tell you: that self bill is urg'd,
  Which in the eleventh year of the last king's reign
  Was like, and had indeed against us pass'd,
  But that the scambling and unquiet time
  Did push it out of farther question.
..."
  });
  ctx.SaveChanges();

  // Write an analogue of the SQL query:
  //  SELECT id, name FROM books
  //  WHERE MATCH (content) AGAINST ('king palace Norfolk');
  var firstQuery = ctx.Books
    .Where(b => MySqlTextFunctions.MatchAgainstAsBool(b.Content, "king palace Norfolk"))
    .Select(b => new { b.Id, b.Name } );
  var firstQueryResults = firstQuery.ToList();

  // Output:
  Console.WriteLine();
  Console.WriteLine("== Querying your single column full-text index ==");
  Console.WriteLine();
  Console.WriteLine("1) Natural language");
  Console.WriteLine();
  Console.WriteLine("SQL:");
  Console.WriteLine(firstQuery.ToString());
  Console.WriteLine();
  Console.WriteLine("Data:");
  Console.WriteLine("{0,-5} | {1}", "Id", "Name");
  Console.WriteLine(new string('-', 50));
  foreach (var item in firstQueryResults)
    Console.WriteLine("{0,-5} | {1} ", item.Id, item.Name);        

  // Write an analogue of the SQL query:
  //   SELECT id, name FROM books
  //   WHERE MATCH (content) AGAINST ('+king +palace -Norfolk' IN BOOLEAN MODE)
  var secondQuery = ctx.Books
            .Where(b => MySqlTextFunctions.MatchAgainstInBooleanModeAsBool(b.Content, "+king +palace -Norfolk"))
            .Select(b => new { b.Id, b.Name } );
  var secondQueryResults = secondQuery.ToList();

  // Output:
  Console.WriteLine();
  Console.WriteLine("2) Boolean mode");
  Console.WriteLine();
  Console.WriteLine("SQL:");
  Console.WriteLine(secondQuery.ToString());
  Console.WriteLine();
  Console.WriteLine("Data:");
  Console.WriteLine("{0,-5} | {1}", "Id", "Name");
  Console.WriteLine(new string('-', 50));
  foreach (var item in secondQueryResults)
    Console.WriteLine("{0,-5} | {1} ", item.Id, item.Name);
}

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

CREATE FULLTEXT INDEX IX_Books_Author_Name ON Books (Author, Name)

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:

Add-Migration CreateAdditionalIndex

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

    public partial class CreateAdditionalIndex : DbMigration
    {
        public override void Up()
        {
        }

        public override void Down()
        {
        }
    }

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

using Devart.Data.MySql.Entity.Migrations;

    public partial class CreateAdditionalIndex : DbMigration
    {
        public override void Up()
        {
          CreateIndex(
            table: "Books",
            name: "IX_Books_Author_Name",
            columns: new string[] { "Author", "Name" },
            anonymousArguments: new MySqlCreateIndexConfiguration() {
              IsFulltext = true
            });
        }

        public override void Down()
        {
          DropIndex(table: "Books", name: "IX_Books_Author_Name");
        }
    }

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

Update-Database -Verbose

As a result, the following command is executed:

CREATE FULLTEXT INDEX IX_Books_Author_Name ON Books (Author, Name)

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.

using (var ctx = new MyContext()) {

  // Write an analogue of the SQL query:
  //   SELECT id, name FROM books
  //   where MATCH (Author, Name) AGAINST ('william king life death');
  var firstQuery = ctx.Books
    .Where(b => MySqlTextFunctions.MatchAgainstAsBool(b.Author, b.Name, "william king life death"))
    .Select(b => new { b.Id, b.Name });
  var firstQueryResults = firstQuery.ToList();

  // Output
  Console.WriteLine();
  Console.WriteLine("== Querying your multiple-column full-text index ==");
  Console.WriteLine();
  Console.WriteLine("3) Natural language");
  Console.WriteLine();
  Console.WriteLine("SQL:");
  Console.WriteLine(firstQuery.ToString());
  Console.WriteLine();
  Console.WriteLine("Data:");
  Console.WriteLine("{0,-5} | {1}", "Id", "Name");
  Console.WriteLine(new string('-', 50));
  foreach (var item in firstQueryResults)
    Console.WriteLine("{0,-5} | {1} ", item.Id, item.Name);

  // Write an analogue of the SQL query:
  //  SELECT b.*, MATCH (Author, Name) AGAINST ('william king life death' WITH QUERY EXPANSION)
  //  FROM books b WHERE MATCH (Author, Name) AGAINST ('william king life death' WITH QUERY EXPANSION) > 0.8
  var secondQuery = ctx.Books
            .Where(b => MySqlTextFunctions.MatchAgainstWithQueryExpansion(b.Author, b.Name, "william king life death") > 0.8)
            .Select(b => new {
              Book = b,
              Score = MySqlTextFunctions.MatchAgainstWithQueryExpansion(b.Author, b.Name, "william king life death")
            });
  var secondQueryResults = secondQuery.ToList();

  // Output
  Console.WriteLine();
  Console.WriteLine("4) Natural language with query expansion");
  Console.WriteLine();
  Console.WriteLine("SQL:");
  Console.WriteLine(secondQuery.ToString());
  Console.WriteLine();
  Console.WriteLine("Data:");
  Console.WriteLine("{0,-5} | {1,-40} | {2}", "Id", "Name", "Score");
  Console.WriteLine(new string('-', 70));
  foreach (var item in secondQueryResults)
    Console.WriteLine("{0,-5} | {1,-40} | {2}", item.Book.Id, item.Book.Name, item.Score);

}

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.

Using MySQL Full-Text Search in Entity Framework

Introduction

Full-Text Search in LINQ to Entities

Full-Text Indexes in Code-First Migrations

Tutorial Overview

Prerequisites

Presetting Database and Model under the Database-First Approach

Presetting Database and Model under the Code-First Approach

Using Full-Text Index on Single Column

Presetting Database and Model under the Database-First Approach

Presetting Database and Model under the Code-First Approach

Full-Text Search Sample Using Natural Language and Boolean Mode Queries

Using Full-Text Index on Multiple Columns

Presetting Database and Model under the Database-First Approach

Presetting Database and Model under the Code-First Approach

Full-Text Search Sample Using Multiple Columns

Conclusion

How to Handle Null or Empty Values in SQL Server

Connect to PostgreSQL Database Using psql, pgAdmin, and PostgreSQL Client Tool

How to Rename a MySQL Database

Whitepaper

Social

Topics

Products

Company

Using MySQL Full-Text Search in Entity Framework

Introduction

Full-Text Search in LINQ to Entities

Full-Text Indexes in Code-First Migrations

Tutorial Overview

Prerequisites

Presetting Database and Model under the Database-First Approach

Presetting Database and Model under the Code-First Approach

Using Full-Text Index on Single Column

Presetting Database and Model under the Database-First Approach

Presetting Database and Model under the Code-First Approach

Full-Text Search Sample Using Natural Language and Boolean Mode Queries

Using Full-Text Index on Multiple Columns

Presetting Database and Model under the Database-First Approach

Presetting Database and Model under the Code-First Approach

Full-Text Search Sample Using Multiple Columns

Conclusion

How to Handle Null or Empty Values in SQL Server

Connect to PostgreSQL Database Using psql, pgAdmin, and PostgreSQL Client Tool

How to Rename a MySQL Database

Whitepaper

Social

Topics

Products

<img src="https://blog.devart.com/wp-content/uploads/2022/12/devart-logo.png" alt="Devart" class="img-responsive" width="106" height="30" loading="lazy">

Company