How to configure a table with Multi-Column Primary Key

[bpaolo71]

I have a table like that:
CREATE TABLE multicolumn_primery_key (
col1 varchar(10) NOT NULL DEFAULT '',
col2 tinyint NOT NULL,
col3 varchar(15) NOT NULL DEFAULT '',
col4 varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (col1,col2,col3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci;
Is it possible to configure i RepoDb?

[mikependon]

I am currently in busines travel, therefore I could not go low-level. But I can answer this easily. Composite keys are not supported as also registered on our limitation page. The alternative to it is documented when you do any push operations.

[mikependon]

Hi @bpaolo71, if I will ask you, how would you like to handle composite keys? If you have a proposal or idea on how to handle it without affecting and breaking the Core ORM, I guess let us go for it. Just let me know.

[bpaolo71]

It is about a old table that was created without the usual 'ID' primary key. But Query och QueryAsync are working fine anyway so it is only Insert/update that have the problem and I can solve with a raw query.
I was wondering if the problem is that "This remains a limitation because RepoDB only returns a single value.", maybe it could help to say to RepoDb in someway att it is not the database that generate the primary key and avoid to return the value.
Another scenario is that we have a one column primary key as string/guid that whose creation is not managed by the database but by the client. The client doesn't need a return value. In this case maybe Insert/Update are working anyway, just the client ignore the return value. But consider that not all the time the database is responsible for creating the primary key, whether a single column or multiple columns are used.

[mikependon]

I think the scenario that the client is sending the value of ID (whether it is a GUID, INT, etc) should easily be handled by the library. We have quite a lot of Integration Tests to that scenario.

In relation to which value to return, your recommendation is actually valid. If you see the KeyColumnReturnBehavior enumeration from the documentation, I think we can consider it there.

[mikependon]

KeyColumnReturnBehavior

[bpaolo71]

Nice,
If it is possible to specify for a repository something like KeyColumnReturnBehavior.None and all Inert/update/others do not return a value should it solve the problem with the client that is sending the value of ID or multiple-column primary key? Is there a scenario where there is a multiple-column primary key and those columns are created by database?

[mikependon]

Hi @bpaolo71 the KeyColumnReturnBehavior.None is not yet supported, but I can support it in v1.17.0 which I am targetting to release on 2nd week October. Will you be able to create an issue for such request?

[cajuncoding]

@mikependon it is strange that RepoDB doesn't support multiple PKey columns . . . as it's not very hard to implement in the MERGE queries. The fact that only a list of single values are returned could be addressed by providing an enum or maybe accepting a Func<> that returns a value the caller defines -- for example a concatenated string of the PKey values. Or nothing at all null.....

In case it may be helpful my library that I wrote does this and it's handled here in the MERGE query builder logic:
https://github.com/cajuncoding/SqlBulkHelpers/blob/1ba9e5d29115945ed63491ef4d532adc064ec1b0/NetStandard.SqlBulkHelpers/SqlBulkHelper/QueryProcessing/SqlBulkHelpersMergeQueryBuilder.cs#L68

We use RepoDB in combination with my SqlBulkHelpers library because they both have different behaviors but some of RepoDBs limitations drive us to needing to SqlBulkHelpers for much of our Bulk processing.

[mikependon]

We can make it happen in RepoDB, we never just prioritize it. In the above table schema defined by @bpaolo71, how would we return the values of composite keys? An array/list of values? Tuple? Dictionary? Or etc?

Note: Returning a concatenated string is contamination. So please advice. 😊

[cajuncoding]

Contamination isn’t the right word but yes I agree def not clean….i was just brainstorming on my mobile.

But thinking on it a little more, personally i think that the current behavior should work fine….return the count of rows updated or the count of rows inserted…etc.

Even the identity behavior is fine by mutating the input models to return the generated Identity values (if one exists).

The only difference I see is how rows are matched on multiple fields (that make up the composite primary key) vs a single identity column.

Not clear on why anything else would change?

[mikependon]

In my opinion, type contamination will be there, especially if we are speaking of date and floating point. Though they may not be selected as PK columns anyway.

The only difference I see is how rows are matched on multiple fields (that make up the composite primary key) vs a single identity column.

It is a part of the limitation for now, but can be rectified by explicit values passed to the 'where/what' argument. There is an alternative to this as explained on our limitation page.

[mikependon]

I added the #1364 as we have identified that both Firebird and Vertica is disrespecting the enumeration KeyColumnReturnBehavior.