How to do a multiple OrderBy in LINQ across two tables.

This article will show example solutions to accomplish multiple OrderBy commands in one query in LINQ across multiple tables.

Table 1: Movies

Columns: MovieID, MovieName, and CategoryID (FK to Categories table)

Table 2: Categories

Columns: CategoryID, CategoryName

Goal: We want to select movies and order the records first by CategoryName and secondly by MovieName.

First, we can select movies and order them by CategoryName like this:

var movieRecords = dbContext.Movies.OrderBy(c => c.CategoryName);

Next, we introduce the ThenBy function and apply it to our query like this:

var movieRecords = dbContext.Movies.OrderBy(c => c.CategoryName).ThenBy(m => m.MovieName)

When we iterate through the resulting movieRecords, the items will be ordered by CategoryName and MovieName.

Another approach to accomplishing the multiple order by query without using Lambda expressions can look like this:

var movieRecords = from movieRecord in dbContext.Movies 
             orderby movieRecord.MovieCategory, movieRecord.MovieName
             select movieRecord;

Simply, separate the order by columns with a comma and write a from X orderby and select LINQ statement like the above example.

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:

http://www.entityframework.info/Home/FullTextSearch

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)
    {
        RewriteFullTextQuery(command);
    }
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        RewriteFullTextQuery(command);
    }
    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)
                    continue;
                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(
                        text,
                        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:

http://stackoverflow.com/questions/42097593/mysql-slow-query-when-combining-two-very-fast-queries

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