HQL query problem

Question

Hi all,

I'm using this hql query for my filters. Query perfectly working except width (string) part.

Here is the query,

public IList<ColorGroup> GetDistinctColorGroups(int typeID, int finishID, string width)
            {
                string queryStr = "Select distinct c from ColorGroup c inner join c.Products p " +
                                  "where p.ShowOnline = 1 ";


                if (typeID > 0)
                    queryStr += " and p.ProductType.ID = " + typeID;

                if (finishID > 0)
                    queryStr += " and p.FinishGroup.ID = " + finishID;

                if (width != "")
                    queryStr += " and p.Size.Width = " + width;

                IList<ColorGroup> colors = NHibernateSession.CreateQuery(queryStr).List<ColorGroup>();

                return colors;
            }

ProductType and Size have same mappings and relations.

This is the error;

NHibernate.QueryException: illegal syntax near collection: Size [Select distinct c from .Domain.ColorGroup c inner join c.Products p where p.ShowOnline = 1 and p.ProductType.ID = 1 and p.FinishGroup.ID = 5 and p.Size.Width = 4]

Any ideas ?

edit:

Btw in this project I used this linq query which is really simmilar hql one. So I don't think it's a misstype or more fundamentally error..

colorOfSerie = (from p in products where p.Size.Width.Equals(width) select p.ColorGroup).Distinct().ToList<ColorGroup>();

Answer 1

If Width is a string:

queryStr += " and p.Size.Width = '" + width + "'";

But I would suggest you to use parameters in your query instead of string concatenations.

Answer 2

Looks like the product property "Size" doesn't exist. Is it "size" or "ProductSize" or something similar? If you remove that condition, does the query work?

Answer 3

I'm guessing that you need to JOIN the Size entity explicitly in the query, since the other two tables (ProductType, FinishGroup) is compared using their primary keys I'm guessing maybe that's why it works? (since, they probably are "belongs-to" relations meaning their ID is actually in the Product-table).