Dapper ORM with dynamic model - How to return no field instead of 'Field' = NULL?

Question

I'm using dapper with dynamic return model objects and I'd like to configure it to 'skip' fields which are null.

For instance instead of :

[0]: {DapperRow, Type = 'PHYS', Street1 = NULL, Street2 = NULL, State = CO}

I'd like Dapper to return

[0]: {DapperRow, Type = 'PHYS', State = CO}

This would be functionally similiar to JSON.Net's 'NullValueHandling setting' functionality.

Is this possible without altering the underlying SQL Query?

The benefit would be decreasing the size of my (eventual) JSON object that is serialized from Dapper's result set and gets returned to an API client.

(This is a different question than this one, as I am using dynamic model classes)

That sounds undesirable; "exists with a null-value" is different to "doesn't exist". Also, the way dapper works with dynamic means that this isn't trivial. — Marc Gravell

Fredrik Ljung Fredrik Ljung · Accepted Answer · 2014-05-22T16:19:34

The underlying type DapperRow implements IDictionary so you can cast to that interface and use the remove function. The following extension method worked fine in my tests:

public static class DapperRowExtensions
{
    public static IEnumerable<dynamic> RemoveNullParams(this IEnumerable<dynamic> rows)
    {
        foreach (var row in rows)
        {
            var item = (IDictionary<string, object>)row;
            foreach (var key in item.Keys.ToList())
            {
                if (item[key] == null)
                    item.Remove(key);
            }
        }
        return rows;
    }
}

You would then use it on the result from the query:

var result = connection.Query("SELECT...");
return result.RemoveNullParams();

Dapper ORM with dynamic model - How to return no field instead of 'Field' = NULL?

1 Answers