Help: Composite key update failure when using IPropertyHandler (string to Guid)

[bcssov]

Here's another scenario where update failes, it seems related to IPropertyHandler as well with a composite key combination? In this scenario (I've continued using the sample I posted in the previous issue)

• I've created a new table

connection.ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS demoCompositeKey
	(
		id char(36),
		id2 char(36),
		entityId INTEGER,
		name TEXT NOT NULL,
		validFrom Datetime,
		validTo  datetime,
		deleted byte,
		PRIMARY KEY ( id, id2 )
	);");

• Defined mappings (I've lowercased the property names to match the table schema as a workaround of the issue I reported)

    [Map("demoCompositeKey")]
    public class DemoCompositeKey //: IUpsertEntity
    {
        [Map("id"), Primary, PropertyHandler(typeof(StringToGuidPropertyHandler))]
        public Guid id { get; set; }
        [Map("id2"), Primary, PropertyHandler(typeof(StringToGuidPropertyHandler))]
        public Guid id2 { get; set; }
        [Map("entityId")]
        public long EntityId { get; set; }
        [Map("validFrom")]
        public DateTime ValidFrom { get; set; }
        [Map("validTo")]
        public DateTime ValidTo { get; set; }
        [Map("deleted")]
        public byte Deleted { get; set; }
        [Map("name")]
        public string Name { get; set; }
    }

• A single record was inserted into the table
• I've then updated the query and re-fetched the entry from the table

            var rows = connection.Update<DemoCompositeKey>(update, e => e.id == update.id && e.id2 == update.id2);
            Console.WriteLine($"Affected rows {rows}");
            var updatedObject = connection.Query<DemoCompositeKey>(e => e.id == update.id && e.id2 == update.id2).FirstOrDefault();
            Console.WriteLine($"New name {update.Name}");

• The record was not updated, name column was string was changed to "Changed" value, as you can see in the immediate window the update failed:

• Changing the id and id2 property types to string resulted in a successful update operation, as you can see in the immediate window:

• Demo project (modified verion) RepoDbIssue437.zip

Also no timelines, I can workaround by changing the type to string.

[mikependon]

The default support to this will never be implemented as RepoDb tend to sided the other scenario that is forcefully eliminating this use-case.

@bcssov - here we explain why composite key is a limitation in RepoDb.

But what you have done here is correct, you have to use the 2 columns of the composite key as the qualifiers. However, the type change seems to be an issue that we will look at. We will get back to you soon.

[bcssov]

Thanks for the links. I've already read and agree with your reasoning (as well as related reported issues). The goal of this issue was to report the type change issue as it does not seem to be the intended behavior :)

[jmiedreich]

Hello Mike,

I'm hoping you can clear up a point for me. Several times I have seen you refer to this statement The default support to this will never be implemented as RepoDb tend to sided the other scenario that is forcefully eliminating this use-case. My question is, what is the other scenario that is forcefully eliminating this uses case, I do not see it described anywhere? Thank you and I love this library so much, great work.

[mikependon]

@jmiedreich on the same page were you have taken such phrase, the technically reason is also explained there.

Insertion with PK/Identity

The return value will not be the value of the Composite Keys, instead, it will return the ID of that row, whether the ID column is an identity (or not). The value of the Composite Keys cannot be returned on this situation as we only expect a scalar value.

using (var connection = new SqlConnection(ConnectionString))
{
    var id = connection.Insert<Person>(new Person { Name = "John Doe" });
}

Update

We tend to defaultly use the PK as the qualifier.

using (var connection = new SqlConnection(ConnectionString))
{
    var affectedRows = connection.Update<Person>(new Person { Name = "John Doe" }, 10045);
}

Here, you can read an alternative way for the Composite Keys.

Delete

Same as Update operation, we are using the PK as the qualifier.

using (var connection = new SqlConnection(ConnectionString))
{
    var affectedRows = connection.Delete<Person>(10045);
}

Here, you can as well read an alternative way for the Composite Keys.

Note

When we do the use the PK as qualifier for both the Delete and Update operation, we only expect a single value, not an array of values that could fill-up the composite keys. Therefore, it is an architectural change and the impact is huge for all consumers. Lastly, the scenario of inserting the row that expects the result of the newly generated ID is really opposing the scenario of the Composite Keys (in which the key is pre-given by the client during the push operations).

Conclusion

In general, it is possible but we need to change the behavior of the library, not to mention the overall architectural changes on the push/pull operations. The value it generates is small if being compared to the scenarios the current capability is being solved.

Hope these explains.

[jmiedreich]

@mikependon - Thank you for taking the time to further explain the details, much appreciated. I was able to get the update working with the expressions and dynamics example provided on the limitations page.

I see that UpdateAll supports a Expression<Func<TEntity, object>> qualifiers being passed in, but I'm spinning my wheels getting the syntax correct. Do you have an example of this or a unit test I can reference ?

Currently, I have this working:

List<Field> Keys = new List<Field>();

                   Field Key1 = new Field(nameof(PrimaryKeyTest.keyCatID));
                   Field Key2 = new Field(nameof(PrimaryKeyTest.KeyID));

                   Keys.Add(Key1);
                   Keys.Add(Key2);

                   var UpdatedRows = connection.UpdateAll<PrimaryKeyTest>(mappingData, Keys);

Is there any other elegant way?

[mikependon]

You can see a sample code implementation from here. In your case, you can call the operation via lambda like below.

connection.UpdateAll<PrimaryKeyTest>(mappingData, qualifiers: e =>new { e.keyCatID, e.KeyID });

The lambda above will parse the newly created dynamic object targeting the defined 2 properties keyCatID and KeyID.

Please revert if this is what you're asking and if you have further questions.

[jmiedreich]

@mikependon - Let me try your example and I will update you again. Also, thank you for all the help so quickly.

Jeremy

[jmiedreich]

@mikependon - Thanks, your follow up example works out just fine, thanks again for help.

Jeremy