Composite Keys and separated Identity Key

Question

Please read that question: Create code first, many to many, with additional fields in association table

Here is the scenario from OP:

public class Member
{
    public int MemberID { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

    public virtual ICollection<Comment> Comments { get; set; }
}

public class Comment
{
    public int CommentID { get; set; }
    public string Message { get; set; }

    public virtual ICollection<Member> Members { get; set; }
}

public class MemberComment
{
    public int MemberID { get; set; }
    public int CommentID { get; set; }
    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

The accepted answer was:

public class MemberComment
{
    [Key, Column(Order = 0)]
    public int MemberID { get; set; }
    [Key, Column(Order = 1)]
    public int CommentID { get; set; }

    public virtual Member Member { get; set; }
    public virtual Comment Comment { get; set; }

    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

Right now, my question is: What if I want add another identity field like a Guid or AutoInc int, by example:

    public int MemberCommentID { get; set; }

I'm not in discussing about it's the better method or no, I want know how I do it?

I was thinking something like this

public class MemberComment
{
    [Key, Column(Order = 0)]
    public int MemberCommentID { get; set; }

    [Key, Column(Order = 1)]
    public int MemberID { get; set; }
    [Key, Column(Order = 2)]
    public int CommentID { get; set; }

    public virtual Member Member { get; set; }
    public virtual Comment Comment { get; set; }

    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

But then EF will (I think) use the 3 fields in the key, when the right is MemberCommentID is alone 1 key, and MemberID+CommentID is another key.

score 1 · Accepted Answer · edited May 23 '17 at 12:31

1

Why don't you use a ForeignKey on MemberID and CommentID in such a case?

public class MemberComment
{
    [Key]
    public int MemberCommentID { get; set; }

    [ForeignKey]
    public int MemberID { get; set; }
    [ForeignKey]
    public int CommentID { get; set; }

    public virtual Member Member { get; set; }
    public virtual Comment Comment { get; set; }

    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

As you can see it will actually allow you to have duplicate MemberComments (that connect the same Member and Comment instances).

If you want to enforce that Member and Comment can be linked only once, then you can achieve it with Unique Constraint.

But if that is your requirement (one MemberComment per each Member Comment pair), then why do you want to add a MemberCommentID key?

MemberID, CommentID is a perfect and natural primary key, that does all the job done without additional fields/indices.

edited May 23 '17 at 12:31

Community

1
1

answered Apr 16 '16 at 19:24

Eugene Podskal

10,270
5
31
53

Why it's not enforcing the key MemberID+CommentID, if I try add a new MemberComment with duplicate MemberID+CommentID, EF doesn't throw any error.. :/ – Click Ok Apr 16 '16 at 19:29
Yes, I know it is perfect... the pain of working and maintan an existing database :/ – Click Ok Apr 16 '16 at 19:37
@ClickOk So it is [code-first-from-database](https://msdn.microsoft.com/en-us/library/jj200620.aspx) rather than code-first as it is tagged in your question, or am I missing something? – Eugene Podskal Apr 16 '16 at 19:40
I didn't tagged, but my linked question yes, so thats the confusion, sorry. – Click Ok Apr 16 '16 at 19:49
@ClickOk I mean do you create your models by hand, or do you use code-first-from-database generator and it fails for you? – Eugene Podskal Apr 16 '16 at 19:51
I create my models myself, to work in an existing (from my customer) database. – Click Ok Apr 16 '16 at 19:52
@ClickOk Have you tried [code-first-from-database](https://msdn.microsoft.com/en-us/library/jj200620.aspx)? It seems to be EF6 and higher, but you can always run it once and adapt the generated code for earlier EF versions (if that is the case). – Eugene Podskal Apr 16 '16 at 19:54
1

I will try, but I think will not work because in the database the rule is not enforced, so EF probably will ignore it too, and reading your link looks that EF doesn't work unique constraints with foreign keys. Maybe I will code is with "Before save" checking + exception handling. – Click Ok Apr 16 '16 at 19:57
@ClickOk Well, with already existing, and not as well designed as we'd liked it to be, database (that you also cannot change) your options seem to be quite limited - AFAIK EF on its own won't enforce those constraints, but I can't find any exact references, so you may like to ask a separate question along the line of "How to enforce foreign key constraint in EF when database doesn't have a foreign key"? But I wouldn't expect a silver bullet for such a case. – Eugene Podskal Apr 16 '16 at 20:06

Composite Keys and separated Identity Key

1 Answers1