Query on string collection property with criteria api

[Ramon Smits]

I am having the following entity + mapping:

public class Entity

{

    public virtual int Id { get; set; }
    public virtual ICollection Tags { get; set; }

}

 

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"

                   assembly="Test"

                   namespace="Test">

<class Entity">

<id name="Id"/>

<set name="Tags" table="WebcastTag">

<key column="WebcastId"/>

<element column="Tag"/>

</set>

</class>

</hibernate-mapping>

I would like to query for the existing of a tag via either the Criteria api or the query over api but cannot seem to get it working. HQL and Linq do work but cannot be used here as this is part of a very huge search query that I rather keep in as a criteria api. I can select the tags via the criteria api but I cannot query on it. I found some web pages mentioning Restrictions.Sql but it is not available in NHibernate v3.1.0.4000 which is the version that I am using.

// Linq query on tag

(from x in Session.Query<Entity>() where x.Tags.Contains("test") select x).ToArray();

// Linq select all tags

(from x in Session.Query<Entity>() from t in x.Tags select t).ToArray();

// Hql query on tag

Session

    .CreateQuery("from Webcast w join w.Tags t where t = :tag")

    .SetString("tag", "test")

    .List();

// Hql select all tags

Session

    .CreateQuery("select distinct t from Webcast w join w.Tags t")

    .List();

// Criteria api select all tags

Session

    .CreateCriteria<Entity>("w")

    .CreateCriteria("Tags", "t")

    .SetProjection(

        Projections.SqlProjection("Tag", new[] { "Tag" },

        new[] { NHibernate.NHibernateUtil.String }))

    .List();

I

[José F. Romaniello]

something like this?

var entities = s.CreateCriteria<Entity>()
    .CreateAlias("Tags", "t").Add( Restrictions.eq("t.Tag", "foo") )
    .List<Entity>();

2011/5/9 Ramon Smits <ramon...@gmail.com>

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

[Ramon Smits]

Well there is no class. Tags is a List<string>. I have tested your code already and it does not work as there is no property with the name 'Tag'. It cannot resolve it.

--

Ramon

[José F. Romaniello]

Ramon, I did a research on this...and i think is not supported even in Hibernate.
That is. The criteria api doesn't support quering colelction of elements... It does support even collection of components.


I did a workarround... with the only purpose of testing:


var result = s.CreateCriteria<Post>()
                    .CreateCriteria("Tags", "t", JoinType.InnerJoin)
                    .Add(Expression.Sql("tags3_.id = 'net'")).List<Post>();

As you can see.. I have used the nhibernate generated alias for the table in the query, and the Expression.Sql.
It is a workarround -I don't recommend you to use this

2011/5/10 Ramon Smits <ramon...@gmail.com>

[Ramon Smits]

Jose thanks for the workaround as this is exactly the workaround I was looking for.

I have to use this until:

a. Criteria api supports querying collections

b. Our search query is converted to Linq to NHibernate

c. We use a seperate type for the tags thus adjusting the domainmodel

--

Ramon

[Ramon Smits]

This construction is working but is it possible to pass the value as a sql parameter? I currently need to scan the comparison value for allowed construction as it could otherwise make sql injection possible.

--

Ramon