Collation / case sensitivity / primary key

Jun 3, 2013 at 4:45 PM
Edited Jun 3, 2013 at 5:37 PM
(warning, this post is misleading and not entirely true due to my mistake during testing)


I'm currently struggling with a following issue. I have set one field of type string in my entity class as primary key:
[Field(IsPrimaryKey = true)]
Then, when doing:
everything works as expected, unless I insert entities into the store that vary only by letter size in primary key. Inserts work (what is strange they work even for exact duplicates of primary key). Selects as above do work too, but they ignore case sensitivity and return only the entity with upper case key. There's no way I could retrieve the other entity which has the same primary key, only in lower case letters.

I thought it may have something to do with collation settings and I'm researching that. Any advice?
Jun 3, 2013 at 4:47 PM
What platform are you running on? Which Store implementation?
Jun 3, 2013 at 4:50 PM
Edited Jun 3, 2013 at 4:51 PM
Sorry, SQL CE.

Emulator for Windows CE, x86,
Jun 3, 2013 at 5:00 PM
So this is part of the risk of using a string as a primary key (which is generally not a great idea in any case). What's happening is this:

When you insert two rows that vary only by case, the PK constraint says "they are not identical, so that's OK by me" and the insert goes through. But when you select back out, the text search is case insensitive, meaning that from a SELECT perspective "Foo" and "fOO" are equal, so all matching rows are returned.

ORM assumes that when doing a search by primary key that only one value is ever going to be returned (it doesn't use SQL, it uses a TableDirect seek to the first matching value, but the premise is the same) so it gives you the first result it finds. Since the key values are, from its perspective, identical, it can't differentiate them.

The best solutions would be to either not use a string as a PK, or add logic to your app to ensure case-insensitive uniqueness. I'll give some thought as to how one might avoid it at the ORM level, but it's not readily apparent to me how we'd avoid it without a special test for string primary key cases.
Jun 3, 2013 at 5:08 PM
Thanks. Would it be possible to do a case sensitive text search?

For now I guess I could do a FilterCondition select and loop through the (very few) results in my app logic? Will it use the primary key index?
Jun 3, 2013 at 5:11 PM
The underlying DB is case insensitive, so you'd have to bring back the results and then use something like a LINQ query to pull the exact match. Shouldn't add much overhead. Yes, it will use the existing PK index when it does the initial search (or at least it should - if it doesn't let me know).
Jun 3, 2013 at 5:36 PM
OK, my bad. Turns out I had a clever Insert OR Update logic in my persistence layer... hence those strange apparent "inserts" were really updates. Inserting "Foo" and "fOO" as primary key is not possible (SQLCeException).

At least I see logic in that now.

I think if I could set the collation of the DB to a case-sensitive one it could work.
Jun 3, 2013 at 5:40 PM
Collation/case sensitivity wasn't supported in SQL CE until version 4.0 (meaning it's not supported on Windows CE).