Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Thomas 160 posts 335 karma points
    Dec 10, 2014 @ 21:27
    Thomas
    1

    Combined primary keys with petapoco

    The following create a primary key for the first column only. Any suggestions please to resolve the issue?

    [TableName("tsLcValues")] [ExplicitColumns] [PrimaryKey("LcTableID,LcResourceID,CultureISOCode,RowID", autoIncrement = false)] public class tsLcValue { public tsLcValue() { }

        [Column("LcTableID")]
        [PrimaryKeyColumn(AutoIncrement = false)]
        public int LcTableID { get; set; }
    
        [Column("LcResourceID")]
        [PrimaryKeyColumn(AutoIncrement = false)]
        public int LcResourceID { get; set; }
    
        [Column("CultureISOCode")]
        [Length(5)]
        [Constraint(Default = "")]
        [PrimaryKeyColumn(AutoIncrement = false)]
        public string CultureISOCode { get; set; }
    
        [Column("RowID")]
        [PrimaryKeyColumn(AutoIncrement = false)]
        public int RowID { get; set; }
    
        [Column("ResourceValue")]
        [Constraint(Default = "")]
        public string ResourceValue { get; set; }
    }
    
  • Tim 1193 posts 2675 karma points MVP 3x c-trib
    Jan 06, 2015 @ 16:50
    Tim
    0

    I believe the standard version of PetaPoco doesn't support multi column primary keys. There are a couple of custom branches which add this functionality though. I'm not sure how easy it would be to use these with Umbraco though.

  • Tim 1193 posts 2675 karma points MVP 3x c-trib
    Jan 06, 2015 @ 16:56
    Tim
    0

    The version used by Umbraco is a forked version with some updates to fix some memory perfomrance issues as well, so you won't be able to just swap them out.

  • Delly Mellas Nyman 64 posts 316 karma points
    May 10, 2016 @ 11:12
    Delly Mellas Nyman
    0

    Maybe you can try to use this

    [TableName("ContentMetadataMember")]
    [PrimaryKey("NodeId,MemberId,MetadataType")]
    [ExplicitColumns]
    public class ContentMetadataMember
    {
        public ContentMetadataMember()
        {
        }
    
        public ContentMetadataMember(int nodeId, int memberId, MetadataType metadataType)
        {
            NodeId = nodeId;
            MemberId = memberId;
            MetadataType = (int)metadataType;
        }
    
        [Column("NodeId")]
        [PrimaryKeyColumn(AutoIncrement = false, OnColumns = "NodeId,MemberId,MetadataType")]
        public int NodeId { get; set; }
    
        [Column("MemberId")]
        public int MemberId { get; set; }
    
        [Column("MetadataType")]
        public int MetadataType { get; set; }
    
        [Column("IpAddress")]
        public string IpAddress { get; set; }
    }
    

    it generate something like this in my mysql db:

  • Ian 23 posts 113 karma points
    Dec 06, 2016 @ 16:27
    Ian
    0

    fyi, this didnt work for me: it creates the table nicely but, when writing to the database

    ApplicationContext.Current.DatabaseContext.Database.Insert(myObject);
    

    I get an error:

    Invalid column name 'col1,col2'

    :(

  • john blair 48 posts 219 karma points
    Jan 06, 2020 @ 10:51
    john blair
    1

    Any update on this for umbraco 8? It seems to ignore my primary key attributes and no primary key is created. Otherwise the table is created as expected. My table definition is

    [TableName(DatabaseConstants.QuestionGroupQuestionTable)]
    [ExplicitColumns]
    [PrimaryKey("questiongroupid,questionid")]
    public class QuestionGroupQuestion
    {
        [Column("questiongroupid")]
        public int QuestionGroupId { get; set; }
    
        [Column("questionid")]
        public int QuestionId { get; set; }
    
        [Column("sortorder")]
        public int SortOrder { get; set; }
    
        [Column("created")]
        public DateTime Created { get; set; }
    
        [Column("updated")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public DateTime? Updated { get; set; }
    
        public override string ToString()
        {
            return $"QuestionGroupId:{this.QuestionGroupId},QuestionGroupQuestionId:{this.QuestionId},SortOrder:{this.SortOrder},Created:{this.Created},Updated:{this.Updated}";
        }
    } 
    
  • Tim 1193 posts 2675 karma points MVP 3x c-trib
    Jan 06, 2020 @ 11:06
    Tim
    1

    Umbraco 8 uses NPoco instead of PetaPoco I believe, and according to the docs, it should be possible to have a composite key, see docs here: https://github.com/schotime/NPoco/wiki/Composite-Primary-Keys (assuming this is the version Umbraco uses).

  • john blair 48 posts 219 karma points
    Jan 06, 2020 @ 11:22
    john blair
    1

    Fyi i read that link and tried it - the composite key just got ignored - no primary key was created. But see the fix i used with the OnColumns attribute. Thanks.

  • john blair 48 posts 219 karma points
    Jan 06, 2020 @ 11:21
    john blair
    2

    Thanks for the pointer - while you were writing i managed to find the solution by trial and error. The fix was given by the following line:

    [PrimaryKeyColumn(AutoIncrement = false, OnColumns = "questiongroupid,questionid")] 
    

    The full table definition used is:

    [TableName(DatabaseConstants.QuestionGroupQuestionTable)]
    [ExplicitColumns]
    [PrimaryKey("questiongroupid,questionid")]
    public class QuestionGroupQuestion
    {
        [Column("questiongroupid")]
        [PrimaryKeyColumn(AutoIncrement = false, OnColumns = "questiongroupid,questionid")]
        public int QuestionGroupId { get; set; }
    
        [Column("questionid")]
        public int QuestionId { get; set; }
    
        [Column("sortorder")]
        public int SortOrder { get; set; }
    
        [Column("created")]
        public DateTime Created { get; set; }
    
        [Column("updated")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public DateTime? Updated { get; set; }
    
        public override string ToString()
        {
            return $"QuestionGroupId:{this.QuestionGroupId},QuestionId:{this.QuestionId},SortOrder:{this.SortOrder},Created:{this.Created},Updated:{this.Updated}";
        }
    }
    
  • milkman matty 31 posts 125 karma points
    Nov 08, 2021 @ 06:36
    milkman matty
    0

    Thanks John, you're a life saver. This works for Umbraco 9 too :D

Please Sign in or register to post replies

Write your reply to:

Draft