views:

63

answers:

2

I have a query which has an Order By clause. The generated SQL from NHibernate looks like

 ORDER BY coalesce(x.Company as x__.Company, y.Company) asc 

This fails as 'as' is not allowed in Order by clause in MS SQL Server. Is there any way I can prevent aliasing?

The criteria query that I have written looks like:

 var orderBy = Projections.SqlFunction("coalesce", NHibernateUtil.String,      
                       Projections.ProjectionList() 
                      .Add(Projections.Property("x.Company"))
                      .Add(Projections.Property("y.Company")));

 var order = Order.Asc(orderBy);
 criteria.AddOrder(order);
A: 

I've had similar annoying problems. You might have to derive a class from PropertyProjection and use that inplace of Projections.Property(). Override the ToSqlString method and strip out the AS clause.

Tim
Tim,What should the override be? I attempted something like: public override SqlString ToSqlString(ICriteria criteria, int loc, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { return new SqlString(this.PropertyName); }But this threw an exception:System.ArgumentException was unhandled by user code Message=length should be greater than or equal to 0
notAnXpert
Or for that matter, in the override I provided return new SqlString(new string[]{this.PropertyName});I received the same ArgumentException
notAnXpert
A: 
Projections.SqlFunction("coalesce",
                        NHibernateUtil.String,
                        Projections.Property("x.Company"),
                        Projections.Property("y.Company"));

The parameters to the coalesce (or any other) function should be passed separately (actually, as a params array), not merged into a ProjectionList.

Diego Mijelshon
Thanks Diego! That helped. I was struck with this one for a while.
notAnXpert