Issue
You make a query on a rdbms database that compares strings with case INSENSITIVE rules instead binary comparison (like case insensitive + accent sensitive of Microsoft SQL Server).
Suppose that you have this table (MYDATA)
| MYCODE | DESCRIPTION |
|---|---|
| A | A-DESCRIPTION |
| B | B-DESCRIPTION |
| C | C-DESCRIPTION |
| D | D-DESCRIPTION |
| E | E-DESCRIPTION |
Suppose that you have to query this table based on website query string params, something like this:
https://myexample.com/favourites?first=a&second=b
If you use old-fashion style you can query database directly using SQL query like this:
DECLARE @first NVARCHAR(MAX);
DECLARE @second NVARCHAR(MAX);
SET @first= 'a';
SET @second= 'b';
SELECT * FROM MYDATA WHERE MYCODE = @first OR MYCODE = @second;
GO
If you are using Microsoft SQL Server with collation sequence CASE INSENSITIVE and ACCENT SENSITIVE you will have 2 rows
| CODE | DESCRIPTION |
|---|---|
| A | A-DESCRIPTION |
| B | B-DESCRIPTION |
You will have row with code 'A' and row with code 'B' even you were looking for 'a' and 'b' (case insensitive).
If you are using ORM you can query database using expression or linq like this:
IEnumerable<MyEntity> selectedData = dataRepo.Where(myEntity => myEntity.Code == first || myEntity.Code == second).ToList();
Above query returns 2 items (2 items of MyEntity).
Next in your code you need to find separately first and second.
(...remember that first value is 'a' and second value is 'b'.)
You can use this in memory expression:
MyEntity firstData = selectedData.FirstOrDefault(myEntity => myEntity.Code == first);
or this linq:
MyEntity firstData = (from items in selectedData
where items.Code == first
orderby items.Code
select items).FirstOrDefault();
Both solutions return NULL !!!
You have used same variables 'first' or 'second', but RDBMS Microsoft SQL Server works in case insensitive and in memory comparison is done using binary comparison.
Common solution is to use .ToUpper() on both string in every memory comparison. This solution works but: is legacy not flexible and you need to remember this rule.
More flexible and dynamic solution is DevExtremeToys package.
With this package you can add this using:
using DevExtremeToys.StringComparer;
Now you can configure the string comparison behavior in this way:
CompareSettings.Instance.GetSetting = () =>
{
return new Settings()
{
CaseOption = CaseOptions.Insensitive,
AccentOption = AccentOptions.Sensitive
};
};
Then you can use new string extensions like this:
firstData = selectedData.FirstOrDefault(myEntity => myEntity.Code.EqualsDevEx(first));
or this:
firstData = (from items in selectedData
where items.Code.EqualsDevEx(first)
orderby items.Code
select items).FirstOrDefault();
With this solution you will have the same behavior between memory and database.
You can configure string comparison rules once, like the previous example, or specifying compare rules in each comparison using overload with compare settings parameter.
You can see more instructions and source code in github repository.
Enjoy
