NHibernate with mapping by code and a SQLite database: saving many-to-one parent-child entities, child gets a null foreign key

Question

Variations of this question have been asked and answered many times and the answers have a lot of overlapping details. I have tried so many different things suggested by these answers, but none of them have worked in my case.

I have a SQLite database with a parent table and a child table. It's a really simple setup. I'm using NHibernate 4.0.4 with mapping by code instead of fluent as it was suggested to me that the former is newer and an improvement over the latter.

ENTITIES:

public class BillingItem
{
    public virtual int ID { get; set; }
    public virtual string Name { get; set; }
    // ... other properties
    public virtual ICollection<PaymentItem> PaymentItems { get; set; }

    public BillingItem()
    {
        PaymentItems = new List<PaymentItem>();
    }
}

public class PaymentItem
{
    public virtual int ID { get; set; }
    public virtual BillingItem OwningBillingItem { get; set; }
    // ... other properties
}

BillingItem MAPPING:

public class BillingItemMapping : ClassMapping<BillingItem> 
{
    public BillingItemMapping()
    {
        Table("BillingItems");
        Lazy(true);
        Id(x => x.ID, map => map.Generator(Generators.Identity));

        Set(x => x.PaymentItems, c =>
            {
                c.Key(k =>
                    {
                        k.Column("ID");
                        k.ForeignKey("BillingItemID");
                    });
                c.Inverse(true);
                c.Cascade(Cascade.None);
            },
            r => r.OneToMany(o => { }));

        Property(x => x.Name);
        // ... other properties
    }
}

PaymentItem MAPPING:

public class PaymentItemMapping  : ClassMapping<PaymentItem> 
{
    public PaymentItemMapping()
    {
        Table("PaymentItems");
        Lazy(true);
        Id(x => x.ID, map => map.Generator(Generators.Identity));

        ManyToOne(x => x.OwningBillingItem, m =>
            {
                m.Column("ID");
                m.Update(false);
                m.Insert(false);
                m.Cascade(Cascade.None);
                m.Fetch(FetchKind.Join);
                m.NotFound(NotFoundMode.Exception);
                m.Lazy(LazyRelation.Proxy);
                m.ForeignKey("BillingItemID");
            });

        Property(x => x.DueDate, map => map.NotNullable(true));
        // ... other properties.
    }
}

REPOSITORY:

public void Add(BillingItem toAdd)
{
    using (ISession session = Helpers.NHibernateHelper.OpenSession())
    using (ITransaction tran = session.BeginTransaction())
    {
        session.Save(toAdd);

        foreach (var pi in toAdd.PaymentItems)
        {
            session.Save(pi);
        }

        tran.Commit();
    }
}

BUSINESS LOGIC:

var bi = new BillingItem()
{
    Name = Guid.NewGuid().ToString(),
    // ... others..
};

var pi = new PaymentItem()
{
    OwningBillingItem = bi,
    DueDate = DateTime.Now.AddDays(3)
    // ... others..
};

bi.PaymentItems.Add(pi);
var repo = new Repository();
repo.Add(bi);

As suggested by this answer (and this and this and many, many others), I have made sure to set the Inverse(true) in my Set (child collection) in BillingItemMapping. I have also set my bi-directional references in the PaymentItem object:

OwningBillingItem = bi

and BillingItem object:

bi.PaymentItems.Add(pi);

I feel I've setup everything else the way it should be, and I've tinkered around with a lot of the mapping settings based on suggestions from various other sources. However, I just can't figure out why it's not working.

The problem is, I can't get the foreign key column on the PaymentItem record to hold the ID from the BillingItem. If I set the column to not allow nulls (which is the way it should be), I get a null constraint exception. If I set it to allow nulls (for testing), it just gets set to null (obviously).

What am I doing wrong?

Answer 1

Hehe, there is something wrong with your PaymentItemMapping , the correct mapping should be like this:

public class PaymentItemMapping : ClassMapping<PaymentItem> {

    public PaymentItemMapping() {
        Table("PaymentItems");
        Lazy(true);
        Id(x => x.ID, map => map.Generator(Generators.Identity));

        ManyToOne(x => x.OwningBillingItem, m => {
            //Do not map to m.Column("ID");
            m.Column("BillingItemID");
            // BillingItemID can be insert and update
            m.Update(true);
            m.Insert(true);
            m.Cascade(Cascade.None);
            m.Fetch(FetchKind.Join);
            m.NotFound(NotFoundMode.Exception);
            m.Lazy(LazyRelation.Proxy);
            m.ForeignKey("BillingItemID");
        });

        Property(x => x.DueDate, map => map.NotNullable(true));
        // ... other properties.
    }
}

Answer 2

One part of the mapping, which seems to be wrong, is a column of a set

// BillingItemMapping()
Set(x => x.PaymentItems, c =>
{
    // this should refer to column where parent id will be found
    c.Key(k =>
    {
        k.Column("ID");
        k.ForeignKey("BillingItemID");
    });

so it should be

    c.Key(k =>
    {
        k.Column("BillingItemID");
    });

Foreign Key is just for sql generators.. it could be skipped now

Also, for every collection I use cascading

Set(x => x.PaymentItems, c =>
{
    c.Key(k =>
    {
        k.Column("BillingItemID");
    });
    c.Inverse(true);
    //c.Cascade(Cascade.None);
    c.Cascade(Cascade.All);
},

With that in place, we can simplify call to persist that all

using (ISession session = Helpers.NHibernateHelper.OpenSession())
using (ITransaction tran = session.BeginTransaction())
{
    session.Save(toAdd);

    //foreach (var pi in toAdd.PaymentItems)
    //{
    //    session.Save(pi);
    //}

    tran.Commit();
}

(we still need to keep both references parent-child / child-parent)

And finally - when we have cascade in place - we MUST allow NHibernate to do its job - to INSERT and UPDATE that relation

ManyToOne(x => x.OwningBillingItem, m =>
{
    m.Column("ID");
    // this is stopper - DO NOT use it
    //m.Update(false);
    //m.Insert(false);

So, do not set Update(false) and Insert(false) if we want to be Updated and Inserted. That should solve that all..

This could also get some insight:

Minimal and correct way to map one-to-many with NHibernate