How to use generated Guid primary key as foreign key while inserting into child table in NHibernate 3.2?

Question

I am trying to persist an object into the database. This operation should touch two tables.

[HttpPost]
public ActionResult Create(Report report)
{
    try
    {
        report.Positions = new Iesi.Collections.Generic.HashedSet<Position>();
        var desks = this.session.Query<Desk>().ToList();

        foreach (var desk in desks)
        {
            foreach (var comm in desk.Commodities)
            {
                report.Positions.Add(
                    new Position
                        {
                            Report = report,
                            ReportId = report.Id,
                            Desk = desk,
                            DeskId = desk.Id,
                            Commodity = comm,
                            CommodityId = comm.Id,
                            Value = .0
                        });
            }
        }

        this.session.Save(report);
        return this.RedirectToAction("Index");
    }
    catch
    {
        // some handling
        return this.RedirectToAction("Index");
    }
}

This is my mapping:

public class EntityMapping<TKey, TEntity> : ClassMapping<TEntity>
    where TEntity : Entity<TKey>
{
    public EntityMapping()
    {
        this.Id(x => x.Id, mapper => mapper.Generator(Generators.GuidComb));
    }
}

public class PositionMapping : ClassMapping<Position>
{
    public PositionMapping()
    {
        this.Table("REPORTPOSITIONS");
        this.ComposedId(
            x =>
            {
                x.Property(p => p.ReportId);
                x.Property(p => p.DeskId);
                x.Property(p => p.CommodityId);
            });
        this.Version(x => x.Version, mapper => mapper.Generated(VersionGeneration.Always));
        this.Property(x => x.Value, mapper => mapper.Column("Position"));
    }
}

public class ReportMapping : EntityMapping<Guid, Report>
{
    public ReportMapping()
    {
        this.Table("REPORTS");
        this.Property(x => x.ReportDate, mapper => mapper.Type(NHibernateUtil.Date));
        this.Set(
            x => x.Positions,
            mapper =>
            {
                mapper.Key(km => km.Column("ReportId"));
                mapper.Lazy(CollectionLazy.Lazy);
                mapper.Inverse(true);
                mapper.Cascade(Cascade.All | Cascade.DeleteOrphans);
            },
            rel => rel.OneToMany());
    }
}

and this is the sql used by nhibernate:

INSERT INTO REPORTS
        (ReportDate,
         Id)
VALUES  ('2012-06-11T00:00:00.00' /* @p0_0 */,
         '7f4d8f3d-1175-4713-bd1c-a06d00bfc614' /* @p1_0 */)

INSERT INTO REPORTPOSITIONS
        (Position,
         CommodityId,
         DeskId,
         ReportId)
VALUES  (0 /* @p0_0 */,
         '3a7d80c4-85e9-ba4b-80d2-064f7f0b58b5' /* @p1_0 */,
         'ed7c4e75-7417-a241-a40a-0ff4bfad7172' /* @p2_0 */,
         '00000000-0000-0000-0000-000000000000' /* @p3_0 */)

The ReportId in the sql statement is C#'s default(GUID), because i have not set it in the controller action. NHibernate generates the id for the reports table. When I set the Id in the controller action

report.Id = Guid.NewGuid();

the sql uses different ids as parameters:

INSERT INTO REPORTS
        (ReportDate,
         Id)
VALUES  ('2012-06-11T00:00:00.00' /* @p0_0 */,
         '6164264e-29cd-4d9c-befd-a06d00c2defd' /* @p1_0 */)

INSERT INTO REPORTPOSITIONS
        (Position,
         CommodityId,
         DeskId,
         ReportId)
VALUES  (0 /* @p0_0 */,
         '3a7d80c4-85e9-ba4b-80d2-064f7f0b58b5' /* @p1_0 */,
         'ed7c4e75-7417-a241-a40a-0ff4bfad7172' /* @p2_0 */,
         '594b2206-7c25-4430-af18-5f2643f6c7bf' /* @p3_0 */)

How do I use the id generated by NHibernate for the second table?

UPDATE:

I tried to map the ManyToOne part explicitly

this.ManyToOne(x => x.Report, mapper => mapper.Column("ReportId"));

but with this it does not even try to insert into reportpositions;

When I instead do something like this

this.ManyToOne(x => x.Report, mapper => mapper.Column("foo"));

it creates this sql statement

INSERT INTO REPORTS
        (ReportDate,
         Id)
VALUES  ('2012-06-11T00:00:00.00' /* @p0_0 */,
         '4ff74d49-8749-400c-b079-a06d00e0bee5' /* @p1_0 */)

INSERT INTO REPORTPOSITIONS
        (foo,
         Position,
         CommodityId,
         DeskId,
         ReportId)
VALUES  ('4ff74d49-8749-400c-b079-a06d00e0bee5' /* @p0_0 */,
         0 /* @p1_0 */,
         '3a7d80c4-85e9-ba4b-80d2-064f7f0b58b5' /* @p2_0 */,
         'ed7c4e75-7417-a241-a40a-0ff4bfad7172' /* @p3_0 */,
         '00000000-0000-0000-0000-000000000000' /* @p4_0 */)

now foo has the correct key. Could someone please explain this behavior and offer a solution?

Stefan Steinegger · Answer 1 · 2012-06-12T05:59:43.477

2

I would most probably map it as a composite element and get rid of the whole id problem. You may create a unique constraint if you want to have it checked.

this.Set(
        x => x.Positions,
        mapper =>
        {
            mapper.Key(km => km.Column(c => 
            {
              c.Name("ReportId");
              c.UniqueKey("ReportDeskCommodity");
            }));
            mapper.Lazy(CollectionLazy.Lazy);
        },
        rel => rel.Component(comp => 
        {
            comp.Parent(p => p.Report);

            comp.Property(
              p => p.DeskId, 
              m => m.Column(c => c.UniqueKey("ReportDeskCommodity")));

            comp.Property(
              p => p.CommodityId, 
              m => m.Column(c => c.UniqueKey("ReportDeskCommodity")));

            comp.Property(
              x => x.Value, 
              m => m.Column("Position"));
        }));

You actually could make it many-to-one, if there is no good reason to map the keys. This is a change on your domain model.

        rel => rel.Component(comp => 
        {
            comp.Parent(p => p.Report);

            comp.ManyToOne(
              p => p.Desk, 
              m => m.Column(c => 
              {
                c.Name("DeskId");
                c.UniqueKey("ReportDeskCommodity");
              }));


            comp.ManyToOne(
              p => p.Commodity, 
              m => m.Column(c => 
              {
                c.Name("CommodityId");
                c.UniqueKey("ReportDeskCommodity");
              }));

            comp.Property(
              x => x.Value, 
              m => m.Column("Position"));
        }));

edited Jun 12 '12 at 05:59

answered Jun 11 '12 at 10:14

Stefan Steinegger

63,782
15
129
193

I tried this approach and it first complained about multiple mapping of column "ReportId" (1. `mapper.Key(km => km.Column("ReportId"));` 2.`comp.Property(p => p.ReportId);` After removing the second mapping it only inserts into the REPORTS table – mrt181 Jun 11 '12 at 10:50
I didn't recognize the backreference. You need to map it as `Parent`. The property is a reference to Report, not an id. It must not be inverse, because it is not inverse. This is the reason why it doesn't store anything (the inverse means that there is another reference in memory which contains the same information, so storing is not necessary.) – Stefan Steinegger Jun 11 '12 at 12:23
Using your code an removing the Parent setting - it threw a MappingException - makes the inserts in both tables work. But when i now use `session.Get(1)` the Position object in the set does not contain/fetch the desk and commodity objects. Any idea how to resolve this too? – mrt181 Jun 11 '12 at 12:45
When you added them correctly before storing and the given mapping is exactly what you have (no additional DeskId properties and stuff as in your code at the top), it should work. By the way, I fixed the unique key and added column names for the foreign keys. – Stefan Steinegger Jun 12 '12 at 05:58
Using your updated this.Set and Component mapping I still get a MappingException `Could not determine type for: Report, MyProject, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null, for columns: NHibernate.Mapping.Column(Report)` because of this line `comp.Parent(p => p.Report);` But without this the Positions collection remains empty when i try to read a report from the database. i can add a new one and it inserts all rows correctly but can't fetch it afterwards -> `could not reassociate uninitialized transient collection` – mrt181 Jun 12 '12 at 06:23
According to Google, this is a transient entity problem. I've never seen this error. There must be something weird in the session handling or the way how you create the objects. – Stefan Steinegger Jun 12 '12 at 09:57

score 0 · Accepted Answer · answered Jun 12 '12 at 05:59

After some fiddling around and climbing NHibernates steep learning curve I came up with this solution.

public class PositionMapping : ClassMapping<Position>
{
    public PositionMapping()
    {
        this.Table("REPORTPOSITIONS");
        this.ComposedId(
            x =>
            {
                x.ManyToOne(p => p.Report, mapper => mapper.Column("ReportId"));
                x.ManyToOne(p => p.Desk, mapper => mapper.Column("DeskId"));
                x.ManyToOne(p => p.Commodity, mapper => mapper.Column("CommodityId"));
            });
        this.Version(x => x.Version, mapper => mapper.Generated(VersionGeneration.Always));
        this.Property(x => x.Value, mapper => mapper.Column("Position"));
    }
}

I declared the ComposedId parts as ManyToOne and removed the Guid properties from the Position entity. NHibernate now populates the Report, Desk and Commodity properties in the Position entity according to table REPORTPOSITIONS and also persits all Positions to this table.

You should avoid composite ids. This feature is meant to be used for legacy databases. — Stefan Steinegger, Jun 12 '12 at 08:42
Interesting, is the use of ComposedId discouraged in NHibernate? In my example it makes sense to me to use it like this and it works. — mrt181, Jun 12 '12 at 09:14

How to use generated Guid primary key as foreign key while inserting into child table in NHibernate 3.2?

2 Answers2