none
Composite foreign key in one-to-many relation? RRS feed

  • Question

  • Hi,

    I'm trying to model a database in LINQ to SQL. I have a table JournalPosts. Each entity in that table is associated with 1 or more senders or recipients. I have the following composite primary key in my table "JournalPosts":

    [Column(IsPrimaryKey = true)]
    public int CaseYear { get; set; }
    
    [Column(IsPrimaryKey = true)]
    public int CaseSequenceNumber { get; set; }
    
    [Column(IsPrimaryKey = true)]
    public int DocumentNumber { get; set; }
    
    I define the one-many relationship like this in JournalPosts:

    private EntitySet<SqlSenderRecipient> senderRecipients;
    
    [Association(OtherKey = "CaseYear, CaseSequenceNumber, DocumentNumber", Storage="senderRecipients")]
    private EntitySet<SqlSenderRecipient> SenderRecipients
    {
        get { return senderRecipients; }
        set 
        { 
            senderRecipients.Assign(value); 
        }
    }
    
    In my other table (SenderRecipients) I've tried to define the fields like this:

    [Association(IsForeignKey=true)]
    public int CaseYear { get; set; }
    
    [Association(IsForeignKey=true)]
    public int CaseSequenceNumber { get; set; }
    
    [Association(IsForeignKey=true)]
    public int DocumentNumber { get; set; }
    

     

    The problem is, I get a "Object reference not set to an instance of an object." error when I try creating the database. However, if I specify the Column attribute in the other table (SenderRecipients) istead of Association-attributes, it works (but then the columns isn't set as foreign keys in the database - which is what I want).

    What is the "correct" way to specify a composite foreign key in a one to many-relationship?

    Thanks in advance.

    Monday, July 13, 2009 1:15 PM