How can I include unaccent pgsql function in Dynamic Linq queries?

[rdeharo]

I need to make a query like this:

habitantes = _context.HABITANTES.Where(h => _context.Unaccent(h.Apellido1) == _context.Unaccent("GARCÍA"));

But I don't know how to put the Unaccent postgress function in mi where string;

whereStr = $"{nameField} {searchOpeStr} "{searchString}"";.

[zspitz]

Not familiar with PostgreSQL, but how would you call the Unaccent function from a strongly-typed query in C#?

You'd probably use this or another overload.

I would suggest registering the DbFunctions class with Dynamic LINQ so its' members will be recognized by the parser.

[rdeharo]

Hi,

A strongly-typed EF 6 query may be something like this:

habitantes = _context.HABITANTES.Where(h => _context.Unaccent(h.Apellido1) == _context.Unaccent("GARCÍA"));

And it works fine.

I forgot to say before that I'm working with Entity Framework 6 NOT with Entity Framework Core.

So, I think the solutions you give me are only suitable for EF Core, right?

[zspitz]

On which type is the Unaccent method defined for the EF6 provider? I can see that for the EF Core provider it's defined on the static class Microsoft.EntityFrameworkCore.NpgsqlFullTextSearchDbFunctions, but I can't seem to find the same for EF6.

If that type (presumably inheriting from DbContext) is an instance method, you could pass in the context as a parameter, and call Unaccent on it:

habitantes = _context.HABITANTES.Where($"@0.Unaccent({nameField}) {searchOpeStr} @0.Unaccent(\"{searchString}\")", _context);

---

If that type is a static type, you'll need to register the type using a custom type provider. For example, the EF Core functions could be registered like this:

using Microsoft.EntityFrameworkCore;

public class PostgreSQLTypePRovider : DefaultDynamicLinqCustomTypeProvider {
    public override HashSet<Type> GetCustomTypes() =>
        new[] { typeof(NpgsqlFullTextSearchDbFunctions) }.ToHashSet();
}

Then, you set the provider in a config object, and pass the config object as the first parameter (although I'm not sure how to get an instance of DbFunctions:

var config = new ParsingConfig {
    CustomTypeProvider = new PostgreSQLTypePRovider()
};

var fns = /* get instance of DbFunctions; not sure how to do that */;
habitantes = _context.HABITANTES.Where(config, $"NpgsqlFullTextSearchDbFunctions.Unaccent(@0, {nameField}) {searchOpeStr} NpgsqlFullTextSearchDbFunctions.Unaccent(@0, \"{searchString}\")", fns);

[StefH]

@rdeharo Is this question still relevant to you? else I'll close it.

[StefH]

Closing...