How to implement a search query in NHibernate 3 (using NHibernate.Linq)
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 t开发者_开发问答hat 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();
(an event has a foreign-key to site)
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.
I expect it to generate SQL like the following:
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)
I would do that query as such:
//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();
You mentioned the Disjunction class, and it may in fact be used with QueryOver, like:
var disjunction= new Disjunction();
disjunction.Add(() => siteAlias.StateCD == state);
disjunction.Add(Restrictions.On(() => siteAlias.StateName).IsLike(state));
The QueryOver query would be:
var results = Session.QueryOver(() => siteAlias)
.JoinAlias(m => m.Event, () => eventAlias)
.Where(() => eventAlias.EventDate > eventDate)
.Where(disjunction)
.List();
When using a join alias as suggested by psousa, you will get results in a strange combination of an object structure and a row structure, with the top-level objects being duplicated by the child objects that are attached to them. In order to get the results I was looking for, you can use TransformUsing and a DistinctRootEntityResultTransformer as shown in the following code:
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();
精彩评论