LoadExtension SQLite

[suatsuphi]

Hi,

LoadExtension is not working with ddl of https://github.com/nalgeon/sqlean/releases/tag/0.19.3

Is there a reason ?

connection.Open();
connection.EnableExtensions(true);
connection.LoadExtension(filename);

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate. Also, please provide the message and full stack trace of any exceptions thrown.

[suatsuphi]

there is no detail about any exceptions

https://github.com/suatsuphi/ConsoleAppSqlean

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: ''.

connection.LoadExtension(file.ToString());

[ajcvickers]

@suatsuphi This works on my machine when I use LoadExtension. Are you sure you have compatible versions of the extensions? I downloaded from https://github.com/nalgeon/sqlean/releases/tag/0.19.3

[suatsuphi]

sqlean-win-x64.zip

it is working

//connection.LoadExtension(file.ToString());
var command = connection.CreateCommand();
command.CommandText = $"select load_extension(\"{file}\"); "; ;
command.ExecuteScalar();

it is not working

connection.LoadExtension(file.ToString());
//var command = connection.CreateCommand();
//command.CommandText = $"select load_extension("{file}"); "; ;
//command.ExecuteScalar();

[ajcvickers]

@suatsuphi Don't know what to tell you. Running your code works for me.

[suatsuphi]

I know running my code because of me.

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

I updated it try again

[ajcvickers]

@suatsuphi Still works for me.

[ajcvickers]

@suatsuphi Still works for me.

[suatsuphi]

how ? could you explain ?

[ajcvickers]

@suatsuphi You want me to explain how something works the way it is supposed to?

[suatsuphi]

well, I downloaded my project https://github.com/suatsuphi/ConsoleAppSqlean

it is not working. You said me that It is working.

connection.LoadExtension("file")

[ajcvickers]

Just noticed you are on 7.0.1. You should always be on the latest patch version--I was running with 7.0.4.

[suatsuphi]

thank you so much.
I said that my project was not working.... :-)

But now, It is just working for commandText

It is not working for UseSqlite

[ajcvickers]

I downloaded your project, updated it to 7.0.4:

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.4" />
  </ItemGroup>

Changed the path to where I the SQLite native dlls are located on my machine (since I'm not going to load random native code posted on the Internet!):

var path = Path.GetFullPath(Path.Combine(@"C:\local\code\repros\sqlean-win-x64"));

And it runs fine:

C:/local/code/repros/ConsoleAppSqlean-master/ConsoleAppSqlean-master/ConsoleAppSqlean/bin/Debug/net7.0/ConsoleAppSqlean.exe
----------------------------
>>>>> aI
>>>>> aI
>>>>> ai
>>>>> aIlk Içerik
>>>>> ailk olmayan
-----------search-----------
ai
ailk olmayan
----------------------------


Process finished with exit code 0.

[suatsuphi]

It is working now that I know it.

But, This is commandText:

----------------------------
>> aI
>> aI
>> ai
>> aIlk Içerik
>> ailk olmayan

it is from UseSqlite :

-----------search-----------
ai
ailk olmayan
----------------------------

I mean It is not working for UseSqlite

Could you read code details ?

[ajcvickers]

I don't know what you mean not working for UseSqlite I ran exactly the code you pointed to. Of course it has UseSqlite in there, or nothing would work...

This is the Program.cs file I am using:

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";



    var command2 = context.connection.CreateCommand();
    command2.CommandText = @"SELECT * FROM Posts WHERE Title like '%ai%' ";
    using (var reader = command2.ExecuteReader())
    {
        while (reader.Read())
        {
            var name = reader.GetString(1);
            Console.WriteLine($">>>>> {name} ");
        }
    }


    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();
        connection.Open();
        connection.EnableExtensions();

        var path = Path.GetFullPath(Path.Combine(@"C:\local\code\repros\sqlean-win-x64"));
        DirectoryInfo d = new DirectoryInfo(path);
        FileInfo[] files = d.GetFiles("*.dll");
 
        foreach (var file in files)
        {
            connection.LoadExtension(file.ToString());
            //var command = connection.CreateCommand();
            //command.CommandText = $"select load_extension(\"{file}\"); "; ;
            //command.ExecuteScalar();
        }
    
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>().Property(t => t.Title).HasColumnType("TEXT COLLATE NOCASE");
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(connection);
    }


}

[suatsuphi]

I mean I have to get same result. Because of like '%ai%' and contain("ai")

[ErikEJ]

i.Title.Contains(value))

Is probably not translated using LIKE .

[ajcvickers]

@suatsuphi So, is this issue no longer about calling LoadExtension? But rather than you think the translation for Contains should be have in the same way as the like function you are using? If so, this is because Contains is translated as below, and is case-sensitive. This is by-design.

      SELECT "p"."PostId", "p"."Title"
      FROM "Posts" AS "p"
      WHERE @__value_0 = '' OR instr("p"."Title", @__value_0) > 0
      ORDER BY "p"."Title"

[suatsuphi]

I guess you mean that like and contain are not equal

[ajcvickers]

Correct. They are not the same.

[suatsuphi]

But It is working for SqlServer. There is patch for SQLite but it is just working for commandText, it is not working for UseSqlite

[ajcvickers]

@suatsuphi SQLite and SQL Server behave in different ways. We haven't implemented support for full text search (including like) on SQLite (see #4823). If we do, it would require an extension, so it's unlikely the default implementation of Contains would be changed.

Regardless, SQLite and SQL Server will always behave differently for any number of different queries. There should never be any expectation that two different relational databases will behave the same way for an arbitrary query.

[suatsuphi]

It is working for commandText. It is not working for UseSqlite. I mean Unicode Character. It is not complicated. I quess... please complete!

[suatsuphi]

i.Title.Contains(value))

Is probably not translated using LIKE .

loadextentions doesn't work if you are using UseSqlite. shouldn't it work? why are unicode characters not wanted to work?

[ajcvickers]

@suatsuphi You are again being very unclear. To clarify what I think you mean, no loading of extensions will change the SQL that EF generates. This is by-design. This is not going to change.