EF Core 2 and SQL Server case sensitivity

For our project we use an SQL Server database with a case insensitive collation and Entity Framework 2.2. We got into a problem with case sensitivity, that I want you to be aware of.

To describe the problem, we’ll use two tables and a “join table” to handle a many-to-many relationship between the two tables (a Client can use many Scopes and a Scope can have many Clients). The tables looks like this:

And the entity classes looks like this:

    public class Client
    {
        [StringLength(50)]
        public string ClientId { get; set; }
        public ICollection<ClientScope> ClientScopes { get; set; }
    }

    public class Scope
    {
        public string ScopeId { get; set; }
        public ICollection<ClientScope> ClientScopes { get; set; }
    }

    public class ClientScope
    {
        public string ClientId { get; set; }
        public Client Client { get; set; }
        public string ScopeId { get; set; }
        public Scope Scope { get; set; }
    }

    public class IdsContext : DbContext
    {
        public IdsContext(DbContextOptions<IdsContext> options)
            : base(options)
        {}

        public DbSet<Client> Clients { get; set; }
        public DbSet<Scope> Scopes { get; set; }
        public DbSet<ClientScope> ClientScope { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ClientScope>()
                .HasKey(cs => new { cs.ClientId, cs.ScopeId });

            base.OnModelCreating(modelBuilder);
        }
    }

A Client has a primary key and a list of Scopes, and a Scope has a primary key and a list of Clients. Notice that the primary key on both tables is of type string.

Lets create a client and a scope, and let the client “use” the scope (i.e. we have a join, and get a row in the join table). Notice that the key value for the scope is in lower case, but when we join the scope to the client, we use upper case:

        static void Main(string[] args)
        {
            var scopeEntity = new Scope {ScopeId = "scope1"};
            var clientEntity = new Client
            {
                ClientId = "client1",
                ClientScopes = new List<ClientScope>
                {
                    new ClientScope {ScopeId = "SCOPE1"}
                }
            };

            var options = new DbContextOptionsBuilder<IdsContext>()
                .UseSqlServer(Constants.ConnectionString)
                .Options;
            using (var context = new IdsContext(options))
            {
                context.Scopes.Add(scopeEntity);
                context.Clients.Add(clientEntity);
                context.SaveChanges();
            }
        }

The ClientScopes table now contains one row:

Most things works as expected. When you query the Scopes table, it ignores the case:

// You get the scope
var where = context.Scopes.AsNoTracking()
    .Where(s => s.ScopeId == "ScOpE1")
    .ToList();

// You get the scope
var first = context.Scopes.AsNoTracking()
    .First(s => s.ScopeId == "ScOpE1");

// You get the scope
var contains = context.Scopes.AsNoTracking()
    .First(s => s.ScopeId.Contains("ScOpE1"));

But when you use .Include, you get into problems:

// You get the scope, BUT scope.ClientScopes.Count == 0 (should be one)
var scope = context.Scopes.AsNoTracking()
    .Include(s => s.ClientScopes)
    .First();

In this case, the scope object should have one client in it’s ClientScopes collection, but it’s empty. You have to use the correct case when you create the client:

        static void Main(string[] args)
        {
            var scopeEntity = new Scope {ScopeId = "scope1"};
            var clientEntity = new Client
            {
                ClientId = "client1",
                ClientScopes = new List<ClientScope>
                {
                    new ClientScope {ScopeId = "scope1"}
                }
            };

            var options = new DbContextOptionsBuilder<IdsContext>()
                .UseSqlServer(Constants.ConnectionString)
                .Options;
            using (var context = new IdsContext(options))
            {
                context.Scopes.Add(scopeEntity);
                context.Clients.Add(clientEntity);
                context.SaveChanges();

                // You get the scope, and scope.ClientScopes.Count == 1
                var scope = context.Scopes.AsNoTracking()
                    .Include(s => s.ClientScopes)
                    .First();
            }
        }

So we have to make sure we use the correct case when we are joining!