CreateFunction Like

Question

CreateFunction Like

SUAT SUPHI 61

Hi,

I need an example about CreateFunction for like

Could you give me an example ?

https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions

2 answers

Your answer

Answer 1

Bruce (SqlWork.com) 81,971 Volunteer Moderator

connection.CreateFunction(
    "like",
    (string a, string b)  => a == b);

SUAT SUPHI 61 Reputation points

2023-01-15T22:46:55.52+00:00

thank you Bruce

can I replace a or b s strings ?

I guess I have to use DbFunctionsExtensions.Like but I don know how it use
Qing Guo - MSFT 896 Reputation points Microsoft External Staff

2023-01-16T03:13:42.7066667+00:00
Hi @SUAT SUPHI ,

Do you want to use DbFunctionsExtensions.Like as below?

.Where(e => EF.Functions.Like(x.SomeColumn, "filter.Keyword"))

SUAT SUPHI 61

Thank you

but I wanna use it in createfunction

there is an console app here:

_connection.CreateFunction("Like ", ...........................

SELECT * FROM Product where Replace(Name,'i','') like Replace('%ilk%','i','');

https://github.com/suatsuphi/ConsoleToLower/blob/master/ConsoleToLower/Program.cs

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        _connection.CreateCollation("NOCASE", (x, y) => string.Compare(x, y, ignoreCase: true));
        _connection.CreateFunction("upper", (string x) => x.ToUpper(), isDeterministic: true);
        _connection.CreateFunction("lower", (string x) => x.ToLower(), isDeterministic: true);

        //_connection.CreateFunction("like", 

        optionsBuilder.UseSqlite(_connection);
    }

Answer 2

@SUAT SUPHI, Welcome to Microsoft Q&A, if you want to make the function to replace string.

You could try the following code.

   _connection.CreateFunction("likeit",(string x,string y)=> y, isDeterministic: true);

Then, you could call it in the commandtext like the following:

    string sql = "select * from Posts where Title==likeit('i','h')";

The result is equal to

select * from Posts where Title='h'.

Note: I suggest that you don't use the like keyword in the sql because it already has some definitions.

Hope my solution could help you.

Best Regards,

Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our [documentation][3] to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

CreateFunction Like

2 answers

Your answer