Efcore: InMemory: Enable case sensitivity/insensitivity setting

[ctolkien]

When querying InMemory database, string comparisons are case specific whereas the default collation of SqlServer is case-insensitive.

Would like to see an option added to the InMemoryDbContextOptionsBuilder object to specify this behaviour so that we can more closely align with SqlServer.

[rowanmiller]

Case sensitivity in SQL Server is actually controlled by the collation settings in the database. We can see the value in having similar settings in the InMemory database so that you can adjust it to align with whatever database you are targeting.

[kirschem]

I'd like to tackle this one as my first issue in ef core. Should I branch from master?

[smitpatel]

@kirschem - Yes, please branch of master.

[xaviergxf]

How will EF.Functions.Collate work with in memory?

[roji]

@xaviergxf EF.Functions.Collate is a relational concept only. The way databases implement collations is quite different from anything we could do in InMemory, at least not without considerable work. For example, SQL allows collations to be applied to arbitrary (textual) expressions, and also supports assigning collations to the columns themselves; that's not something that's immediately compatible with how .NET works.

[xaviergxf]

This means an exception will be thrown? And for testing i will need to mock the this...

[roji]

As very frequently with InMemory, relational features simply aren't supported, making it of limited use for full-fledged testing. In that sense, collations aren't very different from transactions or raw SQL queries. See https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing for more details on this.