How to use Merge

[bpaolo71]

Hello,

I'm not sure how to use the Merge function. This is my test case:
RepoDb 1.15.1, RepoDb.MySqlConnector 1.15.0, Database MySql

CREATE TABLE test_merge (
Id int unsigned NOT NULL AUTO_INCREMENT,
Prop1 varchar(45) COLLATE utf8mb4_0900_as_ci NOT NULL,
Prop2 varchar(45) COLLATE utf8mb4_0900_as_ci NOT NULL DEFAULT '',
PRIMARY KEY (Id),
UNIQUE KEY UNIQUE (Prop1,Prop2)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci;

internal class TestMerge
{
public uint Id { get; set; }
public string Prop1 { get; set; } = string.Empty;
public string Prop2 { get; set; } = string.Empty;
}

internal class TestMergeRepository : DbRepository
{
static TestMergeRepository()
{
FluentMapper
.Entity()
.Table("test_merge");
}

public TestMergeRepository(DatabaseContext dbContext) : base(dbContext.ConnectionString, new RepositoryTrace(write: message => Console.WriteLine(message))) { }

}

[TestMethod]
public void Test_Merge()
{
var entity1 = new TestMerge()
{
Prop1 = "Value1",
Prop2 = "Value2"
};
var result1 = repository.Merge(
entity1,
fields: Field.Parse(a => new { a.Prop1, a.Prop2 }),
qualifiers: (a => new
{
a.Prop1,
a.Prop2
})
);
Console.WriteLine(result1);

var entity2 = new TestMerge()
{
    Prop1 = "Value1",
    Prop2 = "Value2"
};
var result2 = repository.Merge(
    entity2,
    fields: Field.Parse<TestMerge>(a => new { a.Prop1, a.Prop2 }),
    qualifiers: (a => new
    {
        a.Prop1,
        a.Prop2
    })
);
Console.WriteLine(result2);

}

I get this error:
Test method DomainLib.Data.MySql.Tests.Repositories.MergeTests.Test_Merge threw exception:
MySqlConnector.MySqlException: Parameter '@id' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string.

The query: INSERT INTO test_merge ( Prop1, Prop2 ) VALUES ( @Prop1, @prop2 ) ON DUPLICATE KEY UPDATE Prop1 = @Prop1, Prop2 = @prop2 ; SELECT COALESCE(@id, LAST_INSERT_ID()) AS Result ;

[mikependon]

By default, for Merge operations, without specifying the 'fields' argument, all columns of the tables are participated. It uses the PK (in your case, it is the column 'id') as the default qualifier. It needs to be participated always. It will throw an error if you have remove it from the 'fields' argument.

You can make this test passing if you remove the 'fields' argument or add the 'id' into it.

Can you check and revalidate?

[mikependon]

This is intentional by design and is covered by this Integration Test.

RepoDB/RepoDb.Core/RepoDb.Tests/RepoDb.IntegrationTests/Operations/MergeTest.cs

Line 104 in dc8f236

public void TestSqlConnectionMergeForIdentityForEmptyTableWithFields()

[bpaolo71]

I tried to remove the field argument lika that:
[TestMethod]
public void Test_Merge()
{
var entity1 = new TestMerge()
{
Prop1 = "Value1",
Prop2 = "Value2"
};
var id1 = repository.Merge(
entity1,
//fields: Field.Parse(a => new { a.Prop1, a.Prop2 }),
qualifiers: (a => new
{
a.Prop1,
a.Prop2
})
);
Console.WriteLine(id1);

var entity2 = new TestMerge()
{
    Prop1 = "Value1",
    Prop2 = "Value2"
};
var id2 = repository.Merge(
    entity2,
    //fields: Field.Parse<TestMerge>(a => new { a.Prop1, a.Prop2 }),
    qualifiers: (a => new
    {
        a.Prop1,
        a.Prop2
    })
);
Console.WriteLine(id2);

}
This is the output:
INSERT INTO test_merge ( Id, Prop1, Prop2 ) VALUES ( @id, @Prop1, @prop2 ) ON DUPLICATE KEY UPDATE Id = @id, Prop1 = @Prop1, Prop2 = @prop2 ; SELECT COALESCE(@id, LAST_INSERT_ID()) AS Result ;
00:00:00.0072060
0
INSERT INTO test_merge ( Id, Prop1, Prop2 ) VALUES ( @id, @Prop1, @prop2 ) ON DUPLICATE KEY UPDATE Id = @id, Prop1 = @Prop1, Prop2 = @prop2 ; SELECT COALESCE(@id, LAST_INSERT_ID()) AS Result ;
00:00:00.0029504
0
That works but the Id in the only line present in the table has 0 as value. That because the second INSERT overwrites the Id. How can I fix it?
Consider that the table, the field Id has AUTO_INCREMENT.
I noticed that in this case Merge returns 0 in both INSERT.

[mikependon]

It should actually just insert those data and it should generate the identity back. The problem is we cannot assume that passing a 0 in the Id is an invalid use-case. Passing 0 to the Id still a valid value from client, as some is really doing that. Unless you make your Id nullable in the class and make that explicitly NULL.

[mikependon]

Here I attached your use-case and it works as expected.

Some notes.

• Ensure to truncate the table first.
• Just leave the argument 'fields' empty, unless you only target to merge certain fields.
• Ensure that Name and Email is unique, otherwise the target row will be the same.
• Ensure as well that you have the index to Name and Email for performance purposes.

[mikependon]

Here are the additional supporting classes.

[mikependon]

@bpaolo71 I hope that my explanation above solves your problem.

[mikependon]

I reconsidered this as this is a bit outlier in MySQL. Other providers can do it, I therefore patch it. Without needing to make your Id property nullable, would you be able to install the 1.16.0-beta2 library of RepoDb.MySql or RepoDb.MySqlConnector and see if that solves your problem?

[bpaolo71]

I tried your code but with some differences to fit my use case:
string createTableQuery = @"
CREATE TABLE IF NOT EXISTS TestMergeModel (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL DEFAULT '',
Email VARCHAR(100) NOT NULL UNIQUE DEFAULT '',
UNIQUE KEY UNIQUE (Name,Email)
);";
I added the unique key with Name and Email.
var entity1 = new TestMergeModel()
{
Id = null,
Name = "Value1",
Email = "Value2"
};
var entity2 = new TestMergeModel()
{
Id = null,
Name = "Value1",
Email = "Value2"
};
The two entities have the same Name and Email
The output is that:
INSERT INTO TestMergeModel ( Id, Name, Email ) VALUES ( @id, @name, @Email ) ON DUPLICATE KEY UPDATE Id = @id, Name = @name, Email = @Email ; SELECT COALESCE(@id, LAST_INSERT_ID()) AS Result ;
00:00:00.0143537
INSERT INTO TestMergeModel ( Id, Name, Email ) VALUES ( @id, @name, @Email ) ON DUPLICATE KEY UPDATE Id = @id, Name = @name, Email = @Email ; SELECT COALESCE(@id, LAST_INSERT_ID()) AS Result ;
00:00:00.0104028
1
0
I am expecting that I found only one row with a ID value. In my case ID is 0.

I attach the code in a file because this editor is horrible with the code :)

TestRepoDbMerge.txt

[mikependon]

This is expected actually that you only have 1 row in the table as the operation is using the same values for Name and Email to merge the data. But I would expect that the Id should be 1 instead of 0 if the increment seed starts at 1. I will investigate later.

Remember that I actually issue a new beta2 for you that is reconsdering that Nullable Id property. In the new patch, therr is no COALECE anymore and it will always return the Last Inserted Id for as long the column is an identity column.

[bpaolo71]

Thank you. I will test it as soon as i can.

[bpaolo71]

I tested with the beta2 and in both case (with nullable ID and not) is working as expected.
Thank you!