Merge

This method is used to insert a new row or update an existing row from the table. This operation only works like UPSERT, it does not do any deletion.

Code Snippets

Below is the sample code to merge a row into the [dbo].[Person] table.

using (var connection = new SqlConnection(connectionString))
{
    var person = new Person
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York",
        DateOfBirth = DateTime.Parse("2020-01-01"),
        IsActive = true,
        DateInsertedUtc = DateTime.UtcNow
    };
    var id = connection.Merge<Person>(person);
}

By default, the primary column is used as a qualifier. You can override it by simply passing the list of fields in the qualifiers argument.

using (var connection = new SqlConnection(connectionString))
{
    var person = new Person
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York",
        DateOfBirth = DateTime.Parse("2020-01-01"),
        IsActive = true,
        DateInsertedUtc = DateTime.UtcNow
    };
    var id = connection.Merge<Person>(person,
    qualifiers: (p => new { p.Name, p.DateOfBirth }));
}

The result will vary from the value of the GlobalConfiguration.Options property KeyColumnReturnBehavior.

Targeting a Table

You can also target a specific table by passing the literal table like below.

using (var connection = new SqlConnection(connectionString))
{
    var person = new Person
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York",
        DateOfBirth = DateTime.Parse("2020-01-01"),
        IsActive = true,
        DateInsertedUtc = DateTime.UtcNow
    };
    var id = connection.Merge<Person>("[dbo].[Person]",
        entity: person);
}

Or via Anonymous Type.

using (var connection = new SqlConnection(connectionString))
{
    var person = new
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York",
        DateOfBirth = DateTime.Parse("2020-01-01"),
        IsActive = true,
        DateInsertedUtc = DateTime.UtcNow
    };
    var id = connection.Merge("[dbo].[Person]",
        entity: person);
}

Or via Dictionary<string, object> or ExpandoObject.

using (var connection = new SqlConnection(connectionString))
{
    var person = new Dictionary<string, object>
    {
        { "Id", 10045 },
        { "Name", "John Doe" },
        { "Address", "New York" },
        { "DateOfBirth", DateTime.Parse("2020-01-01") },
        { "IsActive", true },
        { "CreatedDateUtc", DateTime.UtcNow }
    };
    var id = connection.Merge("[dbo].[Customer]",
        entity: person);
}

The library will add the newly created value of the identity column into the Dictionary<string, object> or ExpandoObject object (if not present).

Specific Columns

You can also target a specific columns to be merged by passing the list of fields to be included in the fields argument.

using (var connection = new SqlConnection(connectionString))
{
    var person = new Person
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York",
        DateOfBirth = DateTime.Parse("2020-01-01"),
        IsActive = true,
        DateInsertedUtc = DateTime.UtcNow
    };
    var fields = Field.Parse<Person>(e => new
    {
        e.Name,
        e.DateOfBirth,
        e.DateInsertedUtc
    })
    var id = connection.Merge<Person>(entity: person,
        fields: fields);
}

Or via dynamics.

using (var connection = new SqlConnection(connectionString))
{
    var person = new
    {
        Id = 10045,
        Name = "John Doe",
        Address = "New York",
        DateOfBirth = DateTime.Parse("2020-01-01"),
        IsActive = true,
        DateInsertedUtc = DateTime.UtcNow
    };
    var id = connection.Merge("[dbo].[Person]",
        entity: person,
        fields: Field.From("Name", "DateOfBirth", "DateInsertedUtc"));
}

Table Hints

To pass a hint, simply write the table-hints and pass it in the hints argument.

using (var connection = new SqlConnection(connectionString))
{
    var id = connection.Merge<Person>(person,
        hints: "WITH (TABLOCK)");
}

Or, you can use the SqlServerTableHints class.

using (var connection = new SqlConnection(connectionString))
{
    var id = connection.Merge<Person>(person,
        hints: SqlServerTableHints.TabLock);
}