using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using NUnit.Framework;

namespace TestName
{
    public class BoxDbContext : DbContext
    {
        public BoxDbContext(
            DbContextOptions<BoxDbContext> options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Box>().HasKey(x => x.Id);
            modelBuilder.Entity<Box>().Property(t => t.Id).ValueGeneratedOnAdd();
            base.OnModelCreating(modelBuilder);
        }
    }

    public class Box
    {
        public Guid Id { get; set; }
        public Guid SubId { get; set; }
    }

    [TestFixture]
    public class TestClass
    {
        private SqliteConnection SqliteConnection { get; set; }

        private DbContextOptions<BoxDbContext> Options => new DbContextOptionsBuilder<BoxDbContext>()
            .UseSqlite(SqliteConnection)
            .EnableSensitiveDataLogging()
            .Options;

        private DbContext GetDbContext()
        {
            BoxDbContext dbContext = new BoxDbContext(Options);
            dbContext.Database.EnsureCreated();
            return dbContext;
        }

        [SetUp]
        public void DbSetup()
        {
            SqliteConnectionStringBuilder sqliteConnectionStringBuilder = new SqliteConnectionStringBuilder
            {
                Mode = SqliteOpenMode.Memory,
                Cache = SqliteCacheMode.Private
            };
            SqliteConnection = new SqliteConnection(sqliteConnectionStringBuilder.ToString());
            SqliteConnection.Open();
        }

        [TearDown]
        public void DbTearDown()
        {
            SqliteConnection.Close();
        }

        [Test]
        public async Task OrderByGuid()
        {
            List<Guid> subIds = new List<Guid>
            {
                Guid.Parse("901CAB07-315F-4594-A5C6-C37725643DB8"),
                Guid.Parse("FA1760E7-27F4-4F8B-9205-44ACF2358044"),
                Guid.Parse("0C434803-0004-4894-8E29-597AA8BCF8E2"),
                Guid.Parse("C7E76CF2-35D1-4CF8-8A67-83F41842F052"),
                Guid.Parse("1D6F9038-B5B3-4559-9480-3A2651E52623"),
            };

            using (DbContext dbContext = GetDbContext())
            {
                foreach (Guid subId in subIds)
                {
                    dbContext.Set<Box>().Add(new Box {SubId = subId});
                }
                await dbContext.SaveChangesAsync();
            }

            IList<Box> boxs;
            using (DbContext approvalsDbContext = GetDbContext())
            {
                boxs = await approvalsDbContext
                    .Set<Box>()
                    .OrderByDescending(x => x.SubId)
                    .ToListAsync();
            }

            Assert.That(boxs.Count, Is.EqualTo(subIds.Count));
            Assert.That(boxs.ToArray()[0].SubId, Is.EqualTo(subIds[1]));
            Assert.That(boxs.ToArray()[1].SubId, Is.EqualTo(subIds[3]));
            Assert.That(boxs.ToArray()[2].SubId, Is.EqualTo(subIds[0]));
            Assert.That(boxs.ToArray()[3].SubId, Is.EqualTo(subIds[4]));
            Assert.That(boxs.ToArray()[4].SubId, Is.EqualTo(subIds[2]));
        }
    }
}