Maintaining Referential Integrity on Insert with NHibernate

Question

I have figured out a lot about nhibernate, but this seems to be my final hurdle of not understanding what is going on. So here is the problem.

I have a basic databas structure:

Shows:
ID [Pk]
CountryID [Fk]
Name

Countries:
ID [Pk]
Name

CountryID has a foriegn key reference from shows to countries table primary key, and countries is already populated with preset values that will rarely change.

Here is my Mapping Files

    public ShowMap()
    {
        Table("Shows");

        Id(x => x.ID);
        Map(x => x.Name)

        HasOne<Country>(x => x.CountryOrigin)
            .Cascade.All()
            .ForeignKey("CountryID");
    }

    public CountryMap()
    {
        Table("Countries");

        Id(x => x.ID);
        Map(x => x.Name);
    }

I am almost sure it is a mapping problem, but when I do an insert into shows and I have a country attached to it. It tries to insert a new country into the database instead of using an existing one in the database already. Which seems to be the main problem. I don't know how to do an insert properly where it uses an existing record in the DB.

Finally, here is an example of me trying to do an insert not know really what to do.

        using (var tx = _session.BeginTransaction())
        {

            Show s1 = new Show();
            s1.CountryOrigin = new Country { ID = 2, Name = "Japan" };
            s1.Name = "Liar Game";

            _session.SaveOrUpdateCopy(s1);
            tx.Commit();
            tx.Dispose();
            return true;
        }

So the question is how can I insert a new show and have it reference an existing record in the countries table?

---

Update 1 I have reworked it to use a has many relationship because I was reading that a has one probably isn't the right way to go. Still have same problem, here is the code changes. These also reflect code changes in comments.

Insertion Code:

        using (var tx = _session.BeginTransaction())
        {

            Show s1 = new Show();
            s1.CountryOrigin.Add(_session.Get<Country>(2));
            s1.Name = "Liar Game";

            _session.SaveOrUpdateCopy(s1);
            tx.Commit();
            return true;
        }

Mappings:

    public ShowMap()
    {
        Table("Shows");

        Id(x => x.ID);
        Map(x => x.Name)

        HasMany<Country>(x => x.CountryOrigin)
            .KeyColumn("ID")
            .Cascade.SaveUpdate();
    }

    public CountryMap()
    {
        Table("Countries");

        Id(x => x.ID);
        Map(x => x.Name);
    }

Still getting the cannot insert null into CountryID like was mentioned in one of the comments.

Update 2 Doing some a bit of testing/debugging the:

            s1.CountryOrigin.Add(_session.Get<Country>(2));

Does what it is supposed to do and gets the correct country, but the problem happens on insert. So this makes me think it is more mapping problem.

Answer 1

You need to use Session.Load to get your Country object:

s1.CountryOrigin = _session.Load<Country>(2);

Session.Load says "I know that a record exists in the db with this ID. Create a proxy of that object for me, without bothering to go to the database". You could also use Session.Get, but that would create an extra db trip.

Answer 2

You have a one-to-many relationship between country and show with show on the many side. So Country should be mapped using References:

public ShowMap()
{
    Table("Shows");

    Id(x => x.ID);
    Map(x => x.Name)

    References(x => x.CountryOrigin, "CountryID");
}