How to get Dapper dynamic as case insensitive?

Question

I'm using Dapper with Oracle. Oracle query results columns are case insensitive (unless you double-quote them), and Oracle returns the column names in uppercase. And so when using dynamic, Dapper requires using uppercase fieldnames.

Is there a way to make Dapper not care about case when using Dynamic? I see in DapperTable http://dapper-dot-net/Dapper/SqlMapper.DataTable.cs that it uses a Dictionary with StringComparer.Ordinal instead of StringComparer.OrdinalIgnoreCase, and I don't see any options to change it. But I am hoping somebody has a solution for this, or can tell me how to do it in a better way.

Example:

var r = cnn.Query("select 'apple' fruit from dual").FirstOrDefault();
var f = r.Fruit; // f is set to null, but I want it to be "apple"
var F = r.FRUIT; // F is set to "apple", but I don't want to reference using all caps.

I would need to add a setting for this; the problem is that other ado.net providers get it "right", and allow names distinguished by case alone — Marc Gravell, Jun 24 '16 at 06:53
@MarcGravell - I saw something along the lines that Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true; would make it case insensitive, but if it does, I think that is only for property matching. It would be very helpful to have an option to make this work out of the box with Dapper. — Bob Thule, Jun 24 '16 at 14:37

score 1 · Accepted Answer · edited May 23 '17 at 12:32

Edit:

See this answer for maybe an alternative way to enumerate through the properties to do something similar to what I have below (you might be able to cast the dynamic as an IDictionary<string,object>).

Also (not familiar enough with Oracle to know if this is true, or not) but maybe if you aliased the column name in the SELECT query (i.e. using AS in T-SQL) that would override the casing?

Original:

(Marc Gravell said the following wouldn't work with Dapper.)

Does adapting the idea from this answer work (untested with Dapper, specifically)?

using NUnit.Framework;
using System;
using System.Collections.Generic;

namespace StackOverflowSandbox
{
    public class ToDictionaryTests
    {
        [Test]
        public void ItShouldWork()
        {
            // Arrange
            var dapperResult = new
            {
                UPPER = 1,
                lower = 2
            };

            // Act
            var dictionary = dapperResult.ConvertToDictionary();

            // Assert
            Assert.That(dictionary["Upper"], Is.EqualTo(1));
            Assert.That(dictionary["Lower"], Is.EqualTo(2));
        }
    }

    public static class ObjectExtensions
    {
        private static readonly StringComparer ToDictionaryDefaultComparer =
            StringComparer.OrdinalIgnoreCase;

        /// <summary>
        /// Converts an object's properties that can be read
        /// to an IDictionary.
        /// </summary>
        public static IDictionary<string, object> ConvertToDictionary(
            this object @this,
            StringComparer comparer = null)
        {
            // The following is adapted from: 
            // https://stackoverflow.com/a/15698713/569302
            var dictionary = new Dictionary<string, object>(
                comparer ?? ToDictionaryDefaultComparer);
            foreach(var propertyInfo in @this.GetType().GetProperties())
            {
                if (propertyInfo.CanRead && 
                    propertyInfo.GetIndexParameters().Length == 0)
                {
                    dictionary[propertyInfo.Name] = 
                        propertyInfo.GetValue(@this, null);
                }
            }

            return dictionary;
        }   
    }
}

That won't work; the object from Query is `dynamic` in the true sense - asking reflection for rhe properties is meaningless — Marc Gravell, Jun 24 '16 at 06:51
Hi Words Like Jared, to do case sensitive with Oracle, you would do like: select 1 as "QuotesMakeItCaseSensitive" from dual. It's just that then I have to escape the double quotes, and the sql starts looking rough, and it's no longer lightweight. I do think I could create an extension method on IDictionary that returns a case-insensitive IDictionary. That seems likely the best solution without a change in Dapper itself. — Bob Thule, Jun 24 '16 at 14:29

nullPainter · Answer 2 · 2022-01-31T21:12:50.940

Given that the Query result can be cast to an IDictionary<string, object>, all that's required is a cast of each dynamic result, then a call to a method such as this:

public static IDictionary<string, T> ToCaseInsensitiveDictionary<T>(this IDictionary<string, T> source)
{
    var target = new Dictionary<string, T>(StringComparer.OrdinalIgnoreCase);
  
    foreach (var entry in source)
    {
        target[entry.Key] = entry.Value;
    }

    return target;
}

Sample usage:

var results = connection.Query(sql);

foreach (IDictionary<string, object> rawResult in results)
{
    var result = rawResult.ToCaseInsensitiveDictionary();
    var column = result["MY_COLUMN"]; // or result["My_Column"] etc.
}

How to get Dapper dynamic as case insensitive?

2 Answers2