Unable to order by Guid column

[chris31389]

I'm trying to order by a Guid in EF Core with a relational database and its not ordering. Is there something I'm doing wrong or could this be an issue with EF Core?

  Expected: fa1760e7-27f4-4f8b-9205-44acf2358044
  But was:  c7e76cf2-35d1-4cf8-8a67-83f41842f052

Steps to reproduce

Using NUnit I created the following test

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]));
        }
    }
}

Further technical details

EF Core version: EF core 2.0.0
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10
IDE: Visual Studio 2017

[ajcvickers]

@chris31389 This is likely due to SQLite not having a native GUID type, which means that GUID values are saved, if I remember correctly, as blobs. The sorting is thus done on these blobs, which I am guessing gives a different order to the natural ordering of GUIDs.
/cc @bricelam @divega

[bricelam]

From the docs:

Note that the order of bytes in the returned byte array is different from the string representation of a Guid value. The order of the beginning four-byte group and the next two two-byte groups is reversed, whereas the order of the last two-byte group and the closing six-byte group is the same.

...and the other docs:

When two BLOB values are compared, the result is determined using memcmp().

[chris31389]

So this isn't a bug and actually correct implementation?

Is it correct and intended that using a different database type (e.g. UseInMemoryDatabase(...)) Gives a different result?

[ajcvickers]

@chris31389 Sorting order is one of those things that can be different between the CLR and the database, and between different databases, especially, but not exclusively, when values have different representations. We're currently in the middle of discussions on ways to handle this, but it may be that it is inherently something that can vary between database providers.

[divega]

Reopening so that we can decide on this particular issue as port of the larger discussion (i.e. I am not sure leaving it like it is now is the best outcome).

[chris31389]

@ajcvickers I've just tested this with SqlServer and its producing the same results. Should I raise a new issue?

[ajcvickers]

@chris31389 Thanks for the info. It does indeed seem that the default ordering for GUIDs on .NET is different from that of SQL Server. No need for another issue--I have added a note to #10265 which is the general issue we have for consideration of this issue.