4

Using the accepted answer to this question I have been able to concatenate two fields using ICriteria and projections. I have ended up with 

return session.CreateCriteria<Contact>()
                .CreateAlias("USState","USState", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
                .SetProjection(Projections.ProjectionList()
                     .Add(Projections.Property<Contact>(x=>x.Id), "Id")
                     .Add(Projections.SqlFunction("concat", 
                            NHibernateUtil.String,
                            Projections.Property<Contact>(x=>x.BasicInfo.FirstName),
                            Projections.Constant(' '),
                            Projections.Property<Contact>(x=>x.BasicInfo.LastName)),"Name")
                     .Add(Projections.Property<Contact>(x=>x.BasicInfo.City),"City")
                     .Add(Projections.Property<Contact>(x=>x.USState.Name) ,"State"))
                .Add(Restrictions.Where<Contact>(x => x.Pack == false))
                .AddOrder(new Order(Projections.Property<Contact>(x => x.BasicInfo.FirstName), true))
                .AddOrder(new Order(Projections.Property<Contact>(x => x.BasicInfo.LastName), true))
                .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<ContactsViewModel>())
                .List<ContactsViewModel>();

This is sending the expected query to the server and is working.

My question is if there is any other way besides using SqlFunction to do concatenation using ICriteria and projections.

Community
  • 1
  • 1
Steve Mallory
  • 4,245
  • 1
  • 28
  • 31
  • 1
    why this solution is not good for you? – Renatas M. May 23 '11 at 19:37
  • @Reniuz Well I just started trying to do this and I thought that I might be missing some easier way to do it. It seems that it could get a bit unwieldy if you need it a lot. Also, after I got this working, I discovered that if one value is null, the whole projection is null. So now I have to add an addition level method calls in there. – Steve Mallory May 23 '11 at 19:47
  • 1
    @Steve Mallory: I think you should mention that in you question :)...4 example: code working but how could I refactor, get more performance, make easy to read, simplify and so on. – Renatas M. May 23 '11 at 19:54
  • @Reniuz Yes, thanks. I clarified my question. – Steve Mallory May 23 '11 at 20:05
  • Do you prefer that this concatenation be done in SQL? Are you open to doing this after you've retrieved the data? – csano May 23 '11 at 20:12
  • @cs Yes. The resulting list is sent directly to a third party control expecting an IEnumerable. I've looked at concatenating the fields using properties/methods of the control, but it doesn't seem to be supported. – Steve Mallory May 23 '11 at 20:18

0 Answers0