Hibernate Criteria Query - How to search through many-to-many with attributes?

Question

I'm trying to create a Criteria query to select objects that are related via an association table.

Insurer * - 1 Insurer_Section 1 - * Section

InsurerSection has an association attribute: active:bool.

How do I get all Insurers whose active attribute in the InsurerSection class is set to true?

PS: I can't go like this:

Insurer.FindAll( DetachedCriteria.For().CreateCriteria("Insurer_Section").Add(Expression.Eq("Active", true) );

because Insurer_Section is an association table that is only mapped via HasAndBelongsToMany:

[HasAndBelongsToMany(typeof(Section), Table = "Insurer_Section", ColumnKey = "IdInsurer", ColumnRef = "IdSection", Cascade = ManyRelationCascadeEnum.AllDeleteOrphan)] private IList Sections { get { return this.sections; } set { this.sections = value; } }

AND

[HasAndBelongsToMany(typeof(Insurer), Table = "Insurer_Section", ColumnKey = "IdSection", ColumnRef = "IdInsurer", Cascade = ManyRelationCascadeEnum.None, Inverse = true)] public IList Insurers { get { return this.insurers; } set { this.insurers = value; } }

Answer 1

You can't do that, if you association table has properties you need you must map the association as a one-to-many (to an new enity for Insurer_Section) that then has a many-to-one relation to Section.

The moment the association table becomes more than just a the primary keys and possible index columns you will need to map the association table as a separate entity linking the two entities (Insurer and Section together with the association information, like IsActive)

Answer 2

Thanks Torkel, however I can't follow your answer.

My mappings:

Insurer

    [HasAndBelongsToMany(typeof(Section), Table = "`Insurer_Section`", 
        ColumnKey = "`IdInsurer`", ColumnRef = "`IdSection`",
        Cascade = ManyRelationCascadeEnum.AllDeleteOrphan)]
    private IList<Section> Sections {
        get { return this.sections; }
        set { this.sections = value; }
    }

Section

    [HasAndBelongsToMany(typeof(Insurer), Table = "`Insurer_Section`",
        ColumnKey = "`IdSection`", ColumnRef = "`IdInsurer`",
        Cascade = ManyRelationCascadeEnum.None, Inverse = true)]
    public IList<Insurer> Insurers {
        get { return this.insurers; }
        set { this.insurers = value; }
    }

Insurer_Section

    [PrimaryKey("`Id`")]
    private int Id {
        get { return id; }
        set { id = value; }
    }

    [BelongsTo("`IdInsurer`", Cascade = CascadeEnum.SaveUpdate)]
    private Insurer Insurer {
        get { return this.insurer; }
        set { this.insurer = value; }
    }

    [BelongsTo("`IdSection`", Cascade = CascadeEnum.SaveUpdate)]
    private Section Section {
        get { return this.section; }
        set { this.section = value; }
    }

    [Property("`Active`", Default="1")]
    private bool Active {
        get { return this.active; }
        set { this.active = value; }
    }

you will need to map the association table as a separate entity linking the two entities (Insurer and Section together with the association information, like IsActive)

I think I've done this, please correct me if I'm wrong. However, this way I can't query Insurer->Insurer_Section->Active.

Answer 3

Torkel is correct. You need to not only create the new linking entity, but also change the collection for each of the entities to a collection of your new linking entity. Then change the mappings as appropriate.