How to use COLLATE Latin1_General_bin in Entity framework?

Question

I have to use COLLATE in entity framework query. How to write SQL query equivalent in Entity Framework as show below code?

SQL query:

select * from AspNetUsers order by Email COLLATE Latin1_General_bin

Entity Framework:

         using (var db = new testEntities())
            {
                var appUsers = await db.Users.OrderBy(x => x.Email).ToListAsync();
            }

score 0 · Answer 1 · answered Dec 13 '19 at 21:07

It's possible to use Entity Framework's interception hooks.

The first step it to define an interface:

interface ISortInterceptable
{
    IEnumerable<string> AdaptableSortFieldNames { get; set; }
}

Then make your context implement it:

class TestEntities : DbContext, ISortInterceptable
{
    ...
    public IEnumerable<string> AdaptableSortFieldNames { get; set; }
    ...
}

Next, create a command interceptor:

class SortCommandInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command,
        DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if (interceptionContext.DbContexts.First() is ISortInterceptable interceptable
            && interceptable.AdaptableSortFieldNames != null)
        {
            var query = command.CommandText;

            foreach (var fieldName in interceptable.AdaptableSortFieldNames)
            {
                var pattern = $@"(.*\s*ORDER BY\s*.*\.)(\[{fieldName}\])(.*)";
                query = Regex.Replace(query, pattern, "$1$2 COLLATE Latin1_General_bin $3");
            }
            command.CommandText = query;
        }

        base.ReaderExecuting(command, interceptionContext);
    }
}

This is where all the magic happens.

The interceptor first checks if it has to do with a ISortInterceptable (maybe this check can be refined by getting all ISortInterceptables from interceptionContext.DbContexts).
The command text in the command to be executed is analyzed on any occurence of strings like ORDER BY [Alias].[fieldName] where fieldName is a variable. This search pattern is in keeping with the pattern EF always follows to generates queries.
The field name part of the ORDER BY clause, which is in the third group ($2) of the regex match, is extended by the collation phrase.
The replacement is repeated for all field names.

Finally, an example of how to use this interceptor:

DbInterception.Add(new SortCommandInterceptor());
using (var db = new TestEntities())
{
    db.AdaptableSortFieldNames = new[] { "LastName", "Email" };

    var users = db.AspNetUsers
                  .OrderBy(u => u.LastName)
                  .ThenBy(u => U.Email)
                  .ToList();
}

As always with string manipulation, one caveat: this works in a couple of my own tests, but I can't guarantee it to be rock solid. For one, the sorting fields should be text fields, of course.

One last note. EF core 3 also offers interception hooks that can be used in a similar way.

How to use COLLATE Latin1_General_bin in Entity framework?

1 Answers1