how to override like or contain operator in sqlite ?

[suatsuphi]

Hi, how to override like operator?
What I want to do is to add the upper operator automatically when the like operator is used. where name like '%keyword%' > where upper(name) like '%upper(keyword)%'

connection.CreateFunction("upper", (string x) => x.ToUpper(), isDeterministic: true);

or

how to override CONTAINS method `

// Licensed to the .NET Foundation under one or more agreements.
// The .NET Foundation licenses this file to you under the MIT license.

using System.Globalization;
using System.Runtime.CompilerServices;

namespace System
{
    public partial class String
    {
        public bool Contains(string value)
        {
            if (value == null)
                ThrowHelper.ThrowArgumentNullException(ExceptionArgument.value);

            return SpanHelpers.IndexOf(
                ref _firstChar,
                Length,
                ref value._firstChar,
                value.Length) >= 0;
        }

`

[roji]

@suatsuphi you're probably looking to do case-insensitive comparisons, see this doc page. See also case-insensitive column collations, and the docs on how to configure them in EF.

[suatsuphi]

hi @roji thank you, Yes, problem is about case-insensitive. I know that "The LIKE operator in SQLite doesn't honor collations. The default implementation has the same semantics as the NOCASE collation. It's only case-insensitive for the ASCII characters A through Z." I need patch for non ascii characters.... I mean when it is case-insensitive disabled, it have to work... actually you don't need case-sensitive for non ascii characters.

If I intervene the codes manually, it works as follows;

productsByKeywords =
    from p in _productRepository.Table
    where p.Name.ToUpper().Contains(keywords.ToUpper(CultureInfo.CurrentCulture)) ||
        (searchDescriptions &&
            (p.ShortDescription.Contains(keywords) || p.FullDescription.Contains(keywords))) ||
        (searchManufacturerPartNumber && p.ManufacturerPartNumber == keywords) ||
        (searchSku && p.Sku == keywords)
    select p.Id;

Here it only works for stock name.

Is it possible to write an override for the contains method? or the like operator...

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using LinqToDB;
using LinqToDB.Data;
using LinqToDB.DataProvider;
using LinqToDB.DataProvider.SQLite;
using LinqToDB.SqlQuery;
using Microsoft.Data.Sqlite;
using Newtonsoft.Json.Linq;
using Nop.Core;
using Nop.Core.Infrastructure;
using Nop.Data.Migrations;

//https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqliteparametercollection.contains?view=msdata-sqlite-7.0.0

namespace Nop.Data.DataProviders
{
    
    /// <summary>
    /// Represents the SQLite data provider
    /// </summary>
    public partial class SqLiteNopDataProvider : BaseDataProvider, INopDataProvider
    {
 
        #region Consts
        //it's quite fast hash (to cheaply distinguish between objects)
        private const string HASH_ALGORITHM = "SHA1";
        private static DataConnection _dataContext;
        #endregion

        #region Methods
        public void CreateDatabase(string collation, int triesToConnect = 10)
        { 
            ExecuteNonQueryAsync("PRAGMA journal_mode=WAL;PRAGMA writable_schema=1;").Wait(); // PRAGMA case_sensitive_like = 0;
        }

        /// <summary>
        /// Gets a connection to the database for a current data provider
        /// </summary>
        /// <param name="connectionString">Connection string</param>
        /// <returns>Connection to a database</returns>
        protected override DbConnection GetInternalDbConnection(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException(nameof(connectionString));

            var connection = new SqliteConnection(string.IsNullOrEmpty(connectionString)
                ? DataSettingsManager.LoadSettings().ConnectionString
                : connectionString);
            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.Open(); 
            return connection;
        }

        /// <summary>
        /// Initialize database
        /// </summary>
        public override void InitializeDatabase()
        { 
            var migrationManager = EngineContext.Current.Resolve<IMigrationManager>();
            var targetAssembly = typeof(NopDbStartup).Assembly;
            migrationManager.ApplyUpMigrations(targetAssembly);
            //mark update migrations as applied
            migrationManager.ApplyUpMigrations(targetAssembly, MigrationProcessType.Update, true);
        }

        /// <summary>
        /// Gets the name of a foreign key
        /// </summary>
        /// <param name="foreignTable">Foreign key table</param>
        /// <param name="foreignColumn">Foreign key column name</param>
        /// <param name="primaryTable">Primary table</param>
        /// <param name="primaryColumn">Primary key column name</param>
        /// <returns>Name of a foreign key</returns>
        public string CreateForeignKeyName(string foreignTable, string foreignColumn, string primaryTable, string primaryColumn)
        {
            return "FK_" + HashHelper.CreateHash(Encoding.UTF8.GetBytes($"{foreignTable}_{foreignColumn}_{primaryTable}_{primaryColumn}"), HASH_ALGORITHM);
        }

        /// <summary>
        /// Gets the name of an index
        /// </summary>
        /// <param name="targetTable">Target table name</param>
        /// <param name="targetColumn">Target column name</param>
        /// <returns>Name of an index</returns>
        public string GetIndexName(string targetTable, string targetColumn)
        {
            return "IX_" + HashHelper.CreateHash(Encoding.UTF8.GetBytes($"{targetTable}_{targetColumn}"), HASH_ALGORITHM);
        }

        /// <summary>
        /// Get the current identity value
        /// </summary>
        /// <typeparam name="TEntity">Entity</typeparam>
        /// <returns>Integer identity; null if cannot get the result</returns>
        public Task<int?> GetTableIdentAsync<TEntity>() where TEntity : BaseEntity
        {
            var tableName = DataContext.GetTable<TEntity>().TableName;

            var result = DataContext.Query<int?>($"select seq from sqlite_sequence where name = \"{tableName}\"")
                .FirstOrDefault();

            return Task.FromResult<int?>(result ?? 1);
        }
  
        public Task<bool> DatabaseExistsAsync()
        {
            return Task.FromResult(DatabaseExists());
        }

        /// <summary>
        /// Checks if the specified database exists, returns true if database exists
        /// </summary>
        /// <returns>Returns true if the database exists.</returns>
        public bool DatabaseExists()
        {
            return true;
        }

        /// <summary>
        /// Creates a backup of the database
        /// </summary>
        public virtual Task BackupDatabaseAsync(string fileName)
        {
            throw new DataException("This database provider does not support backup");
        }

        /// <summary>
        /// Restores the database from a backup
        /// </summary>
        /// <param name="backupFileName">The name of the backup file</param>
        public virtual Task RestoreDatabaseAsync(string backupFileName)
        {
            throw new DataException("This database provider does not support backup");
        }

        /// <summary>
        /// Re-index database tables
        /// </summary>
        public Task ReIndexTablesAsync()
        {
            DataContext.Execute("VACUUM;");
            return Task.CompletedTask;
        }

        /// <summary>
        /// Build the connection string
        /// </summary>
        /// <param name="nopConnectionString">Connection string info</param>
        /// <returns>Connection string</returns>
        public string BuildConnectionString(INopConnectionStringInfo nopConnectionString)
        {
            if (nopConnectionString is null)
                throw new ArgumentNullException(nameof(nopConnectionString));

            if (nopConnectionString.IntegratedSecurity)
                throw new NopException("Data provider supports connection only with password");

            var builder = new SqliteConnectionStringBuilder
            {
                DataSource = CommonHelper.DefaultFileProvider.MapPath($"~/App_Data/{nopConnectionString.DatabaseName}.db"),
                Password = nopConnectionString.Password,
                Mode = SqliteOpenMode.ReadWrite,
                Cache = SqliteCacheMode.Shared,
                Pooling = true,
            };

            return builder.ConnectionString;
        }

        /// <summary>
        /// Set table identity (is supported)
        /// </summary>
        /// <typeparam name="TEntity">Entity</typeparam>
        /// <param name="ident">Identity value</param>
        public Task SetTableIdentAsync<TEntity>(int ident) where TEntity : BaseEntity
        {
            var tableName = DataContext.GetTable<TEntity>().TableName;
            DataContext.Execute($"update sqlite_sequence set seq = {ident} where name = \"{tableName}\"");

            return Task.CompletedTask;
        }

        /// <summary>
        /// Creates database command instance using provided command text and parameters.
        /// </summary>
        /// <param name="sql">Command text</param>
        /// <param name="dataParameters">Command parameters</param>
        protected override CommandInfo CreateDbCommand(string sql, DataParameter[] dataParameters)
        { 
            if (dataParameters is null)
                throw new ArgumentNullException(nameof(dataParameters));

            return new CommandInfo(DataContext, sql, dataParameters);
        }

        #endregion

        #region Properties

        public DataConnection DataContext => _dataContext ??= CreateDataConnection();

        /// <summary>
        /// Linq2Db data provider
        /// </summary>
        protected override IDataProvider LinqToDbDataProvider { get; } = SQLiteTools.GetDataProvider(ProviderName.SQLiteMS);

        /// <summary>
        /// Gets allowed a limit input value of the data for hashing functions, returns 0 if not limited
        /// </summary>
        public int SupportedLengthOfBinaryHash { get; } = 0;

        /// <summary>
        /// Gets a value indicating whether this data provider supports backup
        /// </summary>
        public bool BackupSupported { get; } = false;

        #endregion
    }

}

[roji]

I need patch for non ascii characters.... I mean when it is case-insensitive disabled, it have to work... actually you don't need case-sensitive for non ascii characters.

I'm a bit confused - so your problem is with non-ASCII characters, right?

If you want to replace the LIKE function, you can follow the link under the LIKE docs section linked above, and define a user-defined function.

[suatsuphi]

thank you your attention @roji

productsByKeywords =
    from p in _productRepository.Table
    where p.Name.Contains(keywords) ||
        (searchDescriptions &&
            (p.ShortDescription.Contains(keywords) || p.FullDescription.Contains(keywords))) ||
        (searchManufacturerPartNumber && p.ManufacturerPartNumber == keywords) ||
        (searchSku && p.Sku == keywords)
    select p.Id;

I don't wanna use upper or lower function. I wanna use like function. There is no example for LIKE function

Operator	Function
X LIKE Y	like(Y, X)
X LIKE Y ESCAPE Z	like(Y, X, Z)

I try this that it is not working: connection.CreateFunction("like", (string x, string y) => x.ToUpper().IndexOf(y.ToUpper(CultureInfo.CurrentCulture)) >= 0, isDeterministic: true);

could you give an example about like function ? there are two parameter escape z that what it is ? how is it use ?

[roji]

I try this that it is not working:

What does "not working" mean? Please always post actual errors/exceptions you're seeing.

Regardless, what you're doing looks wrong: LIKE does not correspond to IndexOf, because it supports wildcards (%, _). So this would be an incorrect definition which would make standard SQL queries fail. You'll have to implement the actual LIKE logic including wildcards.

On another note, rather than calling ToUpper before comparing, you can just specify a case-insensitive culture in .NET, e.g. x.IndexOf(y, StringCOmparison.OrdinalIgnoreCase).

[suatsuphi]

thank you @roji this is not about bug. this topic type is 'Ask a question'. my code is wrong so it is not working. there is no example about like function. Could you give an example about LIKE FUNCTION. I wanna try override LIKE.

[roji]

@suatsuphi you can see here for a C# implementation of the SQL LIKE operator.

[suatsuphi]

thank you @roji

bruce said me https://learn.microsoft.com/en-us/answers/questions/1160856/createfunction-like connection.CreateFunction("like", (string a, string b) => a == b); but it is not work. I mean I can't change variable a or b a.toupper() or a.Replace("i","İ")

Could you share an exaple about DbFunctionsExtensions.Like ?

[roji]

@suatsuphi as I wrote above, you'll have to provide more information on how this doesn't work. What happens when you try to add ToUpper or Replace?

Note again that simply doing an equality check (==) is not an implementation for LIKE, since LIKE uses wildcards. If you want a simple equality check, just use equality in your LINQ queries instead.

[suatsuphi]

as I wrote above, you'll have to provide more information on how this doesn't work. What happens when you try to add ToUpper or Replace?

I wanna change non ascii character that it is problem in sqlite if i see a working code example about like

[roji]

@suatsuphi I've tried to help the best I could - I've pointed you to a working C# implementation of like, which you should be able to integrate in CreateFunction. You're not providing any details on what's exactly not working for you, so I don't know how I can help you further.

[suatsuphi]

thank you @roji I want to remove or change the value from the variable. I can do it for upper or lower so I need an example for like

_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);

finally;

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

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

[roji]

_connection.CreateFunction("upper", (string x) => x.ToUpper(), isDeterministic: true); _connection.CreateFunction("lower", (string x) => x.ToLower(), isDeterministic: true);

You shouldn't need to define upper/lower, since these are built-in functions in SQLite. You can simply call .NET's string.ToUpper/Lower, which EF translates to SQLite upper/lower as per the docs.

For LIKE, you need to copy and paste the C# implementation I pointed to above, and call that:

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

Where X.Like is this function, properly modified (e.g. not DbFunctions parameter).

[suatsuphi]

thank you @roji I failed, below is an example.

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
 
 
Console.WriteLine("----------------------------");

using (BloggingContext context = new BloggingContext())
{
    if (!context.Posts.Any())
    {
        context.Posts.Add(new Post { Title = "j" });
        context.Posts.Add(new Post { Title = "J" });
        context.Posts.Add(new Post { Title = "K" });
        context.Posts.Add(new Post { Title = "k" });
        context.Posts.Add(new Post { Title = "A" });
        context.Posts.Add(new Post { Title = "b" });
        context.Posts.Add(new Post { Title = "C" });
        context.Posts.Add(new Post { Title = "c" });
        context.Posts.Add(new Post { Title = "Ç" });
        context.Posts.Add(new Post { Title = "Ç" });
        context.Posts.Add(new Post { Title = "D" });
        context.Posts.Add(new Post { Title = "d" });
        context.Posts.Add(new Post { Title = "E" });
        context.Posts.Add(new Post { Title = "e" });
        context.Posts.Add(new Post { Title = "F" });
        context.Posts.Add(new Post { Title = "f" });
        context.Posts.Add(new Post { Title = "G" });
        context.Posts.Add(new Post { Title = "g" });
        context.Posts.Add(new Post { Title = "Ğ" });
        context.Posts.Add(new Post { Title = "ğ" });
        context.Posts.Add(new Post { Title = "H" });
        context.Posts.Add(new Post { Title = "h" });
        context.Posts.Add(new Post { Title = "I" });
        context.Posts.Add(new Post { Title = "ı" });
        context.Posts.Add(new Post { Title = "İ" });
        context.Posts.Add(new Post { Title = "i" });


        context.Posts.Add(new Post { Title = "İlk İçerik" });
        context.Posts.Add(new Post { Title = "ilk olmayan" });
    }
    context.SaveChanges();
    string value = "ilk";

    foreach (var item in context.Posts.OrderBy(i => i.Title))
    {
        Console.WriteLine(item.Title);
    }
    Console.WriteLine("-----------search-----------");
    foreach (var item in context.Posts.Where(i => i.Title.Contains(value)))
    {
        Console.WriteLine(item.Title);
    }
    Console.WriteLine("----------------------------");

    Console.ReadLine();
}

public class Post
{
    public int PostId { get; set; }

    public string Title { get; set; }
}
 
public class BloggingContext : DbContext
{
    SqliteConnection _connection = new SqliteConnection("Data Source=BlogDB.db");
    public DbSet<Post> Posts { get; set; }
    public BloggingContext()
    {
        Database.EnsureDeleted();
        Database.EnsureCreated();
        // _connection = new SqliteConnection("Data Source=BlogDB.db");

        _connection = (SqliteConnection)Database.GetDbConnection();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>().Property(t => t.Title).HasColumnType("TEXT COLLATE NOCASE");
    }


    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", (string a, string b) =>  EF.Functions.Like(a.Replace("i", ""), b.Replace("i","")), isDeterministic: true);
        //EF.Functions.Like(matchExpression, pattern)

        optionsBuilder.UseSqlite(_connection);
    }

}

[roji]

_connection.CreateFunction("like", (string a, string b) => EF.Functions.Like(a.Replace("i", ""), b.Replace("i","")), isDeterministic: true);

That's not what I wrote above. You need to copy and paste the code I linked to into your own project, and call that function. EF.Functions.Like no longer contains a C# implementation of Like in recent versions of EF Core.

[suatsuphi]

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

what is X?

[roji]

@suatsuphi that's explained in my comment https://github.com/dotnet/efcore/issues/30063#issuecomment-1383992890. You're going to have to copy the code into your project, in some class (which I'm referring to as X in my sample).

[suatsuphi]

thank you @roji

I could not achieve what I want with the code below.

_connection.CreateFunction("like", (string a, string b) =>
            DbFunctionsExtensions.Like(EF.Functions, a.Replace("i", ""), b.Replace("i", ""))
        ,isDeterministic: true);

[roji]

@suatsuphi I really don't know what else to say - you're not following any of my instructions, and short of coding the solution for you, I don't see how I could explain it... I suggest reading the conversation again and trying to follow the steps I've laid out above.

[suatsuphi]

thank you @roji there is an one step that what X is.

I don't know how to configure matchExpression. I need overide it

[roji]

@suatsuphi copy and paste the following LikeImplementation class into your project. This contains a .NET implementation of LIKE, which you may need to tweak to your needs, e.g. make it case-insensitive.

LikeImplementation class

static class LikeImplementation
{
    public static bool Like(string? matchExpression, string? pattern)
        => LikeCore(matchExpression, pattern, escapeCharacter: null);

    public static bool Like(string? matchExpression, string? pattern, string? escapeCharacter)
        => LikeCore(matchExpression, pattern, escapeCharacter);

    // Regex special chars defined here:
    // https://msdn.microsoft.com/en-us/library/4edbef7e(v=vs.110).aspx

    private static readonly char[] _regexSpecialChars
        = { '.', '$', '^', '{', '[', '(', '|', ')', '*', '+', '?', '\\' };

    private static readonly string _defaultEscapeRegexCharsPattern
        = BuildEscapeRegexCharsPattern(_regexSpecialChars);

    private static readonly TimeSpan _regexTimeout = TimeSpan.FromMilliseconds(value: 1000.0);

    private static string BuildEscapeRegexCharsPattern(IEnumerable<char> regexSpecialChars)
    {
        return string.Join("|", regexSpecialChars.Select(c => @"\" + c));
    }

    private static bool LikeCore(string? matchExpression, string? pattern, string? escapeCharacter)
    {
        var singleEscapeCharacter =
            string.IsNullOrEmpty(escapeCharacter)
                ? (char?)null
                : escapeCharacter.First();

        if (matchExpression == null
            || pattern == null)
        {
            return false;
        }

        if (matchExpression.Equals(pattern, StringComparison.OrdinalIgnoreCase))
        {
            return true;
        }

        if (matchExpression.Length == 0
            || pattern.Length == 0)
        {
            return false;
        }

        var escapeRegexCharsPattern
            = singleEscapeCharacter == null
                ? _defaultEscapeRegexCharsPattern
                : BuildEscapeRegexCharsPattern(_regexSpecialChars.Where(c => c != singleEscapeCharacter));

        var regexPattern
            = Regex.Replace(
                pattern,
                escapeRegexCharsPattern,
                c => @"\" + c,
                default,
                _regexTimeout);

        var stringBuilder = new StringBuilder();

        for (var i = 0; i < regexPattern.Length; i++)
        {
            var c = regexPattern[i];
            var escaped = i > 0 && regexPattern[i - 1] == singleEscapeCharacter;

            switch (c)
            {
                case '_':
                    {
                        stringBuilder.Append(escaped ? '_' : '.');
                        break;
                    }
                case '%':
                    {
                        stringBuilder.Append(escaped ? "%" : ".*");
                        break;
                    }
                default:
                    {
                        if (c != singleEscapeCharacter)
                        {
                            stringBuilder.Append(c);
                        }

                        break;
                    }
            }
        }

        regexPattern = stringBuilder.ToString();

        return Regex.IsMatch(
            matchExpression,
            @"\A" + regexPattern + @"\s*\z",
            RegexOptions.IgnoreCase | RegexOptions.Singleline,
            _regexTimeout);
    }
}

Once you've done that, use CreateFunction to make like to it:

connection.CreateFunction(
    "like",
    (string pattern, string matchExpression) => LikeImplementation.Like(matchExpression, pattern),
    isDeterministic: true);

[suatsuphi]

thank you @roji

I was try this. there is a break point for "var test =" line. it is not working I mean break point

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Text;
using System.Text.RegularExpressions;
 
Console.WriteLine("----------------------------");

using (BloggingContext context = new BloggingContext())
{
    
    if (!context.Posts.Any())
    {
        context.Posts.Add(new Post { Title = "j" });
        context.Posts.Add(new Post { Title = "J" });
        context.Posts.Add(new Post { Title = "K" });
        context.Posts.Add(new Post { Title = "k" });
        context.Posts.Add(new Post { Title = "A" });
        context.Posts.Add(new Post { Title = "b" });
        context.Posts.Add(new Post { Title = "C" });
        context.Posts.Add(new Post { Title = "c" });
        context.Posts.Add(new Post { Title = "Ç" });
        context.Posts.Add(new Post { Title = "Ç" });
        context.Posts.Add(new Post { Title = "D" });
        context.Posts.Add(new Post { Title = "d" });
        context.Posts.Add(new Post { Title = "E" });
        context.Posts.Add(new Post { Title = "e" });
        context.Posts.Add(new Post { Title = "F" });
        context.Posts.Add(new Post { Title = "f" });
        context.Posts.Add(new Post { Title = "G" });
        context.Posts.Add(new Post { Title = "g" });
        context.Posts.Add(new Post { Title = "Ğ" });
        context.Posts.Add(new Post { Title = "ğ" });
        context.Posts.Add(new Post { Title = "H" });
        context.Posts.Add(new Post { Title = "h" });
        context.Posts.Add(new Post { Title = "I" });
        context.Posts.Add(new Post { Title = "ı" });
        context.Posts.Add(new Post { Title = "İ" });
        context.Posts.Add(new Post { Title = "i" });


        context.Posts.Add(new Post { Title = "İlk İçerik" });
        context.Posts.Add(new Post { Title = "ilk olmayan" });
    }
    context.SaveChanges();
    string value = "ilk";

 
    Console.WriteLine("-----------search-----------");
    foreach (var item in context.Posts.Where(i => i.Title.Contains(value))
        .OrderBy(i => i.Title)
        )
    {
        Console.WriteLine(item.Title);
    }
    Console.WriteLine("----------------------------");

    Console.ReadLine();
}

public class Post
{
    public int PostId { get; set; }

    public string Title { get; set; }
}

public class BloggingContext : DbContext
{
    SqliteConnection connection = new SqliteConnection("Data Source=BlogDB.db");
    public DbSet<Post> Posts { get; set; }
    public BloggingContext()
    {
        Database.EnsureDeleted();
        Database.EnsureCreated();
        // _connection = new SqliteConnection("Data Source=BlogDB.db");
 

        connection = (SqliteConnection)Database.GetDbConnection();
          
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>().Property(t => t.Title).HasColumnType("TEXT COLLATE NOCASE");
    }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        //https://learn.microsoft.com/en-us/ef/core/providers/sqlite/functions#string-functions
        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.ToUpper(), isDeterministic: true);
        connection.CreateFunction("like", (string pattern, string matchExpression) =>
        {
            var test = matchExpression;

            return LikeImplementation.Like(matchExpression.ToUpper(), pattern);
        }
        ,
     isDeterministic: true);


        optionsBuilder.UseSqlite(connection);
    }


    static class LikeImplementation
    {
        public static bool Like(string? matchExpression, string? pattern)
            => LikeCore(matchExpression, pattern, escapeCharacter: null);

        public static bool Like(string? matchExpression, string? pattern, string? escapeCharacter)
            => LikeCore(matchExpression, pattern, escapeCharacter);

        // Regex special chars defined here:
        // https://msdn.microsoft.com/en-us/library/4edbef7e(v=vs.110).aspx

        private static readonly char[] _regexSpecialChars
            = { '.', '$', '^', '{', '[', '(', '|', ')', '*', '+', '?', '\\' };

        private static readonly string _defaultEscapeRegexCharsPattern
            = BuildEscapeRegexCharsPattern(_regexSpecialChars);

        private static readonly TimeSpan _regexTimeout = TimeSpan.FromMilliseconds(value: 1000.0);

        private static string BuildEscapeRegexCharsPattern(IEnumerable<char> regexSpecialChars)
        {
            return string.Join("|", regexSpecialChars.Select(c => @"\" + c));
        }

        private static bool LikeCore(string? matchExpression, string? pattern, string? escapeCharacter)
        {
            var singleEscapeCharacter =
                string.IsNullOrEmpty(escapeCharacter)
                    ? (char?)null
                    : escapeCharacter.First();

            if (matchExpression == null
                || pattern == null)
            {
                return false;
            }

            if (matchExpression.Equals(pattern, StringComparison.OrdinalIgnoreCase))
            {
                return true;
            }

            if (matchExpression.Length == 0
                || pattern.Length == 0)
            {
                return false;
            }

            var escapeRegexCharsPattern
                = singleEscapeCharacter == null
                    ? _defaultEscapeRegexCharsPattern
                    : BuildEscapeRegexCharsPattern(_regexSpecialChars.Where(c => c != singleEscapeCharacter));

            var regexPattern
                = Regex.Replace(
                    pattern,
                    escapeRegexCharsPattern,
                    c => @"\" + c,
                    default,
                    _regexTimeout);

            var stringBuilder = new StringBuilder();

            for (var i = 0; i < regexPattern.Length; i++)
            {
                var c = regexPattern[i];
                var escaped = i > 0 && regexPattern[i - 1] == singleEscapeCharacter;

                switch (c)
                {
                    case '_':
                        {
                            stringBuilder.Append(escaped ? '_' : '.');
                            break;
                        }
                    case '%':
                        {
                            stringBuilder.Append(escaped ? "%" : ".*");
                            break;
                        }
                    default:
                        {
                            if (c != singleEscapeCharacter)
                            {
                                stringBuilder.Append(c);
                            }

                            break;
                        }
                }
            }

            regexPattern = stringBuilder.ToString();

            return Regex.IsMatch(
                matchExpression,
                @"\A" + regexPattern + @"\s*\z",
                RegexOptions.IgnoreCase | RegexOptions.Singleline,
                _regexTimeout);
        }
    }



}

[roji]

@suatsuphi I'm sorry, I'm not going to be able to help without clear information. If you have an exception, then I need the full exception details.

[suatsuphi]

@roji I shared all the lines of the console application above. copy paste and start console application just put break point for var test line... this is what I mean thank you

[roji]

I've tried my best to help above, over many back-and-forths, but you're not providing full error reports.

What's the exception you're getting? "A break point for line 'var test...'" doesn't tell me anything.

[suatsuphi]

does not enter the break point and static method

[ajcvickers]

@suatsuphi We have tried very hard to help you here, but I don't think there is anything else we can do.

[suatsuphi]

I explained everything with console app and shared it. but it is claimed that there is no problem.