NHibernate Group By // Having Issue

I'll first expose what i want to do, and after explain my problem. I've 2 tables : "CharItem" and "Attribute", and a table "ItemAttribute" to link both as it's a m:n relation. This table can also define a value for the attribute.

I wanted to search all items having at least some attributes. The sql query is this :

SELECT  ci.*
FROM     CharItem ci 
INNER JOIN ItemAttribute ia
on ci.CharItemId = ia.CharItemId 
WHERE 
( 
(ia.AttributeId = 48 and ia.ValueX >= 1) 
or (ia.AttributeId = 6) 
) 
GROUP BY ci.CharItemId 
HAVING   count(ia.AttributeId) >= 2

So as you can see, what i want is to find all items, so my query must return me some item objects.

I've tryied to do it, and i think i've almost find the solution, but i'm missing something. With the following method,开发者_C百科 i return the good ids, but not a full item, and i don't find how to get a full item :x

  var query = Session 
  .CreateCriteria(typeof(CharItem), "ci") 
  .CreateCriteria("ci.ItemAttribute", "ia", JoinType.InnerJoin); 
  Disjunction disjunction = Restrictions.Disjunction(); 
  foreach (var entry in itemAttribs) 
  { 
    var attribute = entry.Key; 
    var sign = entry.Value.Item1; 
    var value = entry.Value.Item2; 
    var restrictAttrib = Restrictions.Eq("ia.Attribute", attribute); 
    Conjunction conjunction =Restrictions.Conjunction(); 
    conjunction.Add(restrictAttrib); 
    conjunction.Add(Restrictions.Eq("ia.Value", values[j])); 
    disjunction.Add(conjunction); 
  } 
  query.Add(disjunction); 
  query.SetProjection(Projections.ProjectionList() 
    .Add(Projections.GroupProperty("ci.CharItemId"),"CharItemid")); 
  query.Add(Restrictions.Ge(Projections.Count("ia.Attribute"), itemAttribs.Count)); 
} 
var charItems = query.List<CharItem>();

Thanks for your help.

Solved! Check answers to see how to.

When you use Projections, NHibernate will return only the properties specified in the projection. That's why you get only the ids back in the above case. If you want all the other attributes of item, you need to explicitly add them to the list of projections.