InsertOrUpdate with Bulk Merge/ -Insert or -Update (Dapper plus)

[laggerbo]

I am using Dapper plus to Insert data and now I will like to make a function that inserts data if the object doesn't exists and update the object if it does. I've done this:

public void InsertOrUpdate(List<Person> persons)
        {
            DapperPlusManager.Entity<Person>("Insert_key")
                .Table("fact.Person")
                .Key(x => x.PersonId)
                .InsertIfNotExists();

            _connection.BulkInsert("Insert_key", persons);

            DapperPlusManager.Entity<Person>("Update_key")
                .Table("fact.Person")
                .Key(x => x.PersonId)
                .Ignore(x => x.FirstName);

            _connection.BulkUpdate("Update_key", persons);
        }

This works. It Updates all data for person except the tables in .Ignore(). But instead of choosing columns not to update I would like to choose columns to update. I've tried combinations like this:

            DapperPlusManager.Entity<Person>("Merge_key")
                .Table("fact.Person")
                .Key(x => x.PersonId)
                .Map(x => new {x.PhoneNumber, x.Address);

            _connection.BulkMerge("Merge_key", persons);

But without any luck.

How can I Insert a new Person if the PersonId does not exists in the column OR update an existing Person with PhoneNumber and Address?

[JonathanMagnan]

Hello @LAJOH ,

We do not provide this options yet, we will look at it today.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @LAJOH ,

We investigate this issue today but unfortunately didn't make it work yet with BulkMerge.

However, you can easily handle this scenario with BulkUpdate + BulkInsert (You must do BulkUpdate first to avoid to update inserted items).

Here is an example:

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Windows.Forms;
using Z.Dapper.Plus;

namespace Z.Lab
{
    public partial class Form_Request_BulkMerge_ColumnOnly : Form
    {
        public Form_Request_BulkMerge_ColumnOnly()
        {
            DapperPlusManager.Entity<Person>("Insert_key")
                .Table("Person")
                .Key(x => x.PersonId)
                .InsertIfNotExists();

            DapperPlusManager.Entity<Person>("Update_key")
                .Table("Person")
                .Key(x => x.PersonId)
                .Map(x => new {x.PhoneNumber, x.Address});

            var list = new List<Person>();

            for (var i = 0; i < 20; i++)
            {
                list.Add(new Person {PersonId = i, FirstName = "x_" + i, Address = "x_" + i, PhoneNumber = "x_" + i});
            }

            using (var conn = new SqlConnection(My.AppConfig.ConnectionStrings.DapperPlusConnection))
            {
                conn.Open();

                conn.BulkUpdate("Update_key", list);
                conn.BulkInsert("Insert_key", list);
            }

            InitializeComponent();
        }

        public class Person
        {
            public int PersonId { get; set; }
            public string FirstName { get; set; }
            public string PhoneNumber { get; set; }
            public string Address { get; set; }
        }
    }
}

Let me know if this example can solve your problem.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @LAJOH ,

This issue will be closed since it has been resolved.

Feel free to reopen it if you feel otherwise.

Best Regards,

Jonathan

[JonathanMagnan]

In case someone is looking for this answer,

It's now possible to choose only column that will be part of the UPDATE during a BulkMerge with the OnMergeUpdateInputExpression option such as:

context.BulkMerge(list, options =>
{
    options.OnMergeUpdateInputExpression = x => new { x.PhoneNumber, x.Address };
    // options.OnMergeInsertInputExpression = x => new { x.ColumnString };
});