This may be something obvious but I've been banging my head against it for a few hours and can't figure out where I'm going wrong.
I'm trying to run a small piece of code to test adding OR criteria to an NHibernate query. This is the code I have:
using (ISession session = NHibernateHelper.OpenSession())
{
ICriteria criteria = session.CreateCriteria<TestObject>();
int[] ids = {1, 2, 3};
foreach (int id in ids)
{
ICriterion criterion = Restrictions.Eq("Id", id);
criteria.Add(Restrictions.Disjunction().Add(criterion));
}
IList<TestObject> items = criteria.List<TestObject>();
return items;
}
It's just something simple that I would expect to return all test objects with IDs 1-3. But, when I'm running the code, the query generated is to find an object with ID = 1 AND ID = 2 AND ID = 3. Which, unsurprisingly, doesn't return anything.
The mapping's set up correctly (I can add/edit/remove/list all of the objects) and there are objects in there with these IDs.
Am I doing something obviously wrong? Any samples of using Disjunction I've seen online all seem to use it this way. I just don't get why it keeps on using AND.
Thanks.
Your problem lies in the fact that you're creating a new disjunction every time (in a loop). What you need to do is:
int[] ids = {1, 2, 3};
ICriterion disjunction = Restrictions.Disjunction();
foreach (int id in ids)
{
ICriterion criterion = Restrictions.Eq("Id", id)
disjunction.Add(criterion);
}
criteria.Add(disjunction);
The syntax may be a bit wrong - I'm a Hibernate guy rather than .NET :-)
To clarify, your original code would generate something like (in pseudo-code):
WHERE (OR(ID=1)) AND (OR(ID=2)) AND (OR(ID=3))
Since there's nothing to "OR" to, disjunctions were silently omitted.
Updated code based on ChssPly76's answer:
using (ISession session = NHibernateHelper.OpenSession())
{
ICriteria criteria = session.CreateCriteria<TestObject>();
Junction disjunction = Restrictions.Disjunction();
int[] ids = {1, 2, 3};
foreach (int id in ids)
{
ICriterion criterion = Restrictions.Eq("Id", id);
disjunction.Add(criterion);
}
criteria.Add(disjunction);
IList<TestObject> items = criteria.List<TestObject>();
return items;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With