[SQLite]How to use Like operator with insensitive?

[doghappy]

   return db.Articles.AsNoTracking()
          .Where(a => EF.Functions.Like(a.Title, $"%{Keyword}%"))
          .ToListAsync()

it does not work

[smitpatel]

@doghappy - Can you elaborate more what you are trying to achieve and what is current error message? Expected & actual SQLs help.

[doghappy]

I need use Like.
Have this data in SQLite

Id	Title
1	EntityFramework
2	entityframework

return db.Articles.AsNoTracking()
          .Where(a => EF.Functions.Like(a.Title, $"%entityframework%"))
          .ToListAsync()

I want get 2 data. entityframework and EntifyFramework.
if database is SQL Server, I can get expected result, but SQLite can't.
SQLite will only return entityframework to me

@smitpatel

[bricelam]

LIKE on SQLite is case-insensitive by default. Are you using a version compiled with SQLITE_CASE_SENSITIVE_LIKE or are you executing PRAGMA case_sensitive_like?

[doghappy]

@bricelam I don't know, what is SQLITE_CASE_SENSITIVE_LIKE, PRAGMA case_sensitive_like

I can get expected data use this sql

select * from Articles where Title like '%entityframework%'

but EF can't

EF.Functions.Like(a.Title, $"%entityframework%");

[bricelam]

We suspect the Like() method is being evaluated locally and not on the server. We need to dig into this more...

[smitpatel]

I am unable to reproduce this. Repro code:

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new MyContext())
            {
                // Recreate database
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                // Seed database
                db.AddRange(
                    new Article { Title = "EntityFramework" },
                    new Article { Title = "entityframework" }
                    );

                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                // Run queries
                var count = db.Articles.AsNoTracking()
                    .Where(b => EF.Functions.Like(b.Title, $"%entityframework%"))
                    .ToListAsync().GetAwaiter().GetResult().Count;

                Console.WriteLine(count);
            }

            Console.WriteLine("Program finished.");
        }
    }

    public class MyContext : DbContext
    {
        private static ILoggerFactory LoggerFactory => new LoggerFactory().AddConsole(LogLevel.Trace);

        // Declare DBSets
        public DbSet<Article> Articles { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Select 1 provider
            optionsBuilder
                //.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
                .UseSqlite("filename=_modelApp.db")
                //.UseInMemoryDatabase(databaseName: "_modelApp")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(LoggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
        }
    }

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

Generated SQL

      SELECT "b"."Id", "b"."Title"
      FROM "Articles" AS "b"
      WHERE "b"."Title" LIKE '%entityframework%'

[doghappy]

@smitpatel For ConsoleApp it work. but it dosen't work on ASP.NET Core MVC.

the difference is DI

ConsoleApp

using (var db = new MyContext())
{
    // like query
}

ASP.NET Core

public class HomeController : Controller
{
    public HomeController(MyContext db)
    {
        this.db = db;
    }
}

[smitpatel]

@doghappy - There is literally no difference in query translation with regards to how the context is initialized (with or without DI).

Please provide full repro solution which demonstrate the issue.

[doghappy]

lol, it’s my own issue, not EF. thank you @smitpatel