Query PostgreSQL with Npgsql and Entity Framework using unaccent

entity-framework-6 npgsql postgresql


Is possible to use Npgsql and Entity Framework 6 for query PostgreSQL ignoring accents? In play SQL it's possible to use the unaccent extension and could be indexed with a index also based in unaccent:

select * from users where unaccent(name) = unaccent('João')

In previous projects using MySql I could solve this problem just using a collation accent insensitive like utf8_swedish_ci but PostgreSQL lacks this kind of solution as far as I know.

4/27/2016 1:20:14 PM

Accepted Answer

If you use the Codefirst approach, you should try to use EntityFramework.CodeFirstStoreFunctions.

  1. First add EntityFramework.CodeFirstStoreFunctions to your project
  2. Add a custom convention with unaccent to DbModelBuilder
  3. Use it in a query.

Example of database context:

public class DatabaseContext : DbContext
    public DatabaseContext () : base("Context")

    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

        /** Adding unaccent **/           
        modelBuilder.Conventions.Add(new CodeFirstStoreFunctions.FunctionsConvention<DatabaseContext>("public"));

    [DbFunction("CodeFirstDatabaseSchema", "unaccent")]
    public string Unaccent(string value)
        // no need to provide an implementation
        throw new NotSupportedException();

Example of usage:

var users = ctx.Users
               .Where(elem => ctx.Unaccent(elem.FirstName) == ctx.Unaccent("João"))

Important notice:
This solution works with EntityFramework6.Npgsql (which uses Npgsql 3.*).
It doesn't work with Npgsql.EntityFramework (which uses Npgsql 2.*)

6/7/2016 1:12:13 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow