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.

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>

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">