Criteria Query and Select Satement

HibernateJoey · **Joined:** Wed Aug 25, 2010 12:52 pm **Posts:** 12

Hi!

I'd like to know how to select just a few columns instead of all by using a criteria query.
Here is my current code:

Code:

        public ICollection<Geo> GetGeo(string city, int? plc, int? areaCode, string federalState)
        {
            using (ISession session = NHibernateHelper.OpenSession())
            {
                ICriteria criteria = session.CreateCriteria(typeof(Geo));

                if (city.Length != 0)
                    criteria.Add(Expression.Sql("City LIKE ?", city + "%", NHibernateUtil.String));
                if (plc.HasValue)
                    criteria.Add(Expression.Eq("Plc", plc));
                if (areaCode.HasValue)
                    criteria.Add(Expression.Eq("AreaCode", areaCode));
                if (federalState != null)
                    criteria.Add(Expression.Eq("FederalState", federalState));

                return criteria.List<Geo>();
            }
        }

But up to now, I get all columns of the Geo table. How can I now select just city, plc, areaCode and federalState?

Regards, Joe

wolli · **Posted:** Mon Sep 13, 2010 9:35 am

Have a look at projections:

http://nhforge.org/doc/nh/en/index.html#querycriteria-projection

HibernateJoey · **Joined:** Wed Aug 25, 2010 12:52 pm **Posts:** 12

Hi Wolfgang,

thanks for your help. I tried the following but I got a the following exception: Unable to perform find[SQL: SQL not available]
Do you know what I'm doing wrong?

Code:

                ICriteria criteria = session.CreateCriteria(typeof(Geo));

                if (plc.HasValue)
                    criteria.Add(Expression.Eq("Plc", plc));
                if (areaCode.HasValue)
                    criteria.Add(Expression.Eq("AreaCode", areaCode));
                if (federalState != null)
                    criteria.Add(Expression.Eq("FederalState", federalState));
                criteria.SetProjection(Projections.ProjectionList()
                    .Add(Projections.Property("City"), "City")
                    .Add(Projections.Property("Plc"), "Plc"));

Regards, Joe

wolli · **Posted:** Mon Sep 13, 2010 11:01 am

Is there an inner exception ? Can't see an obvious error. Try setting an alias in the criteria

ICriteria criteria = session.CreateCriteria(typeof(Geo), "g");

if (plc.HasValue)
criteria.Add(Expression.Eq("g.Plc", plc));
...

HibernateJoey · **Joined:** Wed Aug 25, 2010 12:52 pm **Posts:** 12

Thank you Wolfgang, you're right. There is no error!
The return value (I didnt't post) was the error. I got an instance of the class back.

Code:

return criteria.List<Geo>();

Regards, Joe