How to apply filter for collection property (with one to many association) within DetachedCriteria

Question

I am facing some issues when fetching the collection with applied criteria.

public class Ship  
{
    public virtual int Id { get; protected set; }
    public virtual string Name { get; set; }
    public virtual IList<Structure> Structures { get; set; }       

    public Ship()
    {            
        Structures = new List<Structure>();
    }

    public virtual void AddStructure(Structure structure)
    {
        Structures.Add(structure);
    }        
}

public class Structure
{       
    public virtual int StructureId { get; protected set; }
    public virtual string Name { get; set; }
    public virtual Ship Ship { get; set; }
    public virtual int Score { get; set; }
    public virtual IList<StructureGeometry> StructuresGeomtries { get; set; }       

    public Structure()
    {
        StructuresGeomtries = new List<StructureGeometry>();
    }       
}  

public class StructureGeometry
{
    public virtual int StructureGeomId { get; protected set; }
    public virtual string Name { get; set; }                
    public virtual Structure Structure { get; set; }        

    public StructureGeometry()
    {          
    }
}


public class StructureMap : ClassMap<Structure>
{
    public StructureMap()
    {
        Id(x => x.StructureId).GeneratedBy.Increment();
        Map(x => x.Name);
        Map(x => x.Score);
        References(x => x.Ship);

        HasMany(x => x.StructuresGeomtries)
           .Cascade.All()
           .Inverse();   
    }
}    

 DetachedCriteria query = DetachedCriteria.For(typeof(Structure), "structure");
 Disjunction juction = Restrictions.Disjunction();
 juction.Add(NHibernate.Criterion.Expression.Like("Score", 1));
 juction.Add(NHibernate.Criterion.Expression.Like("Score", 3));
 query.Add(juction);

 Disjunction structureGeometryjuction = Restrictions.Disjunction();
 structureGeometryjuction.Add(NHibernate.Criterion.Expression.Like("Name", "SGeom3"));

 DetachedCriteria structuGeomCriteria = DetachedCriteria.For(typeof(StructureGeometry));
 structuGeomCriteria.Add(structureGeometryjuction);
 structuGeomCriteria.SetProjection(Projections.ProjectionList().Add(Projections.Property("Name"), "StructGeomName"));
        query.CreateCriteria("structure.StructuresGeomtries", "structureGeometry")               
        .Add(Subqueries.Exists(structuGeomCriteria));

var structures = query.GetExecutableCriteria(session).List<Structure>();

structures[0].StructuresGeomtries are fetched as per the association. I have not created any DTO, I am fetching the data from same entity(POCO).

I am not able to apply filter or criteria on collection property. Nhibernate by default selecting all the geometries as per the one to many association.

So I am able to fetch my root parent as per the criteria but not able to apply the criteria on collection properties.

Thanks,

score 0 · Answer 1 · answered Jun 10 '13 at 16:19

You will have to invert your selection - what you're doing in your query is selecting the structures which have geometries matching your criteria. This doesn't however filter out the geometries of the selected structures.

You could achieve this using a filter on the collection, but I think it might be easier to just execute your query on the StructureGeometry instead, eg:

var geoms = session.CreateCriteria<StructureGeometry>()
              .Add(Expression.Like("Name", "SGeom3"))
          .CreateAlias("Structure", "structure")
          .Add(Expression.Like("structure.Score", 1) || 
                   Expression.Like("structure.Score", 3))
              .List();

How to apply filter for collection property (with one to many association) within DetachedCriteria

1 Answers1