{"action":"created","issue":{"url":"https://api.github.com/repos/schambers/fluentmigrator/issues/640","labels_url":"https://api.github.com/repos/schambers/fluentmigrator/issues/640/labels{/name}","comments_url":"https://api.github.com/repos/schambers/fluentmigrator/issues/640/comments","events_url":"https://api.github.com/repos/schambers/fluentmigrator/issues/640/events","html_url":"https://github.com/schambers/fluentmigrator/issues/640","id":101859000,"number":640,"title":"AddOrUpdate","user":{"login":"ondravondra","id":1297532,"avatar_url":"https://avatars.githubusercontent.com/u/1297532?v=3","gravatar_id":"","url":"https://api.github.com/users/ondravondra","html_url":"https://github.com/ondravondra","followers_url":"https://api.github.com/users/ondravondra/followers","following_url":"https://api.github.com/users/ondravondra/following{/other_user}","gists_url":"https://api.github.com/users/ondravondra/gists{/gist_id}","starred_url":"https://api.github.com/users/ondravondra/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ondravondra/subscriptions","organizations_url":"https://api.github.com/users/ondravondra/orgs","repos_url":"https://api.github.com/users/ondravondra/repos","events_url":"https://api.github.com/users/ondravondra/events{/privacy}","received_events_url":"https://api.github.com/users/ondravondra/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-08-19T10:35:04Z","updated_at":"2015-09-10T11:30:44Z","closed_at":null,"body":"It would be nice to have feature similar to [EF's AddOrUpdate](https://msdn.microsoft.com/en-us/library/hh846514(v=vs.103).aspx) where you supply a collection of rows/entities and EF checks if these rows exist in the table. If they exist, they are updated, otherwise they are inserted.\r\n\r\nThis method is very useful for updating enumerations, they could be used in a Profile which could be automatically executed after deployment. The upside is that you do not have to create a migration when you just want to, for example, update a typo or add a new enumeration value."},"comment":{"url":"https://api.github.com/repos/schambers/fluentmigrator/issues/comments/139209349","html_url":"https://github.com/schambers/fluentmigrator/issues/640#issuecomment-139209349","issue_url":"https://api.github.com/repos/schambers/fluentmigrator/issues/640","id":139209349,"user":{"login":"ondravondra","id":1297532,"avatar_url":"https://avatars.githubusercontent.com/u/1297532?v=3","gravatar_id":"","url":"https://api.github.com/users/ondravondra","html_url":"https://github.com/ondravondra","followers_url":"https://api.github.com/users/ondravondra/followers","following_url":"https://api.github.com/users/ondravondra/following{/other_user}","gists_url":"https://api.github.com/users/ondravondra/gists{/gist_id}","starred_url":"https://api.github.com/users/ondravondra/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ondravondra/subscriptions","organizations_url":"https://api.github.com/users/ondravondra/orgs","repos_url":"https://api.github.com/users/ondravondra/repos","events_url":"https://api.github.com/users/ondravondra/events{/privacy}","received_events_url":"https://api.github.com/users/ondravondra/received_events","type":"User","site_admin":false},"created_at":"2015-09-10T11:30:44Z","updated_at":"2015-09-10T11:30:44Z","body":"I wrote an extension for our company project. It merges the data comparing set of columns for each row. It reuses existing fluent migrator code.\r\n\r\nSample usage:\r\n```\r\nMerge.IntoTable(\"role\").Row(new\r\n{\r\n  role_id = 1,\r\n  name = \"admin\"\r\n}).Row(new\r\n{\r\n  role_id = 2,\r\n  name = \"manager\"\r\n}).Row(new\r\n{\r\n  role_id = 3,\r\n  name = \"operator\"\r\n}).Match(r => new\r\n{\r\n  r.role_id\r\n});\r\n```\r\n\r\nCode:\r\n```\r\nusing FluentMigrator.Expressions;\r\nusing FluentMigrator.Infrastructure;\r\nusing System;\r\nusing System.Collections.Generic;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing System.Threading.Tasks;\r\nusing FluentMigrator;\r\nusing FluentMigrator.Model;\r\nusing System.Data;\r\nusing System.ComponentModel;\r\n\r\nnamespace MigExt\r\n{\r\n    public abstract class MigrationExt : Migration\r\n    {\r\n        public IMergeExpressionRoot Merge\r\n        {\r\n            get\r\n            {\r\n                return new MergeExpressionRoot((IMigrationContext)GetType().GetField(\"_context\", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(this));\r\n            }\r\n        }\r\n    }\r\n\r\n    public interface IMergeExpressionRoot\r\n    {\r\n        IMergeDataOrInSchemaSyntax IntoTable(string tableName);\r\n    }\r\n\r\n    public class MergeExpressionRoot : IMergeExpressionRoot\r\n    {\r\n        private readonly IMigrationContext _context;\r\n\r\n        public MergeExpressionRoot(IMigrationContext context)\r\n        {\r\n            _context = context;\r\n        }\r\n\r\n        public IMergeDataOrInSchemaSyntax IntoTable(string tableName)\r\n        {\r\n            var expression = new MergeDataExpression { TableName = tableName };\r\n            _context.Expressions.Add(expression);\r\n            return new MergeDataExpressionStartBuilder(expression);\r\n        }\r\n    }\r\n\r\n    public interface IMergeDataSyntax\r\n    {\r\n        IMergeDataOrMatchSyntax<T> Row<T>(T dataAsAnonymousType);\r\n    }\r\n\r\n    public interface IMergeDataOrInSchemaSyntax : IMergeDataSyntax\r\n    {\r\n        IMergeDataSyntax InSchema(string schemaName);\r\n    }\r\n\r\n    public interface IMergeDataOrMatchSyntax<T>\r\n    {\r\n        IMergeDataOrMatchSyntax<T> Row(T dataAsAnonymousType);\r\n        void Match<M>(Func<T, M> f);\r\n    }\r\n\r\n    public abstract class MergeDataExpressionBuilderBase : ISupportAdditionalFeatures\r\n    {\r\n        protected readonly MergeDataExpression _expression;\r\n\r\n        protected MergeDataExpressionBuilderBase(MergeDataExpression expression)\r\n        {\r\n            _expression = expression;\r\n        }\r\n\r\n        void ISupportAdditionalFeatures.AddAdditionalFeature(string feature, object value)\r\n        {\r\n            if (!_expression.AdditionalFeatures.ContainsKey(feature))\r\n            {\r\n                _expression.AdditionalFeatures.Add(feature, value);\r\n            }\r\n            else\r\n            {\r\n                _expression.AdditionalFeatures[feature] = value;\r\n            }\r\n        }\r\n\r\n        protected static IDictionary<string, object> ExtractData(object dataAsAnonymousType)\r\n        {\r\n            var data = new Dictionary<string, object>();\r\n\r\n            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(dataAsAnonymousType);\r\n\r\n            foreach (PropertyDescriptor property in properties)\r\n            {\r\n                data.Add(property.Name, property.GetValue(dataAsAnonymousType));\r\n            }\r\n\r\n            return data;\r\n        }\r\n    }\r\n\r\n    public class MergeDataExpressionStartBuilder : MergeDataExpressionBuilderBase, IMergeDataOrInSchemaSyntax\r\n    {\r\n        public MergeDataExpressionStartBuilder(MergeDataExpression expression) : base(expression)\r\n        {\r\n        }\r\n\r\n        public IMergeDataOrMatchSyntax<T> Row<T>(T dataAsAnonymousType)\r\n        {\r\n            var typed = new MergeDataExpressionTypedBuilder<T>(_expression);\r\n            return typed.Row(dataAsAnonymousType);\r\n        }\r\n\r\n        public IMergeDataSyntax InSchema(string schemaName)\r\n        {\r\n            _expression.SchemaName = schemaName;\r\n            return this;\r\n        }\r\n    }\r\n\r\n    public class MergeDataExpressionTypedBuilder<T> : MergeDataExpressionBuilderBase, IMergeDataOrMatchSyntax<T>\r\n    {\r\n        public MergeDataExpressionTypedBuilder(MergeDataExpression expression) : base(expression)\r\n        {\r\n        }\r\n\r\n        public IMergeDataOrMatchSyntax<T> Row(T dataAsAnonymousType)\r\n        {\r\n            IDictionary<string, object> data = ExtractData(dataAsAnonymousType);\r\n\r\n            var dataDefinition = new InsertionDataDefinition();\r\n\r\n            dataDefinition.AddRange(data);\r\n\r\n            _expression.Rows.Add(dataDefinition);\r\n\r\n            return this;\r\n        }\r\n\r\n        public void Match<M>(Func<T, M> f)\r\n        {\r\n            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(M));\r\n            foreach (PropertyDescriptor property in properties)\r\n            {\r\n                _expression.MatchColumns.Add(property.Name);\r\n            }\r\n        }\r\n    }\r\n\r\n    public class MergeDataExpression : MigrationExpressionBase\r\n    {\r\n        private readonly List<InsertionDataDefinition> _rows = new List<InsertionDataDefinition>();\r\n        public string SchemaName { get; set; }\r\n        public string TableName { get; set; }\r\n        private readonly Dictionary<string, object> _additionalFeatures = new Dictionary<string, object>();\r\n\r\n        private readonly List<string> _matchColumns = new List<string>();\r\n\r\n        public List<InsertionDataDefinition> Rows\r\n        {\r\n            get { return _rows; }\r\n        }\r\n\r\n        public IDictionary<string, object> AdditionalFeatures\r\n        {\r\n            get { return _additionalFeatures; }\r\n        }\r\n\r\n        public List<string> MatchColumns\r\n        {\r\n            get { return _matchColumns; }\r\n        }\r\n\r\n        public override void CollectValidationErrors(ICollection<string> errors)\r\n        {\r\n        }\r\n\r\n        public override void ExecuteWith(IMigrationProcessor processor)\r\n        {\r\n            var existingDataSet = processor.ReadTableData(SchemaName, TableName);\r\n            var existingTable = existingDataSet.Tables[0];\r\n\r\n            foreach (var row in _rows)\r\n            {\r\n                var exists = existingTable.Rows.OfType<DataRow>().Any(r =>\r\n                {\r\n                    return _matchColumns.Select(mc =>\r\n                    {\r\n                        var ex = r[mc];\r\n                        var nw = row.Where(p => p.Key == mc).Select(p => p.Value).SingleOrDefault();\r\n                        if (ex == null || nw == null)\r\n                        {\r\n                            return ex == nw;\r\n                        }\r\n                        return ex.Equals(nw);\r\n                    }).All(m => m);\r\n                });\r\n\r\n                if (exists)\r\n                {\r\n                    ExecuteUpdateWith(processor, row);\r\n                }\r\n                else\r\n                {\r\n                    ExecuteInsertWith(processor, row);\r\n                }\r\n            }\r\n        }\r\n\r\n        private void ExecuteUpdateWith(IMigrationProcessor processor, List<KeyValuePair<string, object>> row)\r\n        {\r\n            var update = new UpdateDataExpression\r\n            {\r\n                SchemaName = SchemaName,\r\n                TableName = TableName,\r\n                IsAllRows = false,\r\n                Set = row.Where(p => !_matchColumns.Contains(p.Key)).ToList(),\r\n                Where = _matchColumns.Select(mc =>\r\n                {\r\n                    var v = row.Where(p => p.Key == mc).Select(p => p.Value).SingleOrDefault();\r\n                    return new KeyValuePair<string, object>(mc, v);\r\n                }).ToList()\r\n            };\r\n\r\n            processor.Process(update);\r\n        }\r\n\r\n        private void ExecuteInsertWith(IMigrationProcessor processor, InsertionDataDefinition row)\r\n        {\r\n            var insert = new InsertDataExpression\r\n            {\r\n                SchemaName = SchemaName,\r\n                TableName = TableName\r\n            };\r\n\r\n            foreach (var af in _additionalFeatures)\r\n            {\r\n                insert.AdditionalFeatures.Add(af.Key, af.Value);\r\n            }\r\n\r\n            insert.Rows.Add(row);\r\n\r\n            processor.Process(insert);\r\n        }\r\n    }\r\n}\r\n```"}}