1

I'm trying to do a case insenstive match against a MySQL table while using Dapper Extensions, but I'm getting a null exception on the field name when trying to make it UpperCase within the linq expression:

Here's the code:

            var predicateGroup = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };

            var term = "term";
            if (term.IsNotNullOrEmpty())
            {
                predicateGroup.Predicates.Add(Predicates.Field<Company>(p => p.company_code, Operator.Like, string.Format("%{0}%", term)));
                predicateGroup.Predicates.Add(Predicates.Field<Company>(p => p.company_name.ToUpper(), Operator.Like, string.Format("%{0}%", term)));
            }

            return Count<Company>(predicateGroup, TdsAuthConnectionString);

Does anybody know how I can use DapperExtensions predicates to do a case insensitive match?

Thank you for any help, codenewbie

G Davison
  • 1,079
  • 1
  • 14
  • 21
codenewbie
  • 181
  • 16

1 Answers1

0

The p => p.company_name snippet is a Linq Lambda Expression<Func<T, object>> expression telling DapperExtensions what property name to use within its internal mapping. When you use uppercase in there, it's not pointing to a valid property name, it's pointing to the upper case function instead.

If you're looking for a data case insensitivity, this is a feature of your database engine. If you're using Microsoft SQL Server, data case sensitivity is controlled by the collation of your database. If you're using a case-insensitive code page such as Latin1_General_CI_AS then all queries you send to the database are case-insensitive. You can check what the server collation is by running the following SQL statement:

SELECT SERVERPROPERTY ('Collation')

You can find a list of collations and their case sensitivities here

G Davison
  • 1,079
  • 1
  • 14
  • 21
  • Thank you. We use mysql. I will look into the settings, but don't know if it's anything I can change. I'll report back. – codenewbie Sep 16 '16 at 20:53
  • Sadly, I cannot make any changes to the db. Is there any way within the predicate or elsewhere that I can 'suggest' that the query uses 'UPPER(company_code)'? – codenewbie Oct 20 '16 at 14:54
  • I've had a look through the IFieldPredicate code within the DapperExtensions codebase @ https://github.com/tmsmith/Dapper-Extensions/blob/master/DapperExtensions/Predicates.cs and I can't see how it could be done without implementing a whole new predicate type. You could switch back to Dapper for that particular query instead. You can freely change between Dapper and DapperExtensions (and DapperContrib etc) within your app if one library doesn't meet your needs. – G Davison Oct 20 '16 at 16:48