An issue with the way SQLite handles Unicode characters

[suatsuphi]

Hi,

An issue with the way SQLite handles Unicode characters that when I use tolower that it is not working.

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlite("Data Source=test.db")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Blog> Blogs { get; set; }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Blog {Title = "Akaryakıt Ürünlerine Uygulanacak ÖTV Tutarlarının Güncellenmesi Hk."});
            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            // Works:
            var searchValue = "uygulanacak";
            Console.WriteLine(context.Blogs.FirstOrDefault(e => e.Title.ToLower().Contains(searchValue))?.Title ?? "Not found.");
            // Doesn't work:
            searchValue = "akaryakıt ürünlerine uygulanacak";
            Console.WriteLine(context.Blogs.FirstOrDefault(e => e.Title.ToLower().Contains(searchValue))?.Title ?? "Not found.");
        }
    }
}

[pawelsalawa]

This is a SQLite specific question, which I'm unable to help you with.
You probably want to ask it on the stackoverflow, or someone from the sqlite.org.

[suatsuphi]

ok thank you.

[tuffnatty]

@suatsuphi I think you need the SQLite ICU extension. There is a feature proposal to bundle it by default (#3488)with SQLiteStudio. In the meanwhile, you can compile the extension yourself, find a compiled version, or use the prebuilt unicode extension from https://github.com/nalgeon/sqlean if you don't need all of ICU functionality.

[suatsuphi]

Hi, thank you @tuffnatty
I have two question.
1- It'is not working for .UseSqlite

Exception thrown: 'Microsoft.Data.Sqlite.SqliteException' in Microsoft.Data.Sqlite.dll
An unhandled exception of type 'Microsoft.Data.Sqlite.SqliteException' occurred in Microsoft.Data.Sqlite.dll
SQLite Error 1: ''.

2- Can we merge all dll for one file ?

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Data;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;


Console.WriteLine("----------------------------");
 
using (BloggingContext context = new BloggingContext())
{
    if (!context.Posts.Any())
    {
        context.Posts.Add(new Post { Title = "aj" });
        context.Posts.Add(new Post { Title = "aJ" });
        context.Posts.Add(new Post { Title = "aK" });
        context.Posts.Add(new Post { Title = "ak" });
        context.Posts.Add(new Post { Title = "aA" });
        context.Posts.Add(new Post { Title = "ab" });
        context.Posts.Add(new Post { Title = "aC" });
        context.Posts.Add(new Post { Title = "ac" });
        context.Posts.Add(new Post { Title = "aÇ" });
        context.Posts.Add(new Post { Title = "aÇ" });
        context.Posts.Add(new Post { Title = "aD" });
        context.Posts.Add(new Post { Title = "ad" });
        context.Posts.Add(new Post { Title = "aE" });
        context.Posts.Add(new Post { Title = "ae" });
        context.Posts.Add(new Post { Title = "aF" });
        context.Posts.Add(new Post { Title = "af" });
        context.Posts.Add(new Post { Title = "aG" });
        context.Posts.Add(new Post { Title = "ag" });
        context.Posts.Add(new Post { Title = "aĞ" });
        context.Posts.Add(new Post { Title = "ağ" });
        context.Posts.Add(new Post { Title = "aH" });
        context.Posts.Add(new Post { Title = "ah" });
        context.Posts.Add(new Post { Title = "aI" });
        context.Posts.Add(new Post { Title = "aı" });
        context.Posts.Add(new Post { Title = "aİ" });
        context.Posts.Add(new Post { Title = "ai" });


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

    context.connection.Open();
    context.connection.EnableExtensions();
    var command = context.connection.CreateCommand();
    command.CommandText = @"select load_extension('C:\Users\FB\source\repos\ConsoleAppForLIKE\ConsoleAppForLIKE\bin\Debug\net7.0\crypto.dll');";
    command.ExecuteScalar();
    command.CommandText = @"select load_extension('C:\Users\FB\source\repos\ConsoleAppForLIKE\ConsoleAppForLIKE\bin\Debug\net7.0\unicode.dll');";
    command.ExecuteScalar();
    
    var command2 = context.connection.CreateCommand();
    command2.CommandText = @"SELECT * FROM Posts WHERE Title like '%ai%' ";
    command2.Parameters.AddWithValue("$id", "ai");
    using (var reader = command2.ExecuteReader())
    {
        while (reader.Read())
        {
            var name = reader.GetString(1);

            Console.WriteLine($">>>>> {name} ");
        }
    }

    context.connection.Close();
    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
{
    public SqliteConnection connection = new SqliteConnection("Data Source=BlogDB.db");

    public DbSet<Post> Posts { get; set; }
    public BloggingContext()
    {
        Database.EnsureDeleted();
        Database.EnsureCreated();
        //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.Open();
        //connection.EnableExtensions();
        //connection.LoadExtension(@"select load_extension('C:\Users\FB\source\repos\ConsoleAppForLIKE\ConsoleAppForLIKE\bin\Debug\net7.0\unicode.dll');");

        optionsBuilder.UseSqlite(connection);
        
    }



}

[tuffnatty]

@suatsuphi Well, it's not related to SQLiteStudio, you need to ask it in a SQLite/C# specific group.