Foreign key relationships

Topics: Writing modules
Jun 2, 2011 at 1:58 PM
Edited Jun 2, 2011 at 2:03 PM

This is a 2-part question, one involves working with data and foreign key relationships in Orchard, the other is about best practice for using parts vs. straight up records.

Here's a brief overview to put some things in context.  My goal is to write a module that allows users to register for the site via a specific url, something like /ninjas/register or /pirates/register, and upon successful registration, they are immediately placed into that role.  Another goal is registering existing users for a given role via this module, but I'll burn that bridge when I get there.

So, along with the front-end register page which I got working,  I need an admin interface that allows the user to specify a friendly registration name and select a role to use for that name.

My first attempt was to create a flat table that had an Id, RegistrationName (ninja/ pirate, etc), and a RoleName.  Here was my data migration for that method:

public int Create()
                table => table
                    .Column<int>("Id", column => column.PrimaryKey().Identity())

            return 1;

I was able to get this working, and now I want to clean up the design a little by making that RoleName column a RoleId and set it as a foreign key to the Orchard_Roles_RoleRecord table.  I've been reading the 1-N relations article ( it showed some syntax that seems to work by convention to join to another table in your module.  Something like this:


public int Create()
                table => table
                    .Column<int>("RoleRecord_Id") // foreign key to role table

            return 1;

So, onto the questions:

1.) Is there anything special I have to do to get that RoleRecord_Id to look for it in the Orchard_Roles_RoleRecord table? I know that this is done by convention with the underscore separating the two tables, but I'm not quite sure how to make this jive with an existing table.

2.) After perusing the 1-N article, I got even more confused because it looks like they are using ContentPartRecords there as opposed to straight up non-contentpart record as indicated in my first migration example. When is it better to use one or the other? If I use ContentParts, does that mean I'll need drivers/handlers? I still haven't been able to wrap my head around those concepts.

Your feedback is appreciated...

Jun 2, 2011 at 6:21 PM
Edited Jun 2, 2011 at 6:25 PM

After reading some more, I think I understand the are used when you are creating content for the site, such as a new content type (the Address in the 1-N example is a part because you want to attach it to a content type such as a "Customer."

In my case, I don't need a part because the RoleRegistration is just a standard entity that isn't going to be attached to a piece of content.

So, I think I need some of the fundamentals taken from the 1-N article, but not the content part stuff.  It's slowly clicking, I think...

So, the current error I'm seeing occurs when I'm rendering the view for the admin containing the list of's telling me invalid column name. I'm off to dig into this one...

Jun 2, 2011 at 6:46 PM
Edited Jun 2, 2011 at 6:46 PM

Can you show your code for RegistrationPartRecord itself, and also the admin code where you're rendering the view?

Jun 2, 2011 at 6:55 PM


namespace RoleRegistration.Models
    public class RegistrationRecord
        public virtual int Id { getset; }
        public virtual string FriendlyName { getset; }
        public virtual TempRoleRecord RoleRecord { getset; }

TempRoleRecord.cs (this will eventually need to use the built-in orchard roles table, but I wanted to do new table first to see if I could make it work that way)

namespace RoleRegistration.Models
    public class TempRoleRecord
        public virtual int Id { getset; }
        public virtual string Name { getset; }

namespace RoleRegistration {
    public class Migrations : DataMigrationImpl {

        private readonly IRepository<TempRoleRecord> _roleRepository;
        private readonly IEnumerable<TempRoleRecord> _roles =
        new List<TempRoleRecord> {
            new TempRoleRecord {Name = "Ninjas"},
            new TempRoleRecord {Name = "Pirates"},
            new TempRoleRecord {Name = "Robots"},
            new TempRoleRecord {Name = "Zombies"}

        public Migrations(IRepository<TempRoleRecord> stateRepository) {
            _roleRepository = stateRepository;

        public int Create()
            SchemaBuilder.CreateTable("RegistrationRecord", table => table
                .Column<int>("Id", column => column.PrimaryKey().Identity())

            SchemaBuilder.CreateTable("TempRoleRecord", table => table
                .Column<int>("Id", column => column.PrimaryKey().Identity())

            if (_roleRepository == null)
                throw new InvalidOperationException("Couldn't find role repository.");
            foreach (var role in _roles)

            return 1;


The problem isnt' in the view, it's got something to do with the sql query that gets's my controller:

public ActionResult Index()
            var model = _respository.Table;
            return View(model);

When I look at the contents of the model after the service call, here's the sql that gets generated:
SELECT            AS id52_0_,
       this_.friendlyname  AS friendly2_52_0_,
       this_.rolerecord_id AS rolerecord3_52_0_
FROM   roleregistration_registrationrecord this_

And the resulting error:
Invalid column name 'rolerecord_id'.
Jun 2, 2011 at 7:02 PM

The only place I have "RoleRecord_ID" was in my previous code...perhaps something from that is lingering around...

Jun 2, 2011 at 7:21 PM

The problem is in your migration. Your column is called "RoleRecord" so the id column should be called "RoleRecord_id", not "TempRoleRecord_Id" - it has nothing to do with the type name (id should be lowercase there also, I'm not sure whether that makes a difference)

Jun 2, 2011 at 7:26 PM
Edited Jun 2, 2011 at 7:26 PM

I just scanned my migration file and I named it TempRoleRecord...I even did a search through the project in VS for RoleRecord_Id and the only thing that was returned was TempRoleRecord_Id (it was previously named RoleRecord_Id, but I renamed it).

What line do you see it named RoleRecord?

Jun 2, 2011 at 7:29 PM
Edited Jun 2, 2011 at 7:31 PM

Yeah, I just verified, the table that gets created has a TempRoleRecord_Id column on it, so it's creating it correctly.  Since I renamed that column, maybe something is still hanging around from that?

I did delete the migration record and dropped the two tables so the create script would re-run, but it seems to still be looking for RoleRecord.

Does it create a mapping file somewhere that I might have to delete?

Jun 2, 2011 at 7:51 PM

Bit by convention again :) The problem was in my RegistrationRecord model...I changed the line:

public virtual TempRoleRecord RoleRecord { getset; }


 virtual TempRoleRecord TempRoleRecord { getset; }

So I'll mess around with this for a bit, and if it all works, I can move onto the other part of my question...Since this uses a convention to find out the table to join to,
how do I make it join to a table outside of the scope of my module (the Orchard_Roles_RoleRecord table, for instance)?

Jun 2, 2011 at 8:32 PM

That's what I was saying; it doesn't use any convention. Your column was called "RoleRecord" so your id had to be "RoleRecord_id". It has nothing to do with the name of the table. It will join to the type of the property. So to join to RoleRecord, your column just needs to be of type RoleRecord.

Jun 2, 2011 at 8:37 PM

I think I was confused because you said the problem was in my migration file, whereas when I found the problem, I felt like my problem was in my model...either way, you're right, I had something named wrong, and since I felt like the model should be updated, I fixed it there.  As for the type thing you mentioned, that makes complete sense.

Thanks for your help and your patience...