C# Entity Framework implementing ‘Contains’ Query for MySQL

This article will show some tricks on how to use C# Entity Framework queries with ‘Contains’ predicates for a MySQL database.

The goal is to use C# queries like this against a MySQL database:

var result = dbContext.People.Where(p => p.Name.Contains("john"));

First, we need to create a Full-Text Search Interceptor. We’ll start with the one found at the following link:


That one is specific to SQL Server, so we’ll need to make some changes. After my changes, a version for MySQL is this:

static class LanguageExtensions
    public static bool In<T>(this T source, params T[] list)
        return (list as IList<T>).Contains(source);
public class FtsInterceptor : IDbCommandInterceptor
    private const string FullTextPrefix = "-FTSPREFIX-";
    public static string Fts(string search)
        return string.Format("({0}{1})", FullTextPrefix, search);
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    public static void RewriteFullTextQuery(DbCommand cmd)
        string text = cmd.CommandText;
        for (int i = 0; i < cmd.Parameters.Count; i++)
            DbParameter parameter = cmd.Parameters[i];
            if (parameter.DbType.In(DbType.String, DbType.AnsiString, DbType.StringFixedLength, DbType.AnsiStringFixedLength))
                if (parameter.Value == DBNull.Value)
                var value = (string)parameter.Value;
                if (value.IndexOf(FullTextPrefix) >= 0)
                    parameter.Size = 4096;
                    parameter.DbType = DbType.AnsiStringFixedLength;
                    value = value.Replace(FullTextPrefix, "");
                    value = value.Substring(1, value.Length - 2);
                    parameter.Value = value;
                    cmd.CommandText = Regex.Replace(
                        string.Format(@"`(\w*)`.`(\w*)`\s*LIKE\s*@{0}\s?", parameter.ParameterName),
                        string.Format(@"match(`$1`.`$2`) against(@{0})", parameter.ParameterName)

                    if (text == cmd.CommandText)
                        throw new Exception("FTS was not replaced on: " + text);
                    text = cmd.CommandText;

The most significant difference is in the Regex.Replace. For MySQL, we need to make our SQL use Full Text indices using the Match-Against combo like this:

select * from people WHERE MATCH (Name) against ('john')

Note: Be sure that your table has a Full Text index on the columns you are querying. You can always add a Full Text index to your table like this:

ALTER TABLE people ADD FULLTEXT INDEX people_name_FT_IDX (Name);

Next, we’ll need to add our FtsInterceptor to our DbContext like this:

public partial class MyAppMySqlDbEntities : DbContext
    public MyAppMySqlDbEntities()
        : base("name=MyAppMySqlDbEntities")
        DbInterception.Add(new FtsInterceptor());
        Configuration.UseDatabaseNullSemantics = true;

You’ll also need a using statement to go with those DbInterception calls.

using System.Data.Entity.Infrastructure;
using System.Data.Entity.Infrastructure.Interception;

Finally, we’re ready to make some queries:

var personNameFtsI = FtsInterceptor.Fts('john');
var query = myDbContext.People.Where(p => p.Name.Contains(personNameFtsI));
int count = query.Count();

I suggest you set a break point in the FtsInterceptor.RewriteFullTextQuery function, and when the query.Count line is executed that breakpoint should get hit. Then, you can step through that function and see how your SQL is changing to use the Match-Against combo that MySQL needs for a Full Text index search.

A special big thank you goes out to our friends at AlphaProspect.com for help figuring all of this out.

One final “gotcha” to watch out for is that MySQL seems to only like using one Full Text index per query, so if you do something like this:

var personNameFtsI = FtsInterceptor.Fts('john');
var personJobFtsI = FtsInterceptor.Fts('engineer');
var query = myDbContext.People.Where(p => p.Name.Contains(personNameFtsI) && p.JobTitle.Contains(personJobFtsI));
int count = query.Count();

The SQL result will look correct, and it should have two Match-Against clauses, but MySQL still won’t use both Full Text indices. I suggest you capture your SQL from in the FtsInterceptor.RewriteFullTextQuery function, and then execute it in the MySQL workbench. Get the execution plan or “explain” result for your query. That’ll show that MySQL is really only using one of the Full Text indices.

There’s a way to rewrite the queries to make use of both Full Text indices, but I have no idea how to make that happen using C# Entity Framework and Linq. Here’s a link about how MySQL can do it with temp tables:


If you solve this, I’d love to hear from you about how you did it. Thank you.

Must change item for configuring MySQL for Windows

I recently installed MySQL and the MySQL workbench on my Windows 10 PC. It’s working great. I loaded up about 2 GB of data in two 3 tables with roughly 2M rows total. Most of my simple (single table by ID or Full Text Index) queries ran really fast, and I was happy.

Then, I started working on queries that join tables. They were slow – like 26 seconds slow for a two table join by integer ID using a FK (simple stuff).

First, I checked my indices. I added several indices just trying things and hoping it’d work. That did not solve the slowness.

I had to tinker in Workbench quite a bit imagining what could be wrong and how do I identify the source problem, which turned out to be good learning exercise.

Eventually, I saw (on the Workbench Server’s dashboard) that InnoDB buffer usage went to 100% during my long running queries. I didn’t really know what that meant, but I was out of “good” ideas to try so I was happy to have any leads.

I asked the internet sages (search engines) about this “InnoDB buffer usage at 100%”, and I found some leads. It turns out that buffer size is set in “my.ini” (on Windows) or “my.cnf” (Not-windows). The default was 8MB, which is amazingly low for any modern computer. (MySQL on my phone would want a bigger value than that as a default.)

I found my.ini at this path on my Windows 10 PC:

C:\programdata\MySQL\MySQL Server 5.7\my.ini

Specifically, edit that file with notepad or notepad++. Look for this value:


I changed mine to 1G first, and now I’m using 2G. I don’t even think that’s a lot, but this is all on my dev-box so I don’t want to give it all of my RAM.


After you change that my.ini value, be sure to restart your MySQL service. 

  1. Type services.msc in to the Run box on Windows to bring up your services manager. 
  2. Find the MySQL service, and right click it. 
  3. There should be a “restart” option, so click that.

After this change, my 26 second simple table join query went to sub-second time.