fluentmigrator icon indicating copy to clipboard operation
fluentmigrator copied to clipboard

Add Merge syntax root, similar to EF6 AddOrUpdate

Open ondravondra opened this issue 10 years ago • 9 comments

It would be nice to have feature similar to EF's AddOrUpdate 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.

This 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.

Here is an example of how it could be implemented:

using FluentMigrator.Expressions;
using FluentMigrator.Infrastructure;
using System;
using System.Collections.Generic;
using System.Linq;
using FluentMigrator;
using FluentMigrator.Model;
using System.Data;
using System.ComponentModel;

namespace MigExt
{
    public abstract class MigrationExt : Migration
    {
        public IMergeExpressionRoot Merge
        {
            get
            {
                return new MergeExpressionRoot((IMigrationContext)GetType().GetField("_context", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(this));
            }
        }

        public override void Down()
        {
        }
    }

    public interface IMergeExpressionRoot
    {
        IMergeDataOrInSchemaSyntax IntoTable(string tableName);
    }

    public class MergeExpressionRoot : IMergeExpressionRoot
    {
        private readonly IMigrationContext _context;

        public MergeExpressionRoot(IMigrationContext context)
        {
            _context = context;
        }

        public IMergeDataOrInSchemaSyntax IntoTable(string tableName)
        {
            var expression = new MergeDataExpression { TableName = tableName };
            _context.Expressions.Add(expression);
            return new MergeDataExpressionStartBuilder(expression);
        }
    }

    public interface IMergeDataSyntax
    {
        IMergeDataOrMatchSyntax<T> Row<T>(T dataAsAnonymousType);
    }

    public interface IMergeDataOrInSchemaSyntax : IMergeDataSyntax
    {
        IMergeDataSyntax InSchema(string schemaName);
    }

    public interface IMergeDataOrMatchSyntax<T>
    {
        IMergeDataOrMatchSyntax<T> Row(T dataAsAnonymousType);
        void Match<M>(Func<T, M> f);
    }

    public abstract class MergeDataExpressionBuilderBase : ISupportAdditionalFeatures
    {
        protected readonly MergeDataExpression _expression;

        public IDictionary<string, object> AdditionalFeatures => _expression.AdditionalFeatures;

        protected MergeDataExpressionBuilderBase(MergeDataExpression expression)
        {
            _expression = expression;
        }

        protected static IDictionary<string, object> ExtractData(object dataAsAnonymousType)
        {
            var data = new Dictionary<string, object>();

            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(dataAsAnonymousType);

            foreach (PropertyDescriptor property in properties)
            {
                data.Add(property.Name, property.GetValue(dataAsAnonymousType));
            }

            return data;
        }
    }

    public class MergeDataExpressionStartBuilder : MergeDataExpressionBuilderBase, IMergeDataOrInSchemaSyntax
    {
        public MergeDataExpressionStartBuilder(MergeDataExpression expression) : base(expression)
        {
        }

        public IMergeDataOrMatchSyntax<T> Row<T>(T dataAsAnonymousType)
        {
            var typed = new MergeDataExpressionTypedBuilder<T>(_expression);
            return typed.Row(dataAsAnonymousType);
        }

        public IMergeDataSyntax InSchema(string schemaName)
        {
            _expression.SchemaName = schemaName;
            return this;
        }
    }

    public class MergeDataExpressionTypedBuilder<T> : MergeDataExpressionBuilderBase, IMergeDataOrMatchSyntax<T>
    {
        public MergeDataExpressionTypedBuilder(MergeDataExpression expression) : base(expression)
        {
        }

        public IMergeDataOrMatchSyntax<T> Row(T dataAsAnonymousType)
        {
            IDictionary<string, object> data = ExtractData(dataAsAnonymousType);

            var dataDefinition = new InsertionDataDefinition();

            dataDefinition.AddRange(data);

            _expression.Rows.Add(dataDefinition);

            return this;
        }

        public void Match<M>(Func<T, M> f)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(M));
            foreach (PropertyDescriptor property in properties)
            {
                _expression.MatchColumns.Add(property.Name);
            }
        }
    }

    public class MergeDataExpression : MigrationExpressionBase
    {
        private readonly List<InsertionDataDefinition> _rows = new List<InsertionDataDefinition>();
        public string SchemaName { get; set; }
        public string TableName { get; set; }
        private readonly Dictionary<string, object> _additionalFeatures = new Dictionary<string, object>();

        private readonly List<string> _matchColumns = new List<string>();

        public List<InsertionDataDefinition> Rows
        {
            get { return _rows; }
        }

        public IDictionary<string, object> AdditionalFeatures
        {
            get { return _additionalFeatures; }
        }

        public List<string> MatchColumns
        {
            get { return _matchColumns; }
        }

        public override void ExecuteWith(IMigrationProcessor processor)
        {
            var existingDataSet = processor.ReadTableData(SchemaName, TableName);
            var existingTable = existingDataSet.Tables[0];

            foreach (var row in _rows)
            {
                var exists = existingTable.Rows.OfType<DataRow>().Any(r =>
                {
                    return _matchColumns.Select(mc =>
                    {
                        var ex = r[mc];
                        var nw = row.Where(p => p.Key == mc).Select(p => p.Value).SingleOrDefault();
                        if (ex == null || nw == null)
                        {
                            return ex == nw;
                        }
                        return ex.Equals(nw);
                    }).All(m => m);
                });

                if (exists)
                {
                    ExecuteUpdateWith(processor, row);
                }
                else
                {
                    ExecuteInsertWith(processor, row);
                }
            }
        }

        private void ExecuteUpdateWith(IMigrationProcessor processor, List<KeyValuePair<string, object>> row)
        {
            var update = new UpdateDataExpression
            {
                SchemaName = SchemaName,
                TableName = TableName,
                IsAllRows = false,
                Set = row.Where(p => !_matchColumns.Contains(p.Key)).ToList(),
                Where = _matchColumns.Select(mc =>
                {
                    var v = row.Where(p => p.Key == mc).Select(p => p.Value).SingleOrDefault();
                    return new KeyValuePair<string, object>(mc, v);
                }).ToList()
            };

            processor.Process(update);
        }

        private void ExecuteInsertWith(IMigrationProcessor processor, InsertionDataDefinition row)
        {
            var insert = new InsertDataExpression
            {
                SchemaName = SchemaName,
                TableName = TableName
            };

            foreach (var af in _additionalFeatures)
            {
                insert.AdditionalFeatures.Add(af.Key, af.Value);
            }

            insert.Rows.Add(row);

            processor.Process(insert);
        }
    }
} 

ondravondra avatar Aug 19 '15 10:08 ondravondra

@ondravondra And how would that exactly work, as fluentmigrator does not keep any metadata of the schema between migrations. So it does not know which would be the primary key etc

Basicly on a datalevel you are talking about an upsert, see https://wiki.postgresql.org/wiki/UPSERT

tommarien avatar Sep 07 '15 08:09 tommarien

The primary key could be identified by anonymous type or lambda expression, EF uses Expression<Func<TEntity, Object>> identifierExpression. It could be upsert, but many DBs do not support it and it really does not have to be atomic operation as it is a migration performed typically during maintenance hours. So it can also be implemented by simple select, update, insert sequence.

ondravondra avatar Sep 08 '15 08:09 ondravondra

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.

Sample usage:

Merge.IntoTable("role").Row(new
{
  role_id = 1,
  name = "admin"
}).Row(new
{
  role_id = 2,
  name = "manager"
}).Row(new
{
  role_id = 3,
  name = "operator"
}).Match(r => new
{
  r.role_id
});

Code:

using FluentMigrator.Expressions;
using FluentMigrator.Infrastructure;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FluentMigrator;
using FluentMigrator.Model;
using System.Data;
using System.ComponentModel;

namespace MigExt
{
    public abstract class MigrationExt : Migration
    {
        public IMergeExpressionRoot Merge
        {
            get
            {
                return new MergeExpressionRoot((IMigrationContext)GetType().GetField("_context", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(this));
            }
        }
    }

    public interface IMergeExpressionRoot
    {
        IMergeDataOrInSchemaSyntax IntoTable(string tableName);
    }

    public class MergeExpressionRoot : IMergeExpressionRoot
    {
        private readonly IMigrationContext _context;

        public MergeExpressionRoot(IMigrationContext context)
        {
            _context = context;
        }

        public IMergeDataOrInSchemaSyntax IntoTable(string tableName)
        {
            var expression = new MergeDataExpression { TableName = tableName };
            _context.Expressions.Add(expression);
            return new MergeDataExpressionStartBuilder(expression);
        }
    }

    public interface IMergeDataSyntax
    {
        IMergeDataOrMatchSyntax<T> Row<T>(T dataAsAnonymousType);
    }

    public interface IMergeDataOrInSchemaSyntax : IMergeDataSyntax
    {
        IMergeDataSyntax InSchema(string schemaName);
    }

    public interface IMergeDataOrMatchSyntax<T>
    {
        IMergeDataOrMatchSyntax<T> Row(T dataAsAnonymousType);
        void Match<M>(Func<T, M> f);
    }

    public abstract class MergeDataExpressionBuilderBase : ISupportAdditionalFeatures
    {
        protected readonly MergeDataExpression _expression;

        protected MergeDataExpressionBuilderBase(MergeDataExpression expression)
        {
            _expression = expression;
        }

        void ISupportAdditionalFeatures.AddAdditionalFeature(string feature, object value)
        {
            if (!_expression.AdditionalFeatures.ContainsKey(feature))
            {
                _expression.AdditionalFeatures.Add(feature, value);
            }
            else
            {
                _expression.AdditionalFeatures[feature] = value;
            }
        }

        protected static IDictionary<string, object> ExtractData(object dataAsAnonymousType)
        {
            var data = new Dictionary<string, object>();

            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(dataAsAnonymousType);

            foreach (PropertyDescriptor property in properties)
            {
                data.Add(property.Name, property.GetValue(dataAsAnonymousType));
            }

            return data;
        }
    }

    public class MergeDataExpressionStartBuilder : MergeDataExpressionBuilderBase, IMergeDataOrInSchemaSyntax
    {
        public MergeDataExpressionStartBuilder(MergeDataExpression expression) : base(expression)
        {
        }

        public IMergeDataOrMatchSyntax<T> Row<T>(T dataAsAnonymousType)
        {
            var typed = new MergeDataExpressionTypedBuilder<T>(_expression);
            return typed.Row(dataAsAnonymousType);
        }

        public IMergeDataSyntax InSchema(string schemaName)
        {
            _expression.SchemaName = schemaName;
            return this;
        }
    }

    public class MergeDataExpressionTypedBuilder<T> : MergeDataExpressionBuilderBase, IMergeDataOrMatchSyntax<T>
    {
        public MergeDataExpressionTypedBuilder(MergeDataExpression expression) : base(expression)
        {
        }

        public IMergeDataOrMatchSyntax<T> Row(T dataAsAnonymousType)
        {
            IDictionary<string, object> data = ExtractData(dataAsAnonymousType);

            var dataDefinition = new InsertionDataDefinition();

            dataDefinition.AddRange(data);

            _expression.Rows.Add(dataDefinition);

            return this;
        }

        public void Match<M>(Func<T, M> f)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(M));
            foreach (PropertyDescriptor property in properties)
            {
                _expression.MatchColumns.Add(property.Name);
            }
        }
    }

    public class MergeDataExpression : MigrationExpressionBase
    {
        private readonly List<InsertionDataDefinition> _rows = new List<InsertionDataDefinition>();
        public string SchemaName { get; set; }
        public string TableName { get; set; }
        private readonly Dictionary<string, object> _additionalFeatures = new Dictionary<string, object>();

        private readonly List<string> _matchColumns = new List<string>();

        public List<InsertionDataDefinition> Rows
        {
            get { return _rows; }
        }

        public IDictionary<string, object> AdditionalFeatures
        {
            get { return _additionalFeatures; }
        }

        public List<string> MatchColumns
        {
            get { return _matchColumns; }
        }

        public override void CollectValidationErrors(ICollection<string> errors)
        {
        }

        public override void ExecuteWith(IMigrationProcessor processor)
        {
            var existingDataSet = processor.ReadTableData(SchemaName, TableName);
            var existingTable = existingDataSet.Tables[0];

            foreach (var row in _rows)
            {
                var exists = existingTable.Rows.OfType<DataRow>().Any(r =>
                {
                    return _matchColumns.Select(mc =>
                    {
                        var ex = r[mc];
                        var nw = row.Where(p => p.Key == mc).Select(p => p.Value).SingleOrDefault();
                        if (ex == null || nw == null)
                        {
                            return ex == nw;
                        }
                        return ex.Equals(nw);
                    }).All(m => m);
                });

                if (exists)
                {
                    ExecuteUpdateWith(processor, row);
                }
                else
                {
                    ExecuteInsertWith(processor, row);
                }
            }
        }

        private void ExecuteUpdateWith(IMigrationProcessor processor, List<KeyValuePair<string, object>> row)
        {
            var update = new UpdateDataExpression
            {
                SchemaName = SchemaName,
                TableName = TableName,
                IsAllRows = false,
                Set = row.Where(p => !_matchColumns.Contains(p.Key)).ToList(),
                Where = _matchColumns.Select(mc =>
                {
                    var v = row.Where(p => p.Key == mc).Select(p => p.Value).SingleOrDefault();
                    return new KeyValuePair<string, object>(mc, v);
                }).ToList()
            };

            processor.Process(update);
        }

        private void ExecuteInsertWith(IMigrationProcessor processor, InsertionDataDefinition row)
        {
            var insert = new InsertDataExpression
            {
                SchemaName = SchemaName,
                TableName = TableName
            };

            foreach (var af in _additionalFeatures)
            {
                insert.AdditionalFeatures.Add(af.Key, af.Value);
            }

            insert.Rows.Add(row);

            processor.Process(insert);
        }
    }
}

ondravondra avatar Sep 10 '15 11:09 ondravondra

This feature is important when using with Profile feature, where we seed sample data. Currently we have to rely on the script, which depends on database type.

ltvan avatar May 30 '16 07:05 ltvan

@ondravondra I can't wait for this feature to get into FluentMigrator. In the mean time, I've translated your extension into F#

namespace FluentMigrator.Extensions

open FluentMigrator.Expressions
open FluentMigrator.Infrastructure
open System
open System.Collections.Generic
open System.Linq
open FluentMigrator
open FluentMigrator.Model
open System.Data
open System.ComponentModel

type IMergeDataOrMatchSyntax<'T> = 
    abstract Row : 'T -> IMergeDataOrMatchSyntax<'T>
    abstract Match : ('T -> 'M) -> unit

type IMergeDataSyntax = 
    abstract Row : 'T -> IMergeDataOrMatchSyntax<'T>

type IMergeDataOrInSchemaSyntax = 
    inherit IMergeDataSyntax
    abstract InSchema : string -> IMergeDataSyntax

type IMergeExpressionRoot = 
    abstract IntoTable : string -> IMergeDataOrInSchemaSyntax

type MergeDataExpression() = 
    inherit MigrationExpressionBase()
    let rows = ResizeArray<InsertionDataDefinition>()
    let matchColumns = ResizeArray<string>()
    let additionalFeatures = new Dictionary<string, obj>()
    member val SchemaName = Unchecked.defaultof<string> with get, set
    member val TableName = Unchecked.defaultof<string> with get, set
    member this.Rows = rows
    member this.AdditionalFeatures = additionalFeatures
    member this.MatchColumns = matchColumns
    override this.CollectValidationErrors(errors : ICollection<string>) = ()

    override this.ExecuteWith(processor : IMigrationProcessor) = 
        let existingDataSet = processor.ReadTableData(this.SchemaName, this.TableName)
        let existingTable = existingDataSet.Tables.[0]
        for row in rows do
            let anyColumn (r : DataRow) = 
                let select (mc : string) = 
                    let ex = r.[mc]
                    let nw = row.Where(fun p -> p.Key = mc).Select(fun p -> p.Value).SingleOrDefault()
                    match ex, nw with
                    | null, _ -> ex = nw
                    | _, null -> ex = nw
                    | _ -> ex.Equals(nw)
                matchColumns.Select(select).All(fun m -> m)

            let exists = existingTable.Rows.OfType<DataRow>().Any(Func<DataRow, bool> anyColumn)
            if exists then this.ExecuteUpdateWith(processor, row)
            else this.ExecuteInsertWith(processor, row)

    member this.ExecuteUpdateWith(processor : IMigrationProcessor, row : ResizeArray<KeyValuePair<string, obj>>) = 
        let update = 
            UpdateDataExpression(SchemaName = this.SchemaName, TableName = this.TableName, IsAllRows = false, Set = row.Where(fun p -> not (matchColumns.Contains(p.Key))).ToList(), 
                                 Where = matchColumns.Select(fun mc -> 
                                                     let v = row.Where(fun p -> p.Key = mc).Select(fun p -> p.Value).SingleOrDefault()
                                                     new KeyValuePair<string, obj>(mc, v)).ToList())
        processor.Process(update)

    member this.ExecuteInsertWith(processor : IMigrationProcessor, row : InsertionDataDefinition) = 
        let insert = InsertDataExpression(SchemaName = this.SchemaName, TableName = this.TableName)
        for af in additionalFeatures do
            insert.AdditionalFeatures.Add(af.Key, af.Value)
        insert.Rows.Add(row)
        processor.Process(insert)

[<AbstractClass>]
type MergeDataExpressionBuilderBase(expression : MergeDataExpression) = 

    member this.ExtractData(dataAsAnonymousType : obj) : IDictionary<string, obj> = 
        let data = new Dictionary<string, obj>()
        let properties = TypeDescriptor.GetProperties(dataAsAnonymousType)
        for property in properties do
            data.Add(property.Name, property.GetValue(dataAsAnonymousType))
        data :> _

    interface ISupportAdditionalFeatures with
        member this.AddAdditionalFeature(feature : string, value : obj) = 
            if (expression.AdditionalFeatures.ContainsKey(feature) |> not) then expression.AdditionalFeatures.Add(feature, value)
            else expression.AdditionalFeatures.[feature] <- value

type MergeDataExpressionTypedBuilder<'T>(expression : MergeDataExpression) = 
    inherit MergeDataExpressionBuilderBase(expression)
    interface IMergeDataOrMatchSyntax<'T> with

        member this.Row(dataAsAnonymousType : 'T) : IMergeDataOrMatchSyntax<'T> = 
            let data = this.ExtractData(dataAsAnonymousType)
            let dataDefinition = new InsertionDataDefinition()
            dataDefinition.AddRange(data)
            expression.Rows.Add(dataDefinition)
            this :> _

        member this.Match(f : 'T -> 'M) = 
            let properties = TypeDescriptor.GetProperties(typeof<'M>)
            for property in properties do
                expression.MatchColumns.Add(property.Name)

type MergeDataExpressionStartBuilder(expression) = 
    inherit MergeDataExpressionBuilderBase(expression)
    interface IMergeDataOrInSchemaSyntax with

        member this.Row(dataAsAnonymousType : 'T) = 
            let typed = MergeDataExpressionTypedBuilder<'T>(expression) :> IMergeDataOrMatchSyntax<_>
            typed.Row(dataAsAnonymousType)

        member this.InSchema(schemaName) = 
            expression.SchemaName <- schemaName
            this :> _

type MergeExpressionRoot(context : IMigrationContext) = 
    interface IMergeExpressionRoot with
        member this.IntoTable(tableName : string) : IMergeDataOrInSchemaSyntax = 
            let expression = MergeDataExpression(TableName = tableName)
            context.Expressions.Add(expression)
            MergeDataExpressionStartBuilder(expression) :> _

[<AbstractClass>]
type MigrationExtension = 
    inherit Migration
    member this.Merge = 
        let context = this.GetType().GetField("_context", System.Reflection.BindingFlags.NonPublic ||| System.Reflection.BindingFlags.Instance).GetValue(this) :?> IMigrationContext
        new MergeExpressionRoot(context)

odytrice avatar Nov 02 '16 10:11 odytrice

After giving it some thought, I think that Seed and sample data should be embedded in Migrations. It makes sense that future migrations need to take care of scenarios where data from previous migrations exist in the database

odytrice avatar Nov 02 '16 14:11 odytrice

Just a quick fix for latest FluentMigrator (3.2.9)

using FluentMigrator.Expressions;
using FluentMigrator.Infrastructure;
using System;
using System.Collections.Generic;
using System.Linq;
using FluentMigrator;
using FluentMigrator.Model;
using System.Data;
using System.ComponentModel;

namespace MigExt
{
    public abstract class MigrationExt : Migration
    {
        public IMergeExpressionRoot Merge
        {
            get
            {
                return new MergeExpressionRoot((IMigrationContext)GetType().GetField("_context", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(this));
            }
        }

        public override void Down()
        {
        }
    }

    public interface IMergeExpressionRoot
    {
        IMergeDataOrInSchemaSyntax IntoTable(string tableName);
    }

    public class MergeExpressionRoot : IMergeExpressionRoot
    {
        private readonly IMigrationContext _context;

        public MergeExpressionRoot(IMigrationContext context)
        {
            _context = context;
        }

        public IMergeDataOrInSchemaSyntax IntoTable(string tableName)
        {
            var expression = new MergeDataExpression { TableName = tableName };
            _context.Expressions.Add(expression);
            return new MergeDataExpressionStartBuilder(expression);
        }
    }

    public interface IMergeDataSyntax
    {
        IMergeDataOrMatchSyntax<T> Row<T>(T dataAsAnonymousType);
    }

    public interface IMergeDataOrInSchemaSyntax : IMergeDataSyntax
    {
        IMergeDataSyntax InSchema(string schemaName);
    }

    public interface IMergeDataOrMatchSyntax<T>
    {
        IMergeDataOrMatchSyntax<T> Row(T dataAsAnonymousType);
        void Match<M>(Func<T, M> f);
    }

    public abstract class MergeDataExpressionBuilderBase : ISupportAdditionalFeatures
    {
        protected readonly MergeDataExpression _expression;

        public IDictionary<string, object> AdditionalFeatures => _expression.AdditionalFeatures;

        protected MergeDataExpressionBuilderBase(MergeDataExpression expression)
        {
            _expression = expression;
        }

        protected static IDictionary<string, object> ExtractData(object dataAsAnonymousType)
        {
            var data = new Dictionary<string, object>();

            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(dataAsAnonymousType);

            foreach (PropertyDescriptor property in properties)
            {
                data.Add(property.Name, property.GetValue(dataAsAnonymousType));
            }

            return data;
        }
    }

    public class MergeDataExpressionStartBuilder : MergeDataExpressionBuilderBase, IMergeDataOrInSchemaSyntax
    {
        public MergeDataExpressionStartBuilder(MergeDataExpression expression) : base(expression)
        {
        }

        public IMergeDataOrMatchSyntax<T> Row<T>(T dataAsAnonymousType)
        {
            var typed = new MergeDataExpressionTypedBuilder<T>(_expression);
            return typed.Row(dataAsAnonymousType);
        }

        public IMergeDataSyntax InSchema(string schemaName)
        {
            _expression.SchemaName = schemaName;
            return this;
        }
    }

    public class MergeDataExpressionTypedBuilder<T> : MergeDataExpressionBuilderBase, IMergeDataOrMatchSyntax<T>
    {
        public MergeDataExpressionTypedBuilder(MergeDataExpression expression) : base(expression)
        {
        }

        public IMergeDataOrMatchSyntax<T> Row(T dataAsAnonymousType)
        {
            IDictionary<string, object> data = ExtractData(dataAsAnonymousType);

            var dataDefinition = new InsertionDataDefinition();

            dataDefinition.AddRange(data);

            _expression.Rows.Add(dataDefinition);

            return this;
        }

        public void Match<M>(Func<T, M> f)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(M));
            foreach (PropertyDescriptor property in properties)
            {
                _expression.MatchColumns.Add(property.Name);
            }
        }
    }

    public class MergeDataExpression : MigrationExpressionBase
    {
        private readonly List<InsertionDataDefinition> _rows = new List<InsertionDataDefinition>();
        public string SchemaName { get; set; }
        public string TableName { get; set; }
        private readonly Dictionary<string, object> _additionalFeatures = new Dictionary<string, object>();

        private readonly List<string> _matchColumns = new List<string>();

        public List<InsertionDataDefinition> Rows
        {
            get { return _rows; }
        }

        public IDictionary<string, object> AdditionalFeatures
        {
            get { return _additionalFeatures; }
        }

        public List<string> MatchColumns
        {
            get { return _matchColumns; }
        }

        public override void ExecuteWith(IMigrationProcessor processor)
        {
            var existingDataSet = processor.ReadTableData(SchemaName, TableName);
            var existingTable = existingDataSet.Tables[0];

            foreach (var row in _rows)
            {
                var exists = existingTable.Rows.OfType<DataRow>().Any(r =>
                {
                    return _matchColumns.Select(mc =>
                    {
                        var ex = r[mc];
                        var nw = row.Where(p => p.Key == mc).Select(p => p.Value).SingleOrDefault();
                        if (ex == null || nw == null)
                        {
                            return ex == nw;
                        }
                        return ex.Equals(nw);
                    }).All(m => m);
                });

                if (exists)
                {
                    ExecuteUpdateWith(processor, row);
                }
                else
                {
                    ExecuteInsertWith(processor, row);
                }
            }
        }

        private void ExecuteUpdateWith(IMigrationProcessor processor, List<KeyValuePair<string, object>> row)
        {
            var update = new UpdateDataExpression
            {
                SchemaName = SchemaName,
                TableName = TableName,
                IsAllRows = false,
                Set = row.Where(p => !_matchColumns.Contains(p.Key)).ToList(),
                Where = _matchColumns.Select(mc =>
                {
                    var v = row.Where(p => p.Key == mc).Select(p => p.Value).SingleOrDefault();
                    return new KeyValuePair<string, object>(mc, v);
                }).ToList()
            };

            processor.Process(update);
        }

        private void ExecuteInsertWith(IMigrationProcessor processor, InsertionDataDefinition row)
        {
            var insert = new InsertDataExpression
            {
                SchemaName = SchemaName,
                TableName = TableName
            };

            foreach (var af in _additionalFeatures)
            {
                insert.AdditionalFeatures.Add(af.Key, af.Value);
            }

            insert.Rows.Add(row);

            processor.Process(insert);
        }
    }
} 

brunoAltinet avatar Oct 09 '20 13:10 brunoAltinet

@brunoaltinet can you submit a PR

jzabroski avatar Oct 21 '20 02:10 jzabroski