How to perform a execution time query using Linq to NHibernate

Question

Hello, I am inserting a record into my table, but I want to be able to set my SortOrder field on insert to SELECT MAX(SortOrder) + 1 FROM Category WHERE SiteID = @SiteID. What is the easiest way of doing this?

Here is my data structure:
Category
ID
SiteID
SortOrder
Name

I am using Fluent NHibernate and Linq to NHibernate. Thanks for any help!

Answer 1

Ok, here are 2 ways. The simplest may not involve NHibernate since you're using SQL Server... You could just create an INSTEAD OF INSERT trigger on your table, like so:

CREATE TRIGGER tgINSCategory ON Category INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON

    INSERT INTO Category (SiteID, Name, SortOrder)
    SELECT SiteID, Name, 
        ISNULL((SELECT MAX(c.SortOrder)
                FROM Category c INNER JOIN INSERTED i ON c.SiteID = i.SiteID), 0) + 1   
    FROM INSERTED
END

In this first scenario you would just NHibernate map the SortOrder column as read only (just add a .ReadOnly() on the fluent nhibernate class map SortOrder property).

The second way would be to use NHibernate's built in feature called interceptors. With an interceptor you can run whatever code you like just at the time when NHibernate would normally generate SQL to execute a DML action. The below auto-inserts creation info but would be very similar to what you would need. IInsertLoggable is a custom interface that simply lists the properties/columns of interest for interception.

using System;
using System.Reflection;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;

    public class InsertDefaults : EmptyInterceptor {
        private const string CREATED_BY = "CreatedById";

        private Hashtable GetInsertLoggablePropertyIndexes(string[] Properties) {
            Hashtable result = new Hashtable();
            for (int i = 0; i < Properties.Length; i++) {
                if (Properties[i] == CREATED_BY) {
                    result.Add(CREATED_BY, i);
                    break;
                } 
            }
            return result;
        }

        public override bool OnSave(object entity, object id, object[] state, string[] propertyNames, NHibernate.Type.IType[] types) {
            if (entity is IInsertLoggable) {  
                Hashtable indexes = GetInsertLoggablePropertyIndexes(propertyNames);
                state[(int)indexes[CREATED_BY]] = currentUser;
                PropertyInfo createdByProp = entity.GetType().GetProperty(CREATED_BY);
                if (createdByProp != null)
                    createdByProp.SetValue(entity, currentUser, null);
            }
            return base.OnSave(entity, id, state, propertyNames, types);
        }
    }

In my opinion this sort of classic trigger operation should probably live on the database and I'd go with the first option...