Execute search QueryOptions.ForceIgnoreCase is not working

[jasonlaw]

Hi,

I expect the search in the below code would ignore case, but it is not. Bug?

public static SearchResults<ICommunity> SearchCommunities( this IEntitySession session, CommunityQueryParams searchParams)
        {
            var where = session.NewPredicate<ICommunity>()
                          .AndIfNotEmpty(searchParams.Id, x => x.Id.StartsWith(searchParams.Id))
                          .AndIfNotEmpty(searchParams.Name, x => x.Name.StartsWith(searchParams.Name));

            if ( !string.IsNullOrEmpty(searchParams.MemberId))
            {
                var qMemberCommnunityIds = session.EntitySet<IMemberInCommunity>()
                                            .Where(x => x.Member.User.Id == searchParams.MemberId)
                                            .Select(x => x.Community.Id);

                where = where.And(x => qMemberCommnunityIds.Contains(x.Id));
            }

            var results = session.ExecuteSearch(where, searchParams, options: QueryOptions.ForceIgnoreCase);
            return results;
        }

[rivantsov]

what server? ms sql?

[jasonlaw]

MySQL

[rivantsov]

For MySQL, forceIgnoreCase does nothing as by default LIKE (which is used here) is case insensitive by default, according to this: https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html unless you have table/column definition as binary or with case sensitive collation. So first, get printout of the SQL that was executed, make sure it is LIKE there; then check the collation settings on database, table and column

[jasonlaw]

Thanks for the clarification. My database indeed has the collation utf8mb4_bin which is case sensitive. Just managed to convert it to utf8mb4_unicode_ci, and it is working fine now. Thank you very much!