For my last project we used Oracle 10g and NHibernate ORM framework (the project was started 4-5 yars ago, long before LINQ and ADO.NET Entity Framework). I had a database table which holds payment information along with scanned images of the payment documents. Usually such tables are mapped with a class fully corresponding to the table fields. The usual approach works well but has a performance problem: when the table needs to be displayed, the binary images holding the payment documents do not need to be loaded from the database because they consume much memory and take time to load. NHibernate supports lazy loading for associations only but does not support lazy column / property loading. To achieve a normal performance I needed to do a quick hack and I believe it could be useful for other projects so I want to shera it with the community.

The hack is to map all columns of the database table except the large binary column (BLOB type in Oracle). The large binary column is handle differently with additional select / update operations. See the payment entity definition below:

using System;
using System.Collections.Generic;
using System.Xml.Serialization;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Mapping.Attributes;

namespace Example
{
    [Class(0, Table = "PAYMENTS")]
    [XmlType("payment")]
    public class Payment
    {
        private int id;
        private DateTime paymentDate;
        private decimal paymentAmmount;
        private string paymentRefNumber;
        private string paymentDocumentFileName;

        private static Dao<payment> dao = new Dao</payment><payment>();

        [Id(1, Column = "ID", Type = "System.Int32"),
            Generator(2, Class = "native"), Param(3, Content = "SQ_PAYMENTS", Name = "sequence")]
        [XmlElement("id")]
        public virtual int ID
        {
            get { return id; }
            set { id = value; }
        }

        [Property(Column = "PAYMENT_DATE", Type = "System.DateTime")]
        [XmlElement("paymentDate")]
        public virtual DateTime PaymentDate
        {
            get { return paymentDate; }
            set { paymentDate = value; }
        }

        [Property(Column = "PAYMENT_AMMOUNT", Type = "System.Decimal")]
        [XmlElement("ammount")]
        public virtual decimal PaymentAmmount
        {
            get { return paymentAmmount; }
            set { paymentAmmount = value; }
        }

        [Property(Column = "PAYMENT_REF_NUMBER", Type = "System.String")]
        [XmlElement("paymentRefNumber")]
        public virtual string PaymentRefNumber
        {
            get { return paymentRefNumber; }
            set { paymentRefNumber = value; }
        }

        [Property(Column = "PAYMENT_DOC_FILE_NAME", Type = "System.String")]
        [XmlElement("paymentDocumentFileName")]
        public virtual string PaymentDocumentFileName
        {
            get { return paymentDocumentFileName; }
            set { paymentDocumentFileName = value; }
        }

        public static IList</payment><payment> GetAll()
        {
            return dao.GetAll("paymentDate");
        }

        public byte[] PaymentDocument
        {
            // Implement lazy loading for the PAYMENT_DOC blob (for better performance)
            get
            {
                ISession hbSession = NHibernateSessionManager.Instance.GetSession();
                ISQLQuery query = hbSession.CreateSQLQuery("SELECT PAYMENT_DOC FROM PAYMENTS WHERE ID=:payment_id");
                query.SetParameter("payment_id", this.ID);
                byte[] paymentDoc = (byte[])query.UniqueResult();
                return paymentDoc;
            }
            set
            {
                if (this.ID == 0)
                {
                    throw new Exception("Can not assing PaymentDocument for non-persistent payments!");
                }
                ISession hbSession = NHibernateSessionManager.Instance.GetSession();
                ISQLQuery query = hbSession.CreateSQLQuery(
                    "UPDATE PAYMENTS SET PAYMENT_DOC=:payment_doc WHERE ID=:payment_id");
                query.SetParameter("payment_id", this.ID);
                query.SetParameter("payment_doc", value);
                query.ExecuteUpdate();
            }
        }

        public static Payment Save(Payment payment)
        {
            return dao.Save(payment);
        }
    }
}

The database schema of the PAYMENTS table is as follows:

CREATE TABLE PAYMENTS(
  ID NUMBER(6,0) NOT NULL,
  PAYMENT_DATE DATE NOT NULL,
  PAYMENT_AMMOUNT NUMBER(10,2) NOT NULL,
  PAYMENT_REF_NUMBER VARCHAR2(50),
  PAYMENT_DOC BLOB,
  PAYMENT_DOC_FILE_NAME VARCHAR2(50),
  PRIMARY KEY (ID)
)

I use NHibernate annotations because I want to have the entity class and the .hbm mapping in the same file.

The idea behind the “column lazy loading” hack is to skip mapping the large BLOB column and to red/write it on demand with additional SELECT / UPDATE command.
This approach is applicable to other ORM frameworks that do not support lazy column loading.

Enjoy.