Query PostgreSQL with Npgsql and Entity Framework using unaccent

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")    {        Database.SetInitializer<DatabaseContext>(null);    }    public DbSet<User> Users { get; set; }    protected override void OnModelCreating(DbModelBuilder modelBuilder)    {        modelBuilder.HasDefaultSchema("public");        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();        /** 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"))               .ToList();

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