Hi everyone,

I've been working on migrating our .NET/SQL Server 3-tier application from using ADO.NET and stored procs to NHibernate (for 95% of the cases, at least). Tonight I thought I had cleared my final hurdle: row-level security, implemented by having the middle-tier application server set the calling user's ID in an NHibernate filter. The <filter> tag on each object's mapping file handles the sub-select against the tables that store user-permissions.

This works great for queries - but any kind of loading by the primary key (Load/Get) causes any filters not to be applied. I understand the rationale for this - but in my case, my only option seems to do additional checking in the application server whenever an object is loaded by its ID.

Is there any way I can make the filter apply even when querying by ID? I considered using Criteria instead of Filters, but it seems like Load/Get will ignore those, as well.

Any help would be greatly appreciated! I really would like to move to NHibernate and I hope this final stumbling block doesn't throw everything out the window.

Brad

Just found this post with the same issue:
http://forum.hibernate.org/viewtopic.php?t=985304

Should have searched harder I guess.

Anyway, it appears that ISession.Get will throw an exception if the object cannot be found. This isn't really the behavior I want, I would rather have null returned. So, I devised this method which solves my before-mentioned filtering problem, and it won't throw an exception if the object can't be found.

T GetByID<T>( object id )
{
IList<T> items = _session.CreateCriteria( typeof( T ) )
.Add( Expression.Eq( "id", id ) )
.List<T>();
if ( items.Count > 0 ) return items[ 0 ];
else return default( T );
}

NHibernate will substitute "id" for whatever the ID of your class is. I don't know how this would work with composite keys, but it definitely works for my boring single-column-surrogate-key tables.

Hopefully this helps somebody in the future.