.Net Core - Foreign Key case sensitive issue in Entity Framework Core

Preparation

In some projects, people use string as primary key of the table, this may bring some troubles if they didn’t pay attention to the case of the string. For example, people might add the value with lower case as the Primary key in one table, but uppercase as a foreign key column in another table. Sql Server is case insensitive by default. Let’s take an example to explain this:

-- Category
CREATE TABLE [Category](
	[Code] [nvarchar](10) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)
)
--Product
CREATE TABLE [Product](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [CategoryCode] [nvarchar](10) NOT NULL
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
)

ALTER TABLE [Product]  WITH CHECK ADD  CONSTRAINT [fk_Product_Category] FOREIGN KEY([CategoryCode])
REFERENCES [Category] ([Code])
GO

And we add two records into DB.

--Category
insert into Category values ('ELEC', 'Electronics')
--Product
insert into Product values ('Mobile Phone', 'elec')

The tables will look like:

Category:

Product:

As sql is case insensitive, the following sqls return the same data:

-- Category
select * from Category where Code = 'ELEC'
select * from Category where Code = 'elec'

--Product 
select * from Product where CategoryCode = 'ELEC'
select * from Product where CategoryCode = 'elec'

Entity Framework Core Implementation

Let’s use EntityFrameworkCore to load the data. The entity definition will look like:

public class Product
{
    [Key]
    public int Id { get; set; }
    public string Name {get; set;}

    [ForeignKey("Category")]
    public string CategoryCode {get; set;}
    public virtual Category Category {get;set;}
}

public class Category
{
    [Key]
    public string Code {get; set;}
    public string Name {get; set;}
}

We defined the DbSets in DbContext:

public class MyDbContext : DbContext
{
    public DbSet<Product> Products {get;set;}
    public DbSet<Category> Categories {get;set;}
}

Then let’s select the produt data including the category with the following code:

var product  = dbContext.Products.Include(x => x.Category).SingleOrDefault(x => x.Id == 1);

The result we expected is that the product [Mobile Phone] is loaded with Category property set correctly. But when we examine the object, the Category navigation property is null.

If we inspect the sql generated by entity framework core, we found actually the category is found and returned, but the entity framework didn’t build the relationship between them.

The reason is because the CategoryCode for Product [Mobile Phone] is in lower case(elec), but the Code of Category [Electronics] is in upper case (ELEC). when entity framework core compare the two values, it didn’t treat them as equal.

Solution / Work around

The issue has already been raised in github, but the target fix release version is 3.0.0, which means we have to find a workaround.

There are several ways to fix it:

• Update the databse to make the case match.

• Or tell Entity Framework to do a convert when loading the data. For example, convert the Code of Category to lower case, or convert the CategoryCode of Product to upper case.

If possible, I highly recommend to make a migration to make sure the data store in db having the same case. But if it’s not applicable, we can use the 2 option to do a quick fix in the code level.

The code of conversion in entity framework MyDbContext class is as follow:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>.Property(e => e.CategoryCode)
            .HasConversion(p => p, s => s.ToUpper());
}

After added the above code, entity framework can associate the navigation property correctly.

Technical Details

The versions of related packages are:

Sql Server 2017

EF Core version: 2.1.1

EF Core Provider: Microsoft.EntityFrameworkCore.SqlServer