// @nuget: Microsoft.EntityFrameworkCore.Sqlite -Version 9.0.1

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Microsoft.EntityFrameworkCore;

using var db = new BloggingContext();

db.Database.EnsureDeleted();
db.Database.EnsureCreated();

var clientSideEntities = db.MyEntities.ToList().AsQueryable();

// constants work as expected
LogResults(q => q.Where(x => x.MyHex == 16)); // 1(16, 10), 2(16, 16)
LogResults(q => q.Where(x => x.MyDec == 16)); // 2(16, 16)

var myInt = 16;
// parameters work as expected
LogResults(q => q.Where(x => x.MyHex == myInt)); // 1(16, 10), 2(16, 16)
LogResults(q => q.Where(x => x.MyDec == myInt)); // 2(16, 16)

// column-column comparison does not work as expected
LogResults(q => q.Where(x => x.MyHex == x.MyDec));
// expected: 2(16, 16)
// got: 1(16, 10)

// expressions can succeed with unexpected results
LogResults(q => q.Select(x => x.MyHex + 0));
// expected: 16, 16
// got(SqlServer): 256, 256
// got(Sqlite): 10, 10

// expressions can fail on SQL server
LogResults(q => q.Select(x => x.MyHex - 1));
// expected: 15, 15
// got(SqlServer): The data types nvarchar(max) and nvarchar are incompatible in the subtract operator.
// got(Sqlite): 9, 9

void LogResults<T>(Func<IQueryable<MyEntity>, IEnumerable<T>> list)
{
    var expected = list(clientSideEntities).ToList();
    var actual = list(db.MyEntities).ToList();
    PrintList("expected: ", expected);
    PrintList("got: ", actual);
}

void PrintList<T>(string prefix, IEnumerable<T> list)
{
    try
    {
        Console.WriteLine(prefix + string.Join(", ", list.Select(x => x?.ToString())));
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

public class BloggingContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
            .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
            .EnableSensitiveDataLogging()
            //.UseSqlServer("use a valid connection string here");
            .UseSqlite("Data Source=test.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyEntity>()
            .Property(x => x.MyHex)
            .HasConversion(
                v => v.ToString("x"),
                v => int.Parse(v, System.Globalization.NumberStyles.HexNumber)
            );

        modelBuilder.Entity<MyEntity>()
            .Property(x => x.MyDec)
            .HasConversion(
                v => v.ToString(),
                v => int.Parse(v)
            );

        modelBuilder.Entity<MyEntity>().HasData(new MyEntity { Id = 1, MyHex = 16, MyDec = 10 });
        modelBuilder.Entity<MyEntity>().HasData(new MyEntity { Id = 2, MyHex = 16, MyDec = 16 });
    }
}

public class MyEntity
{
    public int Id { get; set; }
    public int MyHex { get; set; }
    public int MyDec { get; set; }

    public override string ToString() => $"{Id}({MyHex}, {MyDec})";
}