.Net Dapper Bulk update (Query Generation)

Here is the example of how to create a generic method to update the different properties of same type of entities. E.g. you have User table with email, phone. And you want to update 10 records email and 5 records phone in a single DB roundtrip. By using the following method, you can do that easily.

        /// <summary>
        /// Chunking or merging could be done, to overcome the size of query
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public async Task UpdateEntitiesAsync<T, U>(List<(T entity, U id, List<string> propsToUpdate)> entities)
        {
            if (entities.Count == 0)
            {
                return;
            }

            var query = new StringBuilder();
            var attr = typeof(T).GetCustomAttributes(false);
            var tableAttr = attr.FirstOrDefault(f => f is TableAttribute);
            if (tableAttr == null)
            {
                throw new Exception("Table attribute must be applied");
            }
            var tableAttrCasted = (TableAttribute)tableAttr;
            var queryPrefix = $"Update {tableAttrCasted.Schema}.{tableAttrCasted.Name} Set ";
            var queryPostfix = $" Where Id = ";

            var entityIndex = 0;
            var parameters = new Dictionary<string, object?>();
            foreach (var (entity, id, propsToUpdate) in entities )
            {
                if (entity == null || propsToUpdate.Count == 0)
                {
                    continue;
                }
                query.Append(queryPrefix);
                var type = entity.GetType();
                var propIndex = 0;
                foreach (var prop in propsToUpdate)
                {
                    var propInfo = type.GetProperty(prop);
                    var propValue = propInfo.GetValue(entity);
                    query.Append($"{(propIndex > 0 ? "," : string.Empty)} {prop} = @{prop}{entityIndex}");
                    parameters.Add($"@{prop}{entityIndex}", propValue);
                    propIndex++;
                }
                query.Append($"{queryPostfix} @entityId{entityIndex}; \n\n");
                parameters.Add($"@entityId{entityIndex}", id);
                entityIndex++;
            }
            using IDbConnection dbConnection = new SqlConnection(_connectionStringsOptions.Value.SQLConnection);
            await dbConnection.ExecuteAsync(query.ToString(), new DynamicParameters(parameters));
        }

Improvements could be made in above code. E.g. you may not have schema, so use the default schema (dbo). Using merge for large datasets.

Cheers and Peace Out!!!