Mapping a collection of localization entities used by many entities with Fluent NHibernate

1
votes

I am working with a legacy database and I am trying to map some kind of localization table with Fluent NHibernate.

The table looks like this:

DataName
[PK] TableName
[PK] FieldName
[PK] Id
[PK] LangId
Description

This table is mapped through this entity:

public class DataName : EntityBase
{
    public virtual string TableName { get; set; }
    public virtual string FieldName { get; set; }
    public virtual int Id { get; set; }
    public virtual Language Language { get; set; }
    public virtual string Description { get; set; }

    ...
}

public class DataNameMap : ClassMap
{
    public DataNameMap()
    {
        Table("zDataName");

        CompositeId().KeyProperty(x => x.TableName)
            .KeyProperty(x => x.FieldName)
            .KeyProperty(x => x.Id)
            .KeyProperty(x => x.Language, "LangId").CustomType();

        Map(x => x.Description).Column("Descr").Length(2000);
    }
}

I have multiple entities mapped to the "DataName" entity for the purpose of localization and the mapping looks like this:

public class Carrier
{

    public virtual int CarrId { get; set; }
    public virtual string CarrCode { get; set; }
    public virtual List DataNameList { get; set; }

    ...
}

public class CarrierMap : ClassMap
{
    public CarrierMap()
    {
        Table("Carriers");

        Id(x => x.CarrId).GeneratedBy.Identity();
        Map(x => x.CarrCode).Not.Nullable().Length(8);

        HasMany(x => x.DataNameList)
            .KeyColumn("Id")
            .Where("TableName = 'carriers' AND FieldName = 'name'")
            .Inverse()
            .Cascade.AllDeleteOrphan();

        ...
    }
}

The "DataName" table contains records that looks like the following:

TableName       FieldName      Id        LangId      Description
---------       ---------      --        ------      -----------
Carriers        Name           1000      1           English description
Carriers        Name           1000      2           French description

I can retrieve this information from the database without any problem, but when comes the time to save a new entity (insert or update) to the database, NHibernate dosen't perform the operations in the right order.

INSERT INTO DataName (TableName, FieldName, LangId, Id, Description) VALUES ('Carriers', 'Name', 1, NULL, 'English description')
INSERT INTO DataName (TableName, FieldName, LangId, Id, Description) VALUES ('Carriers', 'Name', 2, NULL, 'French description')

INSERT INTO Carriers....

For the purpose of this example, let's say the id generated by the database is 2000.

UPDATE DataName SET Id = 2000
WHERE TableName = 'Carriers' AND FieldName = 'Name' AND LangId = 1 AND Id IS NULL
UPDATE DataName SET Id = 2000
WHERE TableName = 'Carriers' AND FieldName = 'Name' AND LangId = 2 AND Id IS NULL

I have tried playing with the Inverse() option of my HasMany mapping for the DataNameList collection, but it doesn't seem to have any effect on this particular situation.

How do I tell NHibernate to persist the "Carrier" entity and then the collection of "DataName" without having to issue additional UPDATE statement after the "Carrier" entity is inserted?

1
.netnhibernateormfluent-nhibernatemapping

1 Answers

1
votes

In this scenario (maybe related to legacy database) NHibernate is working correctly, as expected. I will try explain why. We have to observe the FLUSH Ordering and the double usage of one column:

Firstly, the order of session flushing.

NHibernate documentation sais:

9.6 Flush ( http://nhibernate.info/doc/nh/en/index.html#manipulatingdata-flushing )

...

The SQL statements are issued in the following order

  • all entity insertions, in the same order the corresponding objects were saved using ISession.Save()
  • all entity updates
  • all collection deletions
  • all collection element deletions, updates and insertions
  • all collection insertions
  • all entity deletions, in the same order the corresponding objects were deleted using ISession.Delete()

Secondly, the double column mapping.

Snippets shown above use One column: Id (in table DataName) for two purposes: I. as a primary key (and this will be handled during the INSERT statement)

CompositeId().KeyProperty(x => x.TableName)
  .KeyProperty(x => x.FieldName)
  .KeyProperty(x => x.Id)
  ...

II. as a reference two entity Carrier

HasMany(x => x.DataNameList)
.KeyColumn("Id")
...

Finally : what happens

Given that, the answer is: NHibernate must INSERT all the entities in the first round. If there will be some many-to-one mapping it could be done as part of INSERT as well. So DataNames are inserted and Carrier as well.

But in our example, we also need to persist Collection (the DataNameList). So now NHibernate updates all (just inserted) collection items to be provided with reference to the owner (Carrier)

Suggestion

So this is why I would mark this observed behavior as "the expected one". To avoid the UPDATE, we can set the list mapping to cascade="none" and manually manage the persistence (e.g. Call Session.Save(Carrier) then assign Carrier to every DataName and call Session.Save(DataName),

...

EDIT: extended suggestion

I. settings producing INSERT and UPDATE

With this settings I was able to repeat the behavior you've experienced C# class carrier mapping to DataName

private IList<DataName> _dataNames;
public virtual IList<DataName> DataNames
{
  get { return _dataNames ?? (_dataNames = new List<DataName>()); }
  set { _dataNames = value; }
}

XML mapping (The same as your fluent, but inverse its false)

<bag name="DataNames" inverse="false" cascade="all-delete-orphan"
  where="TableName = 'carriers' AND FieldName = 'name'" >
  <key column="Id" />
  <one-to-many class="DataName" />
</bag>

And the code which results in the same issues, you've described above:

// create 2 DataNames
DataName dn1 = CreateNew(LangId = 1, "English");
DataName dn2 = CreateNew(LangId = 2, "French");

// insert them into collection
carrier.DataNames.Add(dn1);
carrier.DataNames.Add(dn2);

// persist them all
session.Save(carrier);

And this results in INSERTS with UPDATES

II. working settings, just with INSERT

If I make these changes: 1) change inverse into true inverse="true" 2) set the DataName.Id explicitly, based on its holder (carrier)

// carrier ID must be get from the server, because of identity
session.Save(carrier);

// explicity setting of the ID
dn1.Id = carrier.ID;
dn2.Id = carrier.ID;

// inverse set to false 
carrier.DataNames.Add(dn1);
carrier.DataNames.Add(dn2);

// Update via the carrier entity, will trigger INSERT
session.Update(carrier);

Then only INSERTS statements are applied. Could this help?

Note: If the ID won't be generated on a SQL Server (identity), but assinged by NHibernate (HiLo), the round-trip could be reduced.