Multiple Foreign Key references in a Part / Record?

Topics: General, Writing modules
Oct 29, 2012 at 6:41 AM

Hi guys -

I'm trying to work out the best way to do this ... I have a foreign key table, "TeamPartRecord". Standard. I have a second table, "TeamEventPartRecord", that I want to have TWO foreign key references to "TeamPartRecord": HomeTeam (required) and AwayTeam (optional).

(Basic idea: a team has 'events', like games - or practices. There is always a "home" team, and there is sometimes - for games - an away team. These should both be referenced from the TeamPartRecord table).

For TeamEventPartRecord, I don't think this will work:

// Record class:
public virtual TeamPartRecord HomeTeam { get; set; }    
public virtual TeamPartRecord AwayTeam { get; set; }

// Part class:
public TeamPartRecord HomeTeam
    {
      get { return Record.HomeTeam; }
      set { Record.HomeTeam = value; }
    }

public TeamPartRecord AwayTeam
    {
      get { return Record.AwayTeam; }
      set { Record.AwayTeam = value; }
    }

// migration class ... ??

So I looked into SchemaBuilder.CreateForiegnKey( .. ). So something like this:

// record class:
public virtual int HomeTeam_Id { get; set; }    
public virtual int? AwayTeam_Id { get; set; }

// part class: not sure ..
public int HomeTeam_Id    
{     
 get { return Record.HomeTeam_Id; }      
set { Record.HomeTeam_Id = value; }    }

 public int? AwayTeam_Id    
{      
get { return Record.AwayTeam_Id; }      
set { Record.AwayTeam_Id = value; }    }

// migration: SchemaBuilder.CreateForeignKey("FK_HomeTeam", "TeamEventPartRecord", new string[] {"HomeTeam_Id"}, "TeamPartRecord", new string[] {"Id"}); SchemaBuilder.CreateForeignKey("FK_AwayTeam", "TeamEventPartRecord", new string[] { "AwayTeam_Id" }, "TeamPartRecord", new string[] { "Id" });

 

I dunno, doesn't seem right .. I've created lots of normal foreign key relationships in Orchard, but this is the first time I've need two references to the same table, thereby forcing me out of doing it the way I know how :-(

Any help is greatly appreciated. Thanks.

Kurt Mang

Developer
Oct 29, 2012 at 9:00 AM

Foreign key constraints are nice, but they aren't required in order for navigation properties to work.
You can also skip the HomeTeam_Id and AwayTeam_Id properties on the TeamEventPartRecord.

All you really have to do to make this work is create the "TeamEventPartRecord" table with two columns of type int: "HomeTeam_Id" and "AwayTeam_Id" (and of course the Id column as created by the ContentPartRecord() method).

Orchard (or NHibernate) will know that the columns "HomeTeam_Id" need to be mapped to the "HomeTeam" property on your record class. Same goes for AwayTeam.

In case you do want to define the foreign key constraints, I think your code looks correct to me. Are you getting errors or seeing unexpected behavior?

 

Oct 29, 2012 at 4:46 PM

Thanks for the reply. So, on TeamEventPartRecord, if I define the usual foreign key type references, i.e.

public virtual TeamPartRecord HomeTeam { get; set; }
public virtual TeamPartRecord AwayTeam { get; set; }

// With a corresponding Part class:
// TeamEventPart:
public TeamPartRecord HomeTeam
    {
      get { return Record.HomeTeam; }
      set { Record.HomeTeam = value; }
    }

public TeamPartRecord AwayTeam
    {
      get { return Record.AwayTeam; }
      set { Record.AwayTeam = value; }
    }

... with a migration class specify (off the top of my head, syntax may be wonky):

table => table
.Column<int>("HomeTeam_Id", col => col.NotNull())
.Column<int>("AwayTeam_Id", col => col.Nullable())

.. and the navigation properties on my ContentPartRecord (and hence ContentPart (TeamEventPart)) will automatically be populated?

Rad!

And the ForeignKey refs are a nice to have but only necessary for referential integrity and maybe indexing, correct?

I'll give it a shot tonight - thanks for the feedback. I'll post the working solution here for future generations of Orchard users!

Kurt

Developer
Oct 29, 2012 at 11:21 PM
Edited Feb 19, 2013 at 12:42 AM
kurtmang wrote: And the ForeignKey refs are a nice to have but only necessary for referential integrity and maybe indexing, correct? Correct. You could run into a problem with the not nullable column. If Orchard starts throwing exceptions about not being able to create records because the column does not allow nulls, simply change the column to accept null values.
Feb 18, 2013 at 11:42 PM
Hi gang - quick reply for posterity - sfmskywalker was correct, those foreign keys have to be defined as nullable or else NHibernate provides "can't insert null value" errors and rolls back the transaction. Making the FKs nullable does the trick!