Help with ICriteria & CreateCriteria on sub-objects

jambrose · **Joined:** Fri Nov 16, 2007 6:20 pm **Posts:** 15

The following "works" but it ANDs all the expressions together. What I really want is to have them all ORed. How do I accomplish this? Is it possible with a criteria query?

Code:

ICriteria criteria = Session.CreateCriteria(typeof(User));
criteria.Add(Expression.Like("UserName", keyword, MatchMode.Anywhere));

criteria.CreateCriteria("Membership").Add(Expression.Like("Email", keyword, MatchMode.Anywhere));

criteria.CreateCriteria("Profile").Add(Expression.Like("PropertyName", "FirstName", MatchMode.Anywhere)).Add(Expression.Like("PropertyValueString",keyword, MatchMode.Anywhere));

Thanks
Jack

wolli · **Posted:** Tue Aug 26, 2008 2:53 am

Try this ... I hope the brackets are OK.

Code:

ICriteria criteria = Session.CreateCriteria(typeof(User), "u")
   .CreateAlias("Membership", "m")
   .CreateCriteria("Profile", "p")
   .Add(Expression.Disjunction()
        .Add(Expression.Like("u.UserName", keyword, MatchMode.Anywhere))
        .Add(Expression.Like("m.Email", keyword, MatchMode.Anywhere))        
        .Add(Expression.And(
            Expression.Like("p.PropertyName", "FirstName",
            Expression.Like("p.PropertyValueString",keyword, MatchMode.Anywhere))
);

jambrose · **Joined:** Fri Nov 16, 2007 6:20 pm **Posts:** 15

Thanks! This almost works, here is my current criteria modified somewhat so I could tweak things:

Code:

ICriteria criteria = Session.CreateCriteria(typeof(User), "u")
                .CreateAlias("Membership", "m")
                .CreateCriteria("Profile", "p");


            criteria.Add(Expression.Disjunction()
                             .Add(Expression.Like("u.UserName", keyword, MatchMode.Anywhere))
                             .Add(Expression.Like("m.Email", keyword, MatchMode.Anywhere))
                             .Add(Expression.And(
                                      Expression.Like("p.PropertyName", "FirstName"),
                                      Expression.Like("p.PropertyValueString", keyword, MatchMode.Anywhere))));

If I remove the third expressions set for p.PropertyName this thing works perfectly. The problem seems to be that PropertyName may not exist in the properties table, this seem to cause some very odd behavior. If there is an entry with a FirstName, I get like 8 duplicate entries returned. If there isn't a FirstName I get nothing.

The criteria is generating the following sql like command:

Code:

u.UserName like %asdf% or m.Email like %asdf% or 
(p.PropertyName like FirstName and p.PropertyValueString like %asdf%)

What is the difference between using CreateAlias and CreateCriteria? I changed them in and out and it didn't seem to impact anything.

Would I be better off going the HQL route rather than a Criteria query?

Thanks
Jack

wolli · **Posted:** Tue Aug 26, 2008 12:46 pm

The difference between CreateCriteria and CreateAlias is the first one returns a new instance of a criteria and all following expressions are for that criteria. If you use "your" notation it makes no difference, but if you use the fluent notation like in my example it does.

Btw, in my suggestion, it should be CreateAlias in both cases.

Quote:

If there is an entry with a FirstName, I get like 8 duplicate entries returned. If there isn't a FirstName I get nothing.

You get duplicates if you have specified FetchMode.Join somewhere (either in the mapping or in the criteria itself). You can remove the duplicates with a result transformer:

criteria.SetResultTransformer( CriteriaUtil.DistinctRootEntity )

Do you have to cover the case that no property "FirstName" exists ? Then it gets a bit complicated I think ...

jambrose · **Joined:** Fri Nov 16, 2007 6:20 pm **Posts:** 15

Getting closer!

OK the result transformer quickly solved the duplicate entry. However, I still get no results for any entry that doesn't have a property value. In some cases PropertyName -> FirstName won't exist, how do I accomodate that? I tried adding an expression IsNotNull, but that didn't do a thing. Here is my current ICriteria query:

Code:

ICriteria criteria = Session.CreateCriteria(typeof(User), "u")
                .CreateAlias("Membership", "m")
                .CreateAlias("Profile", "p");


            criteria.Add(Expression.Disjunction()
                             .Add(Expression.Like("u.UserName", keyword, MatchMode.Anywhere))
                             .Add(Expression.Like("m.Email", keyword, MatchMode.Anywhere))
                             .Add(Expression.Conjunction()
                                .Add(Expression.Eq("p.PropertyName", "FirstName"))
                                .Add(Expression.Like("p.PropertyValueString", keyword, MatchMode.Anywhere)))
                            
                        );
            criteria.SetResultTransformer(CriteriaUtil.DistinctRootEntity);
            
            return criteria.List<User>() as List<User>;

Also, is there any advantage to the fluent notation over what I have done?

Thanks
Jack

wolli · **Posted:** Tue Aug 26, 2008 1:03 pm

What kind of association is Profile ? A collection or a many/one-to-one ? If PropertyName "FirstName" does not exist, does that mean, there is no profile at all ?

You probably need a subquery for that. http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/querycriteria.html#querycriteria-detachedqueries

jambrose · **Joined:** Fri Nov 16, 2007 6:20 pm **Posts:** 15

Profile is a one-to-many mapping snippet from the user.hbm.xml file:

Code:

<bag name="Profile" cascade="all" lazy="true" inverse="false">
      <key column="UserId" />
      <one-to-many class="ProfileItem" />
    </bag>

ProfileItem mapping:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="dbo">
  <class name="WSDOT.Core.Domain.ProfileItem, WSDOT.Core" table="wsdot_Profile">
     <id name="ID" type="Int32" column="ProfileId">
      <generator class="identity" />
    </id>
    <many-to-one name="Application" column="ApplicationId" class="WSDOT.Core.Domain.Application, WSDOT.Core" />
    <property name="UserId" column="UserId" type="Guid" />
    <property name="LastUpdateDate" column="LastUpdateDate" type="DateTime" />
    <property name="PropertyName" column="PropertyName" type="String" />
    <property name="PropertyValueBinary" column="PropertyValueBinary" type="Byte[]" />
    <property name="PropertyValueString" column="PropertyValueString" type="String" />
  </class>
</hibernate-mapping>

Current Criteria query:

Code:

ICriteria criteria = Session.CreateCriteria(typeof(User), "u")
                .CreateAlias("Membership", "m");

            DetachedCriteria sub = DetachedCriteria.For<ProfileItem>("p");
            sub.Add(Expression.Conjunction()
                .Add(Expression.Like("p.PropertyName", "FirstName"))
                .Add(Expression.Like("p.PropertyValueString", keyword, MatchMode.Anywhere)));

            criteria.Add(Expression.Disjunction()
                             .Add(Expression.Like("u.UserName", keyword, MatchMode.Anywhere))
                             .Add(Expression.Like("m.Email", keyword, MatchMode.Anywhere))
                             .Add(Subqueries.Exists(sub))
                        );
            //criteria.SetResultTransformer(CriteriaUtil.DistinctRootEntity);

            var x = criteria.List();        //For debugging
            IList<User> users = criteria.List<User>() as List<User>;

            return users;

The above code does not actually work. I get a Object reference not set to an instance of an object error when it does criteria.List(). This appears to be on the sub-query since if I remove that it all works.

Error follows:

Quote:

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 67: //criteria.SetResultTransformer(CriteriaUtil.DistinctRootEntity);
Line 68:
Line 69: var x = criteria.List();
Line 70: IList<User> users = criteria.List<User>() as List<User>;
Line 71:

Source File: D:\Workspaces\CommuteLog_Main\WSDOT.Data\UserDao.cs Line: 69

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
NHibernate.Loader.Criteria.CriteriaQueryTranslator.get_ProjectedTypes() +43
NHibernate.Criterion.SubqueryExpression.InitializeInnerQueryAndParameters(ICriteriaQuery criteriaQuery) +168
NHibernate.Criterion.SubqueryExpression.ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters) +71
NHibernate.Criterion.Junction.ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters) +361
NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetWhereCondition(IDictionary`2 enabledFilters) +285
NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable persister, CriteriaQueryTranslator translator, ISessionFactoryImplementor factory, CriteriaImpl criteria, String rootEntityName, IDictionary`2 enabledFilters) +399
NHibernate.Loader.Criteria.CriteriaLoader..ctor(IOuterJoinLoadable persister, ISessionFactoryImplementor factory, CriteriaImpl rootCriteria, String rootEntityName, IDictionary`2 enabledFilters) +159
NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) +295
NHibernate.Impl.CriteriaImpl.List(IList results) +65
NHibernate.Impl.CriteriaImpl.List() +55
WSDOT.CommuteLog.Data.UserDao.Find(String keyword) in D:\Workspaces\CommuteLog_Main\WSDOT.Data\UserDao.cs:69
WSDOT.Controllers.AccountController.List(Int32 page) in D:\Workspaces\CommuteLog_Main\WSDOT.Controllers\AccountController.cs:52

Thoughts?
Jack

jambrose · **Joined:** Fri Nov 16, 2007 6:20 pm **Posts:** 15

Thanks wolli. Between you and the nhusers Google group I got the thing to work!

Final criteria is:

Code:

ICriteria criteria = Session.CreateCriteria(typeof(User), "u")
                .CreateAlias("Membership", "m");

            DetachedCriteria sub = DetachedCriteria.For<ProfileItem>("p");
            sub.Add(Expression.Conjunction()
                .Add(Expression.Like("p.PropertyName", "FirstName"))
                .Add(Expression.Like("p.PropertyValueString", keyword, MatchMode.Anywhere))
                .Add(Expression.EqProperty("p.UserId", "u.ID"))
                );
            sub.SetProjection(Projections.Id());

            criteria.Add(Expression.Disjunction()
                             .Add(Expression.Like("u.UserName", keyword, MatchMode.Anywhere))
                             .Add(Expression.Like("m.Email", keyword, MatchMode.Anywhere))
                             .Add(Subqueries.Exists(sub))
                        );
            criteria.SetResultTransformer(CriteriaUtil.DistinctRootEntity);
            return criteria.List<User>() as List<User>;

I may need to clean it up a bit, but it appears to work.

Thanks again,
Jack

wolli · **Posted:** Wed Aug 27, 2008 3:08 am

That's what I had in mind. Looks good now. Speaking of cleaning up, you don't need a conjunction in the detached criteria:

Code:

DetachedCriteria sub = DetachedCriteria.For<ProfileItem>("p")
   .Add(Expression.Like("p.PropertyName", "FirstName"))
   .Add(Expression.Like("p.PropertyValueString", keyword, MatchMode.Anywhere))
   .Add(Expression.EqProperty("p.UserId", "u.ID"))
   .SetProjection(Projections.Id()); 