Fuzzy text search: support for Daitch-Mokotoff function

[profet23]

I don't seem to see a built in function for:

https://www.postgresql.org/docs/current/fuzzystrmatch.html#FUZZYSTRMATCH-DAITCH-MOKOTOFF

Is it missing? Undocumented? Any reason it isn't supported?

[profet23]

There's probably a way to do this on my own until an official method exists. But the documentation feels a bit lacking around Array type mappings. Any help would be appreciated.

The daitch_mokotoff postgres function takes a text argument, and returns a text[].

This is not correct:

		modelBuilder.HasDbFunction(typeof(SqlFunctions)
			.GetMethod(nameof(DaitchMokotoffSoundex), [typeof(string)])!)
			.HasTranslation(TranslateDaitchMokotoffSoundex);

public static string[] DaitchMokotoffSoundex(string source)
{
	throw new InvalidOperationException($"{nameof(DaitchMokotoffSoundex)} should be performed on database");
}

	
public static SqlExpression TranslateDaitchMokotoffSoundex(IReadOnlyCollection<SqlExpression> args)
{
	// Get the source string parameter
	var source = args.First();
	
	var daitchMokotoffExpression = new SqlFunctionExpression(
		"daitch_mokotoff",
		new[] { source },
		true,
		new[] { true },
		typeof(string[]),
		new NpgsqlArrayTypeMapping<string[], string[], string>
			(new NpgsqlStringTypeMapping("text", NpgsqlDbType.Text)));

	return daitchMokotoffExpression;
}

Any help with what I'm doing wrong would be deeply appreciated.

[roji]

@profet23 the Daitch-Mokotoff function was added only recently to PG's fuzzystrmatch extensions (in PG 16), which is why there's no translation for it. You're welcome to submit a PR to add support for it, just like the EF.Functions.FuzzyStringMatchSoundex which is already supported.

Otherwise, in your code sample, rather than trying to instantiate NpgsqlArrayTypeMapping yourself, you can simply look up the type mapping for string[] via the type mapping source of the provider - that should be quite trivial and should work (check out other translators for examples).

BTW I'm also noticing that we don't actually have documentation for the fuzzy text search translations, submitted #405 npgsql/doc#405 to fix this.

[profet23]

@profet23 the Daitch-Mokotoff function was added only recently to PG's fuzzystrmatch extensions (in PG 16), which is why there's no translation for it. You're welcome to submit a PR to add support for it, just like the EF.Functions.FuzzyStringMatchSoundex which is already supported.

Otherwise, in your code sample, rather than trying to instantiate NpgsqlArrayTypeMapping yourself, you can simply look up the type mapping for string[] via the type mapping source of the provider - that should be quite trivial and should work (check out other translators for examples).

BTW I'm also noticing that we don't actually have documentation for the fuzzy text search translations, submitted #405 to fix this.

Is that the correct issue number? "405" ?

[profet23]

@roji Thanks.

I'm REALLY close but I'm still having issues.

If I define my translation as:

public static string[] DaitchMokotoffSoundex(string source)
{
	throw new InvalidOperationException($"{nameof(DaitchMokotoffSoundex)} should be performed on database");
}

	
public static SqlExpression TranslateDaitchMokotoffSoundex(IRelationalTypeMappingSource typeMappingSource, IReadOnlyCollection<SqlExpression> args)
{
	// Get the source string parameter
	var source = args.First();
	
	var daitchMokotoffExpression = new SqlFunctionExpression(
		"daitch_mokotoff",
		new[] { source },
		true,
		new[] { true },
		typeof(string[]),
		typeMappingSource.FindMapping(typeof(string[]))
 );

	return daitchMokotoffExpression;
}

Then I can execute the function and get a valid result array if I perform it as a select.

But if I try to do an operation on the result, say an Any operation or an Intersect operation, the expression fails to be translated with an exception:

could not be translated. Additional information: Translation of method 'System.Linq.Enumerable.Any' failed.

I understand this isn't a support forum. But any quick tips would be appreciated.

[roji]

Is that the correct issue number? "405" ?

Sorry, wrong repo... The correct link is: npgsql/doc#405

But if I try to do an operation on the result, say an Any operation or an Intersect operation, the expression fails to be translated with an exception

Yeah, that's an unfortunate current limitation of EF itself - I opened dotnet/efcore#35980 to track this on the EF side. This is unrelated to Daitch-Mokotoff: it's a general limitation around composing LINQ operators on the return values of function calls in EF.