How to modify PetaPoco class to work with Composite key comprising of non-numeric columns?

Question

I have a table with following columns:

ContractorId ......... INT ............. IDENTITY
ContractorName ........ Varchar(50) ....... P.K
ContractorGrade ....... Varchar(3) ....... P.K

The class generated by PetaPoco T4 template looks like this:

[TableName("contractor_master")]
[PrimaryKey("contractorname", autoIncrement=false)]
[ExplicitColumns]
public partial class contractor_master : TubewellRepo.Record<contractor_master>  
{
    [Column] 
    public int contractorid 
    { 
        get
        {
            return _contractorid;
        }
        set
        {
            _contractorid = value;
            MarkColumnModified("contractorid");
        }
    }
    int _contractorid;

    [Column] 
    public string contractorname 
    { 
        get
        {
            return _contractorname;
        }
        set
        {
            _contractorname = value;
            MarkColumnModified("contractorname");
        }
    }
    string _contractorname;

    [Column] 
    public string contractorgrade 
    { 
        get
        {
            return _contractorgrade;
        }
        set
        {
            _contractorgrade = value;
            MarkColumnModified("contractorgrade");
        }
    }
    string _contractorgrade;
  }

The code to INSERT a new record is as below:

// Insert a record
var Contractor=new contractor_master();
Contractor.contractorname = "Super Borewells";
Contractor.contractorgrade = "A";

db.Insert(Contractor);

In the second line of the Class Code, I want to know how to mention a Composite Key, which is (ContractorName + ContractorGrade).

Secondly, it is not inserting a record because it expects an Id column. Even though ContractorId is IDENTITY, it is not a primary key.

It is not INSERTING a new record and gives error because it is inserting 0 in the IDENTITY column.

Samaria

If you already have a ContractorId column, why not have PetaPoco use it as the primary key? You can still keep the composite key in the DB. – Dolhenty

Ok. But just wanted to know whether PetaPoco supported Composite Key or not. – Samaria

Answer 1

My branch here: https://github.com/schotime/petapoco supports composite primary key by specifying the PrimaryKey attribute like:

[PrimaryKey("ContractorName,ContractorGrade")]

I'm not sure how it will work if you want the identity column there as well.

Vibrissa

I tried it but it lacks support for IsNew(..) method, can you update on this? – Environs

Nice! I'm surprised that after all this time it has not been pull back into the main project. – Cristiecristin

Answer 2

I had to make the following changes to support IsNew()

// Check if a poco represents a new record
        public bool IsNew(string primaryKeyName, object poco)
        {
            var pd = PocoData.ForObject(poco, primaryKeyName);
            object pk;
            PocoColumn pc;
            if (pd.Columns.TryGetValue(primaryKeyName, out pc))
            {
                pk = pc.GetValue(poco);
            }
#if !PETAPOCO_NO_DYNAMIC
            else if (poco.GetType() == typeof(System.Dynamic.ExpandoObject))
            {
                return true;
            }
#endif
            else if (primaryKeyName.Contains(","))
            {
                return primaryKeyName.Split(',')
                    .Select(pkPart => GetValue(pkPart, poco))
                    .Any(pkValue => IsDefaultOrNull(pkValue));
            }
            else
            {
                pk = GetValue(primaryKeyName, poco);
            }

            return IsDefaultOrNull(pk);
        }

        private static object GetValue(string primaryKeyName, object poco)
        {
            object pk;
            var pi = poco.GetType().GetProperty(primaryKeyName);
            if (pi == null)
                throw new ArgumentException(
                    string.Format("The object doesn't have a property matching the primary key column name '{0}'",
                                  primaryKeyName));
            pk = pi.GetValue(poco, null);
            return pk;
        }

        private static bool IsDefaultOrNull(object pk)
        {
            if (pk == null)
                return true;

            var type = pk.GetType();

            if (type.IsValueType)
            {
                // Common primary key types
                if (type == typeof(long))
                    return (long)pk == default(long);
                else if (type == typeof(ulong))
                    return (ulong)pk == default(ulong);
                else if (type == typeof(int))
                    return (int)pk == default(int);
                else if (type == typeof(uint))
                    return (uint)pk == default(uint);
                else if (type == typeof(Guid))
                    return (Guid)pk == default(Guid);

                // Create a default instance and compare
                return pk == Activator.CreateInstance(pk.GetType());
            }
            else
            {
                return pk == null;
            }
        }

Direct

Actually IsNew is used to decide whether a Insert or Update is needed. In PetaPoco it says "IsNew() and Save() are only supported on tables with auto-increment/identity primary key columns" I believe it needs lots of other work like auto incrementing for composite keys/make a Select to see if it is a new key for real. this method is not needed to be modified – Odense

Answer 3

I can see that there's the CompositeKeySuppport branch now, so it's a good chance we'll have this supported in the official repo soon, meaning we'll get NuGet updates and stuff.

Bravura