Question

I m trying to build a search query using NHibernate that will filter on parameters from several different tables and result in somewhat-reasonable SQL that can take advantage of NHibernate s lazy-loading. Fromm reading various tips online, it seems that the latest-and-greatest way to do that is to use the QueryOver object to conditionally add in the parameters being used, as in the following snippet:

Hibernate.Criterion.QueryOver<Models.Site, Models.Site> query = NHibernate.Criterion.QueryOver.Of<Models.Site>();

if (!string.IsNullOrEmpty(state))
                query = query.WhereRestrictionOn(r => r.State.StateName).IsInsensitiveLike("%" + state + "%");

if (startDate.HasValue)
                    query = query.Where(r => r.Events
                                            .Where(e=>e.EventDate >= startDate.Value)
                                            .Count() > 0
                                    );

return query.GetExecutableQueryOver(currentSession).Cacheable().List();

（事件具有指向站点的外键）

I have two questions: How do I filter the child objects, instead of just the parent? The sample code above gives me all the sites with a matching events, but within that site, I only want matching events. If I m supposed to use a join or a subquery, how? I m confused about maintaining my tree-like hierarchy with lazy-loading through a join or subquery.

Edit: this has been answered. Thanks psousa! ~~How do I add an or clause? I found reference to a Disjunction object, but it doesn t seem like that s available using the QueryOver method.~~

Edit: I want to result in a list of sites (top level object) filtered by the site criteria, and each site should have its list of events filtered by the event criteria.

我希望它生成如下SQL：

SELECT *
FROM [site] s
    LEFT JOIN [event] e ON s.siteID = e.siteID
WHERE e.eventDate > @eventDate
    AND (s.stateCd = @state OR s.stateName LIKE @state)

Answer 1

当使用psousa建议的连接别名时，您将得到对象结构和行结构的奇怪组合，顶级对象由附加到它们的子对象复制。为了获得我想要的结果，您可以使用TransformUsing和DistinctRootEntityResultTransformer，如下代码所示：

    Site siteAlias = null;
    Event eventAlias = null;

    var results = currentSession.QueryOver<Site>(() => siteAlias)
            .JoinAlias(m => m.Event, () => eventAlias)
            .Where(() => eventAlias.EventDate > eventDate)
            .Where(() => siteAlias.StateCd == state || Restrictions.On(() => siteAlias.StateName).IsLike(state))
            .TransformUsing(new NHibernate.Transform.DistinctRootEntityResultTransformer())
            .List();

Answer 2

我会这样做：

//use aliases. Optional but more practical IMHO 
Site siteAlias = null;
Event eventAlias = null;

//use JoinAlias instead of JoinQueryOver to keep the condition at the "Site" level
var results = Session.QueryOver(() => siteAlias)
        .JoinAlias(m => m.Event, () => eventAlias)
        .Where(() => eventAlias.EventDate > eventDate)
        .Where(() => siteAlias.StateCd == state || Restrictions.On(() => siteAlias.StateName).IsLike(state))
        .List();

您提到了Disjunction类，它实际上可能与QueryOver一起使用，例如：

var disjunction= new Disjunction();
disjunction.Add(() => siteAlias.StateCD == state);
disjunction.Add(Restrictions.On(() => siteAlias.StateName).IsLike(state));

QueryOver查询将是：

var results = Session.QueryOver(() => siteAlias)
        .JoinAlias(m => m.Event, () => eventAlias)
        .Where(() => eventAlias.EventDate > eventDate)
        .Where(disjunction)
        .List();

友情链接