conditionally creating criteria nhibernate

Question

Say I have a users table and a countries table, something like:

tblUsers
int UserId
int UserCountry

tblCountries
int CountryId
string CountryName

and have the mappings etc appropriately organized for each.

How would create a query to retrieve all users from a list of countries, if I had something like List EligibleCountries?

Something like:

DetachedCriteria query = DetachedCriteria.For<Users>();
for(int i = 0; i < EligibleCountries.Count(); i++)
{
  query.CreateAlias("Country", "c")
        .Add(Restrictions.Like("c.CountryId", EligibleCountries[i]));
}

Wont work because a users only in one country and that would be checking to see if they're in all countries...

I tried using Restrictions.In but that didn't seem to work the way I wanted.

How can I do it so that it will retrieve users so long as they are in one of the countries in the eligible countries list?

Answer 1

I think you need the following if you want to stick with the Criteria API as opposed to HQL:

.Add(Expressions.Or(Expressions.Like(...

Hopefully, you can create a new instance of an Expressions class and dynamically populate it prior to adding it to the query itself.

Answer 2

// Or expression
var countryCondition = Expression.Disjunction();

foreach(int countryId in EligibleCountries)
{
    countryCondition.Add(Restrictions.Like("c.CountryId", countryId));
}

DetachedCriteria query = DetachedCriteria.For<Users>()
  .CreateCriteria("Country", "c")
  .Add(countryCondition);