Restrict by a set element in Criteria

Skip to first unread message

Dmitry Starosta

Feb 20, 2010, 2:36:42 PM2/20/10
to nhusers
I have a class that looks like this:

public class Company
// ....

ICollection<string> Notes;

// ....

The table for notes has 2 fields:

table notes
id int, -- company Id
note nvarchar(50)

The mapping in fluent nhibernate looks like this:

HasMany(e => e.Notes).Table("notes")

Everything works great as far as the mapping goes. However, I cannot
figure out how to query by the note text using ICriteria.

Let's say I want to find all companies that have notes with text
"testing 123".

I tried doing a query as follows:

var query = session.CreateCriteria<Company>();
query.CreateAlias("Notes", "note").Add(Restrictions.Eq("note",
noteToFind /* a string object */));
var companeis = query.List();

It says note is not a property of object Company. I cannot do it with
an inner criteria on the Notes property as well.

Any help would be appreciated.

For nitpickers: I know this is not how you search for free text in
real life. This is just an example with non-actual entity types or

Diego Mijelshon

Feb 21, 2010, 10:33:10 AM2/21/10
to nhusers
Although you should use a DetachedCriteria with Subqueries.PropertyIn to get the result you really want (search for examples in this group), the error you're getting is because CreateAlias doesn't change the "context" of the criteria. 
The message makes it clear: you are trying to access a "note" property in Company.
If you use CreateAlias, you can refer to that property as "note.note" (the naming you're using doesn't help much. That property should be called Text).
If you use CreateCriteria instead, it'll work as it's written.


You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to
To unsubscribe from this group, send email to
For more options, visit this group at

Dmitry Starosta

Feb 22, 2010, 12:34:04 AM2/22/10
to nhusers
Diego, Thank you for the response. But I am still confused.

The alias naming in the example was bad on my part. Let's change that
code to

"noteAlias").Add(Restrictions.Eq("noteAlias", noteToFind /* a string
object */));

"noteAlias.note" in the restriction clause does not work because the
Notes property is a collection of strings and not entities. Strings do
not have mapped properties.

I cannot do "query.Add(Subqueries.In("Notes"), detachedCriteria)"
either because the "noteToFind" string variable comes from user
interface and not the database.

Any help would be greatly apprecitated.

Dmitry Starosta

Feb 22, 2010, 12:42:22 AM2/22/10
to nhusers
I also tried running code:

query.Add(Restrictions.Eq("Notes", noteToFind);

I got an error: Notes expected type
Iesi.Collections.Generic.ISet`1[System.String], actual type

Is there a restriction or a subquery that would search for an in-
memory string inside a collection property? Restrictions.In seems to
be doing the opposite thing as well and cannot be used for collection

Diego Mijelshon

Feb 22, 2010, 7:18:12 AM2/22/10
to nhusers
My bad. I didn't look well and thought Notes was an entity collection.
I'm not really sure how to do it with Criteria then. 
With HQL, I believe you can do it with the "in" operator using the collection's elements as an argument.



Dmitry Starosta

Feb 22, 2010, 8:55:18 AM2/22/10
to nhusers
I can do something similar using SqlProjection, however, there seems
to be no way to define custom parameters in SqlProjection/Criteria.
Concatenating strings with a note text is certainly not something I
want to do due to Sql injection concerns. I wonder if there is at
least a way to define a strongly typed query parameter from a criteria

Dmitry Starosta

Feb 23, 2010, 12:59:34 PM2/23/10
to nhusers
Nobody knows how to restrict a query by a set element?

Dmitry Starosta

Feb 27, 2010, 10:51:29 PM2/27/10
to nhusers
I came up with a workaround that looks as follows:

var sqlProjection = Projections.SqlProjection("{alias}.note AS
note_value", new[] { "note_value" }, new[] { NHibernateUtil.String });

companyQuery.CreateCriteria<Company>(c => c.Notes) // I am using
lamdba extensions to criteria
.Add(Restrictions.Eq(sqlProjection, "some

The problem I get into is the note table alias in the JOIN condition
is different from the WHERE condition causing the query to fail.
Giving name to the criteria makes no difference as it only changes the
alias of the table in the WHERE condition. Does it sound like an
NHibernate bug?

I can get around the problem using a SELECT query inside the
projection but it is really ugly.

Ayende Rahien

Feb 27, 2010, 10:52:44 PM2/27/10
to nhusers

Take a look at HQL's elements() function.

Dmitry Starosta

Feb 27, 2010, 10:54:33 PM2/27/10
to nhusers
The problem is I cannot use HQL or even SQL easily because this
criteria also queries Lucene.


Feb 28, 2010, 12:34:38 PM2/28/10
to nhusers
> Does it sound like an NHibernate bug?

Yes, the SQL alias sounds like a bug. You will have to find a
workaround :

- You can hard-code the alias in the SQL criterion
var criteria = session.CreateCriteria<Company>()
.CreateAlias("Notes", "Notes")
.Add(Expression.Sql("notes3_.note = ?", noteToFind,

- You can remove the alias (it works fine if you do not have column
name conflicts)
var criteria = session.CreateCriteria<Company>()
.CreateAlias("Notes", "Notes")
.Add(Expression.Sql("note = ?", noteToFind, NHibernateUtil.String));

- You can use an "exists" statement :
var criteria = session.CreateCriteria<Company>()
.Add(new ValueExpression("Notes", noteToFind, "="));

public class ValueExpression : AbstractCriterion
private readonly string collectionName;
private readonly string op;
private readonly object value;

public ValueExpression(string collectionName, object value, string
this.collectionName = collectionName;
this.value = value;
this.op = op;

public override IProjection[] GetProjections()
return null;

public override TypedValue[] GetTypedValues(ICriteria criteria,
ICriteriaQuery criteriaQuery)
ICollectionPersister persister =
+ "." + this.collectionName);
return new TypedValue[] { new TypedValue(persister.ElementType,
this.value, EntityMode.Poco) };

public override SqlString ToSqlString(ICriteria criteria,
ICriteriaQuery criteriaQuery, IDictionary<string, IFilter>
IQueryableCollection persister =
+ "." + this.collectionName);


string tableName = ((IJoinable)persister).TableName;

SqlStringBuilder builder = new SqlStringBuilder(20);
builder.Add("exists ( select 1 from ").Add(tableName);
builder.Add(" where ").Add(persister.KeyColumnNames[0]).Add(" =
builder.Add(" and
").Add(persister.ElementColumnNames[0]).Add(this.op).AddParameter().Add(" )");

return builder.ToSqlString();

public override string ToString()
return (this.collectionName + this.op + this.value);


Feb 28, 2010, 1:35:54 PM2/28/10
to nhusers
I'm having a similar problem. But I posted my question in the old

For now I solved it with SQL and a parameter. That is injection save,
but not the solution I would like.

I was also able to solve this in HQL, but it generated a really ugly
in (subquery) statement. As far as performance goes, you should avoid
the in statement in sql and the combination in (subquery) is the worst
of them all.


Dmitry Starosta

Feb 28, 2010, 2:50:30 PM2/28/10
to nhusers

Thank you so much!!! The ValueExpression does exactly what I need to

Hardcoding aliases was not an option because if there are other
dynamically generated restrictions that caused the alias to change.

I will try to reproduce the alias bug in a small test solution and
will submit it to Nhibernate Jira.

Thanks again,

Olivier Coanet

Feb 28, 2010, 4:49:53 PM2/28/10
Zorgoban, I think you can apply the same solution to your problem. But I would probably use a named SQL query instead.
Reply all
Reply to author
0 new messages