如何查询具有多对多关系的审计表

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:

但是,在尝试访问Tags属性或entryRevision时,出现错误:

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

NHibernate.LazyInitializationException:初始化[Unavailable#] - 懒得初始化集合,没有关闭会话或会话

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?

我想通过直接查询Tag_Entry_AUD表来解决这个问题,以获取曾经与此条目相关联的所有标记ID,但我不确定如何操作?是否可以使用HQL查询执行此操作?

1 个解决方案

#1

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.

这给出了一个标签ID列表,这些标签标签是或者与该条目相关联的一个点。

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):

以上作为一个HQL查询(正如Roger所指出的,上面的代码将加载整个Tag_entry_AUD表...可能需要避免的事情):

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();

#1