Restrict by a set element in Criteria

[Dmitry Starosta]

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")
.KeyColumn("id")
.Cascade.All()
.LazyLoad()
.AsSet().Element("note");

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
data.

[Diego Mijelshon]

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.

   Diego

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Dmitry Starosta]

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

query.CreateAlias("Notes",
"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]

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
System.String.

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
properties.

[Diego Mijelshon]

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.

   Diego

--

[Dmitry Starosta]

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
query.

[Dmitry Starosta]

Nobody knows how to restrict a query by a set element?

[Dmitry Starosta]

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
text"));

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]

http://nhforge.org/doc/nh/en/index.html#queryhql-examples

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

[Dmitry Starosta]

The problem is I cannot use HQL or even SQL easily because this
criteria also queries Lucene.

[MisterTom]

> 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,
NHibernateUtil.String));

- 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, "="));

[Serializable]
public class ValueExpression : AbstractCriterion
{
private readonly string collectionName;
private readonly string op;
private readonly object value;

public ValueExpression(string collectionName, object value, string
op)
{
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 =
(IQueryableCollection)criteriaQuery.Factory.GetCollectionPersister(criteriaQuery.GetEntityName(criteria)
+ "." + this.collectionName);
return new TypedValue[] { new TypedValue(persister.ElementType,
this.value, EntityMode.Poco) };
}

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

criteriaQuery.AddUsedTypedValues(this.GetTypedValues(criteria,
criteriaQuery));

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(" =
").Add(criteriaQuery.GetIdentifierColumns(criteria)[0]);
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);
}
}

[Zorgoban]

I'm having a similar problem. But I posted my question in the old
forums:
https://forum.hibernate.org/viewtopic.php?f=25&t=1002868

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.

Greetings!
Zorgoban

[Dmitry Starosta]

MisterTom,

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

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,
Dmitry

[Olivier Coanet]

Zorgoban, I think you can apply the same solution to your problem. But I would probably use a named SQL query instead.