How to avoid updating a column on subsequent Update calls using Dapper.Contrib?

Question

I have a class Media that is mostly 1-to-1 with my DB table Media. I'm updating the table using Dapper.Contrib's Update SqlConnection extension method by passing in a Media object.

One of my the columns of the table (and the corresponding class property) is OwnerID, simply it is the ID of the user who first created this media. It should be written on the first insert (which is also done using Dapper.Contrib's Insert extension method), and then updates shouldn't change it. Is it possible to accomplish this with just Dapper.Contrib? If possible I don't want to read the OwnerID column before doing updates just to ensure the OwnerID object property is the same.

The [Computed] attribute seems to omit this column from both Update and Insert The description here seems to indicate this attribute should only omit writing to the column on updates which gives me hope that I am simply using the library incorrectly.

Answer 1

Unfortunatelly, Dapper.Contrib can't do what you want. The point is that according to sources extension method Insert<T> and Update<T> has completely the same mechanism to collect the fields, that will be affected. Basically it looks like:

// in Insert<T>
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();

...

// in Update<T>
var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties)).ToList();

So these methods always affects the same set of fields. To achieve your target behavior, you need to write your own extension and attribute. There are a lot of code below and I don't think, that it is elegant solution. However you are free to use and rework it as you need. It is a rough synchronous implementation, so consider it just as example, than can help you to develop your own solution. Here is a test example for custom MyUpdate<T> extension method:

[Table("Media")]
public class Media
{
    [Key]
    public long Id { get; set; }

    [NotUpdateable]
    public int OwnerId { get; set; }

    public string Name { get; set; }
}

[Test]
public void DapperContribNotWriteableField()
{
    // Arrange
    var conn = new SqlConnection(
        "Data Source=vrpisilstage.c0hnd1p1buwt.us-east-1.rds.amazonaws.com;Initial Catalog=VRPISIL;User ID=VRPISILSTAGE;Password=ottubansIvCajlokojOt;Connect Timeout=100");
    conn.Open();
    var media = new Media
    {
        OwnerId = 100500,
        Name = "Media"
    };

    // Act
    media.Id = conn.Insert(media);
    media.OwnerId = 500100;
    conn.MyUpdate(media);
    var result = conn.Get<Media>(media.Id);

    // Assert
    Assert.AreEqual(result.OwnerId, 100500);
}

Table in DB:

CREATE TABLE [Media]
(
    [Id]           INT IDENTITY (1, 1) NOT NULL,
    [OwnerId]      BIGINT              NOT NULL,    
    [Name]         VARCHAR(50)         NOT NULL
)

Here is attribute to mark properties, that can't be used in Update queries:

/// <summary>
/// Specifies that this is a not updateable column.
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
public class NotUpdateableAttribute : Attribute
{
}

And here is a little bit reworked extension method, that considers attribute NotUpdatable:

/// <summary>
/// My extensions for Dapper
/// </summary>
public static class TestSqlMapperExtensions
{
    private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
    private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> ExplicitKeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
    private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
    private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> ComputedProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
    private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> UpdatableProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
    private static readonly ConcurrentDictionary<RuntimeTypeHandle, string> GetQueries = new ConcurrentDictionary<RuntimeTypeHandle, string>();
    private static readonly ConcurrentDictionary<RuntimeTypeHandle, string> TypeTableName = new ConcurrentDictionary<RuntimeTypeHandle, string>();

    private static readonly ISqlAdapter DefaultAdapter = new SqlServerAdapter();
    private static readonly Dictionary<string, ISqlAdapter> AdapterDictionary
        = new Dictionary<string, ISqlAdapter>
        {
            ["sqlconnection"] = new SqlServerAdapter(),
            ["sqlceconnection"] = new SqlCeServerAdapter(),
            ["npgsqlconnection"] = new PostgresAdapter(),
            ["sqliteconnection"] = new SQLiteAdapter(),
            ["mysqlconnection"] = new MySqlAdapter(),
            ["fbconnection"] = new FbAdapter()
        };

    private static List<PropertyInfo> ComputedPropertiesCache(Type type)
    {
        if (ComputedProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pi))
        {
            return pi.ToList();
        }

        var computedProperties = TypePropertiesCache(type).Where(p => p.GetCustomAttributes(true).Any(a => a is ComputedAttribute)).ToList();

        ComputedProperties[type.TypeHandle] = computedProperties;
        return computedProperties;
    }

    private static List<PropertyInfo> NotUpdateablePropertiesCache(Type type)
    {
        if (UpdatableProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pi))
        {
            return pi.ToList();
        }

        var notUpdateableProperties = TypePropertiesCache(type).Where(p => p.GetCustomAttributes(true).Any(a => a is NotUpdateableAttribute)).ToList();

        UpdatableProperties[type.TypeHandle] = notUpdateableProperties;
        return notUpdateableProperties;
    }

    private static List<PropertyInfo> ExplicitKeyPropertiesCache(Type type)
    {
        if (ExplicitKeyProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pi))
        {
            return pi.ToList();
        }

        var explicitKeyProperties = TypePropertiesCache(type).Where(p => p.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute)).ToList();

        ExplicitKeyProperties[type.TypeHandle] = explicitKeyProperties;
        return explicitKeyProperties;
    }

    private static List<PropertyInfo> KeyPropertiesCache(Type type)
    {
        if (KeyProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pi))
        {
            return pi.ToList();
        }

        var allProperties = TypePropertiesCache(type);
        var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute)).ToList();

        if (keyProperties.Count == 0)
        {
            var idProp = allProperties.Find(p => string.Equals(p.Name, "id", StringComparison.CurrentCultureIgnoreCase));
            if (idProp != null && !idProp.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute))
            {
                keyProperties.Add(idProp);
            }
        }

        KeyProperties[type.TypeHandle] = keyProperties;
        return keyProperties;
    }

    private static List<PropertyInfo> TypePropertiesCache(Type type)
    {
        if (TypeProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pis))
        {
            return pis.ToList();
        }

        var properties = type.GetProperties().Where(IsWriteable).ToArray();
        TypeProperties[type.TypeHandle] = properties;
        return properties.ToList();
    }

    private static bool IsWriteable(PropertyInfo pi)
    {
        var attributes = pi.GetCustomAttributes(typeof(WriteAttribute), false).AsList();
        if (attributes.Count != 1) return true;

        var writeAttribute = (WriteAttribute)attributes[0];
        return writeAttribute.Write;
    }



    private static string GetTableName(Type type)
    {
        if (TypeTableName.TryGetValue(type.TypeHandle, out string name)) return name;

        if (SqlMapperExtensions.TableNameMapper != null)
        {
            name = SqlMapperExtensions.TableNameMapper(type);
        }
        else
        {
            var info = type;
            //NOTE: This as dynamic trick falls back to handle both our own Table-attribute as well as the one in EntityFramework 
            var tableAttrName =
                info.GetCustomAttribute<TableAttribute>(false)?.Name
                ?? (info.GetCustomAttributes(false).FirstOrDefault(attr => attr.GetType().Name == "TableAttribute") as dynamic)?.Name;

            if (tableAttrName != null)
            {
                name = tableAttrName;
            }
            else
            {
                name = type.Name + "s";
                if (type.IsInterface && name.StartsWith("I"))
                    name = name.Substring(1);
            }
        }

        TypeTableName[type.TypeHandle] = name;
        return name;
    }

    /// <summary>
    /// Updates entity in table "Ts", checks if the entity is modified if the entity is tracked by the Get() extension.
    /// </summary>
    /// <typeparam name="T">Type to be updated</typeparam>
    /// <param name="connection">Open SqlConnection</param>
    /// <param name="entityToUpdate">Entity to be updated</param>
    /// <param name="transaction">The transaction to run under, null (the default) if none</param>
    /// <param name="commandTimeout">Number of seconds before command execution timeout</param>
    /// <returns>true if updated, false if not found or not modified (tracked entities)</returns>
    public static bool MyUpdate<T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
    {
        if (entityToUpdate is Dapper.Contrib.Extensions.SqlMapperExtensions.IProxy proxy && !proxy.IsDirty)
        {
            return false;
        }

        var type = typeof(T);

        if (type.IsArray)
        {
            type = type.GetElementType();
        }
        else if (type.IsGenericType)
        {
            var typeInfo = type.GetTypeInfo();
            bool implementsGenericIEnumerableOrIsGenericIEnumerable =
                typeInfo.ImplementedInterfaces.Any(ti => ti.IsGenericType && ti.GetGenericTypeDefinition() == typeof(IEnumerable<>)) ||
                typeInfo.GetGenericTypeDefinition() == typeof(IEnumerable<>);

            if (implementsGenericIEnumerableOrIsGenericIEnumerable)
            {
                type = type.GetGenericArguments()[0];
            }
        }

        var keyProperties = KeyPropertiesCache(type).ToList();  //added ToList() due to issue #418, must work on a list copy
        var explicitKeyProperties = ExplicitKeyPropertiesCache(type);
        if (keyProperties.Count == 0 && explicitKeyProperties.Count == 0)
            throw new ArgumentException("Entity must have at least one [Key] or [ExplicitKey] property");

        var name = GetTableName(type);

        var sb = new StringBuilder();
        sb.AppendFormat("update {0} set ", name);

        var allProperties = TypePropertiesCache(type);
        keyProperties.AddRange(explicitKeyProperties);
        var computedProperties = ComputedPropertiesCache(type);

        // Exclude not updateable fields
        var notUpdateableProperties = NotUpdateablePropertiesCache(type);
        var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties).Union(notUpdateableProperties)).ToList();

        var adapter = GetFormatter(connection);

        for (var i = 0; i < nonIdProps.Count; i++)
        {
            var property = nonIdProps[i];
            adapter.AppendColumnNameEqualsValue(sb, property.Name);  //fix for issue #336
            if (i < nonIdProps.Count - 1)
                sb.Append(", ");
        }
        sb.Append(" where ");
        for (var i = 0; i < keyProperties.Count; i++)
        {
            var property = keyProperties[i];
            adapter.AppendColumnNameEqualsValue(sb, property.Name);  //fix for issue #336
            if (i < keyProperties.Count - 1)
                sb.Append(" and ");
        }
        var updated = connection.Execute(sb.ToString(), entityToUpdate, commandTimeout: commandTimeout, transaction: transaction);
        return updated > 0;
    }

    private static ISqlAdapter GetFormatter(IDbConnection connection)
    {
        var name = SqlMapperExtensions.GetDatabaseType?.Invoke(connection).ToLower()
                   ?? connection.GetType().Name.ToLower();

        return !AdapterDictionary.ContainsKey(name)
            ? DefaultAdapter
            : AdapterDictionary[name];
    }


}

Hope it helps.