How to query audit table with many-to-many relationship

Question

I have an entry class with a many-to-many relationship with a tags class. I'm trying to restore a historical copy of an entry.

I've tried querying like this:

AuditReader reader = AuditReaderFactory.get(getEm());
var entryRevision = reader.createQuery()
    .ForEntitiesAtRevision(typeof(IEntry), false, true)
    .Add(new IdentifierEqAuditExpression(entryId, true))
    .Add(AuditEntity.RevisionNumber().Eq(revisionNumber)))
    .GetResultList().SingleOrDefault();

However, on attempting to access the Tags property or entryRevision, I get an error:

NHibernate.LazyInitializationException : Initializing[Unavailable#]-failed to lazily initialize a collection, no session or session was closed

I have confirmed that the session is still open when I try to access the tags.

I would like to work around this by querying the Tag_Entry_AUD table directly to get all the tag ids that might have once been associated with this entry, but I'm not sure how to? Is it possible to do this with an HQL query?

Please confirm that the session is still open once again ;). This is a NH core exception and not an envers exception... — Roger, May 06 '15 at 20:58

ryan · Accepted Answer · 2015-05-06T21:27:47.613

I was able to get something working:

const string hql = "from Tag_Entry_AUD tc";
var associatedTagIds = reader.CreateQuery(hql).List<Hashtable>()
    .Select(t => t["originalId"] as Hashtable)
    .Where(tc => (Guid) tc["Entry_Id"] == entryId && ((RevisionHistory)tc["REV"]).RevisionNumber <= revisionNumber)
    .Select(tc => (Guid) tc["Tags_Id"])
    .ToList();

This gives a list of tag Ids that tags that are, or were at one point associated with the entry.

EDIT:

The above as one HQL query (as Roger pointed out, the above code will load the entire Tag_entry_AUD table... probably something to avoid):

const string hql = "SELECT originalId.Tags_Id " +
    "FROM Tag_Entry_AUD te " +
    "WHERE te.originalId.Entry_Id = :id " +
    "AND te.originalId.REV = :revNo";

SimpleQuery<Guid> q = new SimpleQuery<Guid>(hql);
q.SetParameter("id", entryId);
q.SetParameter("revNo", revisionNumber);
var associatedTagIds = q.Execute().ToList();

That will load every row in tag_entry_aud table. – Roger May 06 '15 at 20:59 — Roger, May 06 '15 at 20:59

How to query audit table with many-to-many relationship

1 Answers1