CODEWITHSUNDEEP
c#sqliteentity-framework-core
Anup Sharma
Anup Sharma

Reputation: 2083

Sqlite with EntityFrameworkCore unique column is case sensitive. Search is also case sensitive

I have a property Name in entity UnitType set to Unique for SQLite using EntityFrameworkCore 2.0.

modelBuilder.Entity<UnitType>().HasIndex(t => t.Name).IsUnique();

But it behaves as case sensitive. Means it considers Gram and gram as two different values and does insert them. This is clearly not what to expect after spending much of my time on MS-SQL.

Also, another problem is when filtering data on this column. Even that is case sensitive.

db.Units.Where(w => w.Name.Contains(SearchText));

How to make it case insensitive?

Upvotes: 1

Views: 2334

Answers (1)

Anup Sharma
Anup Sharma

Reputation: 2083

The default behavior of SQLite is case sensitive.

To make a column as Case-Insensitive, COLLATE NOCASE needs to be used.

So we need to add this line as well.

modelBuilder.Entity<UnitType>().Property(t => t.Name).HasColumnType("TEXT COLLATE NOCASE");

This will make sure that Gram and gram are understood as same values and hence unique constraint will restrict insertion of one if other is already present.

To filter there are two methods.

1) Convert both Column value and search text to either lower case or upper case.

db.Units.Where(w => w.Name.ToLowerInvariant().Contains(SearchText.ToLowerInvariant()));

2) Use Like keyword with wildcard characters instead of Contain

db.Units.Where(w => EF.Functions.Like(w.Name, $"%{SearchText}%"));

Also, I have read somewhere that converting the column to lower or upper will cause the database engine to convert all values of that column, which may be performance bottleneck on a large dataset. So I recommend using second method.

Upvotes: 7

Related Questions