Repodb: Bug: Wrong schema detection in case of composite keys

[EmilianoMusso]

Testing RepoDb on SQL Server and having composite key tables, i've run into the following behaviour. RepoDb cannot look out the complete composite key, using only the first field of it to determine the primary key. That results in total deletion of records which have equality on the determined field.

Here follows a sample code to replicate behaviour (TSQL + C#) :

   CREATE TABLE [dbo].[Product](
      CompanyCode VARCHAR(2) DEFAULT '' NOT NULL,
      ProductCode VARCHAR(25) DEFAULT '' NOT NULL,
      Description VARCHAR(50) DEFAULT '' NOT NULL,
      PRIMARY KEY(CompanyCode, ProductCode)
   )

    // Sample class
    public class Product
    {
        // First field of primary key
        public string CompanyCode { get; set; }
        // Second field of primary key
        public string ProductCode { get; set; }
        public string Description { get; set; }
    }

    // Code to replicate behaviour
    var p = new Product()
                {
                    CompanyCode = "CC",
                    ProductCode = "Product_01",
                    Description = "FOR TEST USE"
                };
   // The following command result in complete deletion of all records having CompanyCode = "CC", without considering ProductCode
   connection.Delete<Product>(p);

Thank you

[mikependon]

@EmilianoMusso - thank you for this report. Yes, this is correct and it is a "known" issue as well in RepoDb even here in the organization. It can only support the non-composite Primary Key as of writing this.

Given with your sample:

var product = new Product()
{
    CompanyCode = "CC",
    ProductCode = "Product_01",
    Description = "FOR TEST USE"
};

The alternative for you is below.

connection.Delete<Product>(p =>
    p.CompanyCode == product.CompanyCode && p.ProductCode == product.ProductCode);

Or via dynamic like below.

connection.Delete<Product>(new { product.CompanyCode , product.ProductCode });

It is still fast as it using the columns of you Composite Key.

In relation to the support to Composite Key, we will discuss it internally, therefore, just leave this issue open.

[EmilianoMusso]

@mikependon Thank you so much.
Yes, i've already tried dynamic and lambda prior submitting the issue, and they are ok, and fast - like you said.
Thank you for your answer, samples and time.

[mikependon]

@EmilianoMusso, I have describe this composite keys scenario in our limitation page. After the further analysis, we cannot support this use-case as we are supporting the other use-case that is completely eliminating the validity of having the support to composite keys, for the Fluent-Based operations.

It is important to take note that the Non-Fluent-Based operations (i.e.: Query(TableName), Insert(TableName), etc) are completely supporting this, like the alternative solution that I recommended.

[EmilianoMusso]

@mikependon Ok, thank you. Limitation page is clear enough, as are alternative solutions. Shall we close the issue, or you prefer to keep it active?

[mikependon]

I created a label to tag this as limitation. Maybe just keep this one open so the community would see it still. Thank your for this report. 👍🏼