string.StartsWith/EndsWith do not respect StringComparison arg.

[jack128]

This bug hits atleast Firebird and Sqlite providers.

		[Test, Combinatorial]
		public void SearchStringTest(
			[IncludeDataSources(TestProvName.AllSQLite, TestProvName.AllFirebird)] string context, 
			[Values(nameof(string.StartsWith), nameof(string.EndsWith))] string methodName,
			[Values]bool ignoreCase)
		{
			var stringComparison = ignoreCase ? StringComparison.OrdinalIgnoreCase : StringComparison.Ordinal;
			Expression<Func<Model.Person, bool>> GetPredicate(string param) => methodName switch
			{
				nameof(string.StartsWith) => (Model.Person p) => p.LastName.StartsWith(param, stringComparison),
				nameof(string.EndsWith) => (Model.Person p) => p.LastName.EndsWith(param, stringComparison),				
				_ => throw new ArgumentException("Unknown method name " + methodName)
			};

			using (var db = GetDataContext(context))
			{
				Assert.IsTrue(db.Person.Any(GetPredicate("Pupkin")));
				Assert.AreEqual(ignoreCase, db.Person.Any(GetPredicate("pupkin")));
			}			
		}

Also there is FB specific issue in FirebirdSqlOptimizer.ConvertSearchStringPredicate. "starting with" is case-sensitive, but linq2db use it in "ignorecase" branch

Environment details

linq2db version: 3.3
Database Server: Firebird

[sdanyliv]

Will prepare fix for that.

[jack128]

I believe the bug is here

ExpressionBuilder.SqlBuilder.cs

ISqlPredicate ConvertPredicate(IBuildContext? context, Expression expression)
{
...
    switch (e.Method.Name)
    {
        case "Contains"   : predicate = CreateStringPredicate(context, e, SqlPredicate.SearchString.SearchKind.Contains);   break;
        case "StartsWith" : predicate = CreateStringPredicate(context, e, SqlPredicate.SearchString.SearchKind.StartsWith); break;
	case "EndsWith"   : predicate = CreateStringPredicate(context, e, SqlPredicate.SearchString.SearchKind.EndsWith);   break;
    }
...
}

ISqlPredicate CreateStringPredicate(IBuildContext? context, MethodCallExpression expression, SqlPredicate.SearchString.SearchKind kind)
{
    var e = expression;
    var o = ConvertToSql(context, e.Object);
    var a = ConvertToSql(context, e.Arguments[0]);

    return new SqlPredicate.SearchString(o, false, a, kind, true); // !!!!!!!!!!! true means ignoreCase. linq2db always ignore case
}

[sdanyliv]

Don't worry I know all places which I have to fix. This is one of them.

[sdanyliv]

"starting with" is case-sensitive

Looking at documentation, it is not. Where is Truth with Firebird?

[jack128]

https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-commons-predstartwith

The STARTING WITH predicate searches for a string or a string-like type that starts with the characters in its value argument. The search is case-sensitive.

[sdanyliv]

What is the better way to do non-case sensitive search?

[jack128]

Well, FB doesnt have any special support for non-case sensitive search. So i'd use lower function.
"lower(Field) starting with @p"