How to persist a subset of an object instead of the whole object?

Question

I'm struggling with a NHibernate related problem where I could use some input.

Introduction:

I have a legacy database where the relational concepts have not really been applied.

In the database I have an OrderLine table which contains data for an order lines.

On top of that the table also contains all columns with Order specific information. This could for example be order number of a customer.

E.x. If i have 10 order lines - then I have 10 rows in my OrderLines table and each row has all the Order specific data e.g. order number or customer information.

I did not want to have the above structure in my code so a view was created for Orders so that I could map my Order in NHibernate which then has a set/bag of OrderLines which makes much more sense.

Mapping: (simplified)

<class name="Order" table="[view_Orders]">
    <bag name="OrderLines">
</class>

<class name="OrderLine" table="OrderLines" />

The problem:

The complexity of the view makes it impossible to save to the view. When trying NHibernates throws this exception:

NHibernate.Exceptions.GenericADOException: could not insert: XXX ---> System.Data.SqlClient.SqlException: View or function 'view_Orders' is not updatable because the modification affects multiple base tables.

My NHibernate mapping is constructed as an Order object which has a "set or bag" of OrderLine objects. Ideally I would like NHibernate only to persist the set of OrderLine objects instead of the whole object.

Is there a way of achieving this? I have tried locking the object using different lock modes but it did not help me.

Amalia

It seems like what you're trying to do should work. The exception indicates that NHibernate is trying to execute an UPDATE against your Order entity which is mapped to a non-updatable view. NHibernate will only do that if it thinks the entity is dirty, outside of the OrderLines collections. You should check your other mapped properties. And perhaps try using a profiler like NHProf to see what's changing. – Tarpaulin

Exactly. I would like NOT to update my Order because its mapped to a non-updateable view. Instead i would like to only update the child object i.e. the bag of OrderLines. I have been using NHProfiler and the exception from above is basically a SQL Exception which is passed through NHibernate. – Amalia

Answer 1

You can use mutable="false" to avoid the update and deletes as this article says:

Immutable classes, mutable="false", may not be updated or deleted by the application. This allows NHibernate to make some minor performance optimizations.

To avoid the insert you can use the following statement (Uses the proyection instead an insert command, dont forget use check="none"):

<sql-insert check="none">SELECT 1</sql-insert>

Here is a tested example:

<class name="Order" table="[view_Orders]" mutable="false">
  <id name="OrderId" type="System.Guid">
    <generator class="guid.comb"/> <!-- Change as you need -->
  </id>

  <!-- Other properties -->
  <!-- <property name="GrandTotal"/> -->

  <set name="OrderLines" lazy="true" inverse="true" cascade="all-delete-orphan">
    <key column="OrderId"/>
    <one-to-many class="OrderLine"/>
  </set>

  <sql-insert check="none">SELECT 1</sql-insert>
</class>

<class name="OrderLine" table="OrderLine">
  <id name="OrderLineId" type="System.Guid">
    <generator class="guid.comb"/> <!-- Change as you need -->
  </id>

  <!-- Other properties -->
  <!-- <property name="OrderId"/>
  <property name="GrandTotal"/>/> --> 

</class>

Fideicommissum

Answer 2

In case I do understand your issue, the solution is surprisingly simple. We just would mark root object with dynamic-update="true"

<class name="Order" table="[view_Orders]" dynamic-update="true">
    ...
</class>

And then apply update="false" to every property or reference which we have in that Order class mapped to view:

...
<property name="Code"       update="false"/>
...
<many-to-one name="Country" update="false />

But our collection will need the standard, even cascade mapping:

<class name="Order" table="[view_Orders]" dynamic-update="true">
    <bag name="OrderLines" 
         lazy="true" 
         inverse="true" 
         batch-size="25" 
         cascade="all-delete-orphan" >
      ...
     </bag>
     ... // other stuff is update="false"
</class>

And now code like this would do management of OrderLines, while not executing any updates on the root object Order

var session = ... // get ISession 
// load root
var root = session.Get<Order>(123);

// if needed change existing line (pretend there is one)
root.OrderLines[0].Amount = 100;

// add new
var newOrder = ... // new order
root.OrderLines.Add(newOrder);

session.Save(root);
session.Flush();

And that is it. Cascade on the root object is doing what we need, while the update="false" is not updating it...

NOTE: Just interesting note - there is also class and collection setting mutable="false", but it would not work here... as the solution mentioned above (it is sad, because that would be more elegant, but not working as expected...). See:

19.2.2. Strategy: read only

If your application needs to read but never modify instances of a persistent class, a read-only cache may be used. This is the simplest and best performing strategy. Its even perfectly safe for use in a cluster.

<class name="Eg.Immutable" mutable="false">

Judicial

Hi Radim, Thank you very much for your help. I already tried out setting mutable="false" without any succes. I tried using dynamic-update but i'm still getting the same errors. The error i described above is related to inserts of data and not updates which i believe your example is aimed at if im correct? – Amalia

Yes, as I said mutable="false" is not the solution here. But marking all view related properties as update="false" will work. Just <bag> will be persisted... Because... if you say that you've mapped the view - we have to be talking JUST ABOUT THE UPDATE. So, As you described (if I read it properly) - we need to persist just collection - during the update. In that case, even items of that collection can be INSERTED. But insert of the brand new record into parent view... does not make sense based on your scenario. This is what I checked and is working.. or? – Mackmackay

I mean the part of your question "...Ideally I would like NHibernate only to persist the set of OrderLine objects instead of the whole object...." so, if we do have existing record (the one represented by view, mapped as parent object) we will insert just OrderLines... and that solution will be supported by the above answer. Order already exists (is not updated by NHibernate) all items in OrderLines are fully manageable... – Mackmackay