SQL Server: translation for the PATINDEX function

[roji]

SQL Server has PATINDEX, which allows getting the index of a LIKE-like pattern (as opposed to just checking whether the pattern matches). EF6 had SqlFunctions.PatIndex.

Raised by @irg1008 in #6159.

[imangd]

Hi @roji , I would be glad to work on this issue as my first contribution if it hasn't been worked on yet :)

[roji]

Sure thing, feel free to submit a PR!

[imangd]

Thank you @roji! I'd like to share my plan and hopefully receive feedback/insights on whether or not I'm on the right track. Based on the discussion on 6159 and what I have learned from the codebase so far, I believe I should add a new PatIndex method to DbFunctionsExtensions and add a translation for that method in SqlServerStringMethodTranslator. Does that sound right?

[roji]

The PatIndex would go on SqlServerDbFunctionsExtensions, not DbFunctionsExtensions, as it's a SQL Server-specific thing. And yeah, implementing the translation in SqlServerStringMethodTranslator makes sense.

[smnsht]

Hi, @roji

I have a working branch (ready for PR) for this issue with a decent number of unity tests.
(started my work without announcing it)

In my solution, "PATINDEX" resides with SqlServerFullTextSearchFunctionsTranslator, and handles queries like

var result = context.Employees
    .Where(
        c => EF.Functions.PatIndex("%London%", c.City) > 0
            && EF.Functions.PatIndex("%Manager%", c.Title, "Latin1_General_BIN") > 0)
    .FirstOrDefault();

I added unit tests similar to those that exist for EF.Functions.FreeText, replacing language with collate. All works.

SqlServerStringMethodTranslator translates string methods, like str.Trim(), str.TrimLeft(), etc...
Can you confirm it is the place for patindex?

Shall I move my changes to SqlServerStringMethodTranslator?

[roji]

Shall I move my changes to SqlServerStringMethodTranslator?

Yes. "full text" in the SQL Server context means something very specific (see docs), and PATINDEX isn't part of that. In any case, where exactly the translator lives isn't very important.