Hi -
nhibernate version: 1.2.1.4000
I am succesfully using the criteria API to do the following..
Code:
IList results = session.CreateCriteria(typeof(TranslationBatch))
.SetProjection(Projections.Alias(
NHibernate.Expression.Projections.SqlProjection("case when {alias}.CheckedOutBy = 'SomeUserName' Then 0 else 1 END as CheckedOutSort", new string[] { "CheckedOutSort" }, new NHibernate.Type.IType[] { NHibernateUtil.Int32 }), "CheckedOutSort")
)
.AddOrder(Order.Asc("CheckedOutSort")).List();
(NOTE: I broke the above code up for readability)
This generates the following query which works great!
Code:
SELECT case when this_.CheckedOutBy = 'SomeUserName' Then 0 else 1 END as CheckedOutSort FROM TranslationBatch this_ ORDER BY CheckedOutSort asc
But what I really need to do is us a SqlProjection to add a field to my query which I can order by
AND populate the entity object. In my case the entity object (i.e. the class object) is TranslationBatch.
Here is some code i'm unsuccessfully trying...
Code:
DetachedCriteria dcTranslationBatch = DetachedCriteria.For<TranslationBatch>();
dcTranslationBatch.SetProjection(Projections.Alias(NHibernate.Expression.Projections.SqlProjection("case when {alias}.CheckedOutBy ='SomeUserName' Then 0 else 1 END as CheckedOutSort", new string[] { "CheckedOutSort" },new NHibernate.Type.IType[] { NHibernateUtil.Int32}),"CheckedOutSort"));
dcTranslationBatch.AddOrder(Order.Asc("CheckedOutSort"));
ICriteria criteria = dcTranslationBatch.GetExecutableCriteria(session);
List<TranslationBatch> listResults = criteria.List<TranslationBatch>() as List<TranslationBatch>;
This generates the following NHibernate.ADOException 'unable to perform find'.
The inner exception text reads '{"The value \"0\" is not of type \"CFCA.Web.Core.Domain.TranslationBatch\" and cannot be used in this generic collection.\r\nParameter name: value"}'
It appears Nhibernate is trying to match the value '0' to a Property in the TranslationBatch class.
Projections appear very useful but maybe i'm trying to use them incorrectly. I really
DON'T want to write a stored procedure or HQL because i'm combining many other criteria in my query.
I just simply want to add a projection
AND return the entity object.
Is this possible with projections or am I missing something? I've read the manual and I notice they always return IList objects when doing projections. Maybe this all we can do?
Thanks -
Login
FAQ
Search