使用 unaccent 使用 Npgsql 和 Entity Framework 查询 PostgreSQL [英] Query PostgreSQL with Npgsql and Entity Framework using unaccent
问题描述
是否可以使用 Npgsql 和 Entity Framework 6 来查询 PostgreSQL 忽略重音符号?在 play SQL 中,可以使用 unaccent 扩展,并且可以使用同样基于 unaccent 的索引进行索引:
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')
在以前使用 MySql 的项目中,我可以仅使用不区分排序规则的重音符号(如 utf8_swedish_ci)来解决此问题,但据我所知 PostgreSQL 缺乏这种解决方案.
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.
推荐答案
如果你使用Codefirst的方式,你应该尝试使用EntityFramework.CodeFirstStoreFunctions.
If you use the Codefirst approach, you should try to use EntityFramework.CodeFirstStoreFunctions.
- 首先将 EntityFramework.CodeFirstStoreFunctions 添加到您的项目中
- 将带有 unaccent 的自定义约定添加到 DbModelBuilder
- 在查询中使用它.
- First add EntityFramework.CodeFirstStoreFunctions to your project
- Add a custom convention with unaccent to DbModelBuilder
- Use it in a query.
数据库上下文示例:
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();
}
}
使用示例:
var users = ctx.Users
.Where(elem => ctx.Unaccent(elem.FirstName) == ctx.Unaccent("João"))
.ToList();
重要提示:
此解决方案适用于 EntityFramework6.Npgsql(使用 Npgsql 3.*).
它不适用于 Npgsql.EntityFramework(使用 Npgsql 2.*)
Important notice:
This solution works with EntityFramework6.Npgsql (which uses Npgsql 3.*).
It doesn't work with Npgsql.EntityFramework (which uses Npgsql 2.*)
这篇关于使用 unaccent 使用 Npgsql 和 Entity Framework 查询 PostgreSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!