staticlibs
commented
2 months ago @karthika-thirumoorthy
Collations are used in DB to define the rules to use when unicode strings comparison is done. Deterministic collation will consider two characters as equal if, and only if, they have the same byte sequence. So all deterministic collations must be case-sensitive (cs) and accent-sensitive (as). Postgres doesn’t support the LIKE clause on non-deterministic collations at all. Babelfish supports it for case-insensitive ci_as collations (doesn’t support on accent-insensitive ai collations).
By default Babelfish uses bbf_unicode_general_ci_as collation - case-insensitive UTF-8. I suggest to use this collation for all columns unless there are specific collation requirements for a particular column (need to be handled case-by-case).
If an ai collation is used in some column with EF like this:
create table tab1 (
id int,
col1 nvarchar(max) collate bbf_unicode_cp1_ci_ai
)public class Tab1
{
public int Id { get; set; }
public string Col1 { get; set; }
}Then this column cannot be used with EF queries that use LIKE under the hood, for example:
var rec1 = db.Tab1
.Where(r => r.Col1.StartsWith("foo"))
.OrderBy(r => r.Id)
.First();is translated to:
SELECT TOP(1) [t].[Id], [t].[Col1]
FROM [Tab1] AS [t]
WHERE [t].[Col1] LIKE N'foo%'
ORDER BY [t].[Id]and will fail on Babelfish with:
An unhandled exception of type 'Microsoft.Data.SqlClient.SqlException' occurred in Microsoft.EntityFrameworkCore.Relational.dll: 'nondeterministic collations are not supported for LIKE'
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
[...]To avoid this problem you need to either change the column collation to as one (bbf_unicode_general_ci_as in this example) or to specify the collation name explicitly in LINQ query like this:
var rec1 = db.Tab1
.Where(r => EF.Functions.Collate(r.Col1, "bbf_unicode_cp1_ci_as")
.StartsWith("foo"))
.OrderBy(r => r.Id)
.First();Collations used for the particular table can be viewed with this query:
select name, collation_name
from sys.columns
where [object_id] = object_id('tab1')All collation names supported by Babelfish can be listed with this query:
select name, description from sys.fn_helpcollations()
karthika-thirumoorthy
psrinivasa2
JayakumarEMIS
Hi @bill-ramos-rmoswi ,
we are trying to insert a value to employee table from entity framework but it throws an exception "nondeterministic collations are not supported for LIKE"
when we are trying to insert the value in SSMS it is inserted successfully.
INSERT INTO [dbo].[Employee] ([EmployeeGuid] ,[EmployeeId] ,[GivenName] ,[Surname] ,[MURAccredited] ,[NMSAccredited] ,[Deleted] ,[ServerSide] ,[DateAdded] ,[DateModified] ,[UserInRoleId]) VALUES (<EmployeeGuid, uniqueidentifier,> ,<EmployeeId, int,> ,<GivenName, varchar(35),> ,<Surname, varchar(100),> ,<MURAccredited, bit,> ,<NMSAccredited, bit,> ,<Deleted, bit,> ,<ServerSide, bit,> ,<DateAdded, datetime,> ,<DateModified, datetime,> ,<UserInRoleId, int,>) GO