NHibernate mapping a component list with a unique key constraint

Question

I'm trying to map a list of components that contains a unique key constraint on its values.

A 'trimmed' sample of the classes are below:

public class MyObject
{
    public virtual int Id { get; set; }
    public virtual IList<Alias> Aliases { get; set; }
}

public class Alias
{
    public Alias(string type, string value)
    {
        Type = type;
        Value = value;
    }

    public virtual string Type { get; protected set; }
    public virtual string Value { get; protected set; }
}

And the relevant NHibernate mapping from MyObject (types have been trimmed for brevity):

  <list cascade="all-delete-orphan" name="Aliases" table="MyObject_Aliases" mutable="true">
      <key>
        <column name="MyObject_id" />
      </key>
      <index>
        <column name="`Index`" />
      </index>
      <composite-element class="Alias">
        <property name="Type" type="System.String">
          <column name="Type" unique-key="UK_Alias" />
        </property>
        <property name="Value" type="System.String">
          <column name="Value" unique-key="UK_Alias" />
        </property>
      </composite-element>
    </list>

The intention is to prevent two MyObjects from containing the same Alias.

This works perfectly well when adding a new Alias to MyObject. However, if you were to remove an alias from the list where there are aliases after the removed alias then NHibernate tries to reorder the list which causes a key violation.

A sample of the SQL that NHibernate is generating is:

-- statement #1, saving MyObject.Aliases
INSERT INTO MyObject_Aliases
           (MyObject_id,
            "Index",
            Type,
            Value)
VALUES     (101 /* @p0 */,
            0 /* @p1 */,
            'A' /* @p2 */,
            'ALIAS1' /* @p3 */)

INSERT INTO MyObject_Aliases
           (MyObject_id,
            "Index",
            Type,
            Value)
VALUES     (101 /* @p0 */,
            1 /* @p1 */,
            'A' /* @p2 */,
            'ALIAS2' /* @p3 */)

-- statement #2, updating MyObject.Aliases after removing the first list item
UPDATE MyObject_Aliases
SET    Type = 'A' /* @p0 */,
       Value = 'ALIAS2' /* @p1 */
WHERE  MyObject_id = 101 /* @p2 */
       AND "Index" = 0 /* @p3 */

Statement #2 in this example throws:

NHibernate.Exceptions.GenericADOException : could not update collection rows: [MyObject.Aliases#101][SQL: UPDATE MyObject_Aliases SET Type = ?, Value = ? WHERE MyObject_id = ? AND "Index" = ?]
  ----> System.Data.SQLite.SQLiteException : Abort due to constraint violation
columns Type, Value are not unique

The exception itself makes perfect sense based on what is actually happening, but how do I get this to actually work as expected?

Do you need to use a list mapping, or can you use a set instead? — Vadim
– Vadim, Commented Mar 11, 2011 at 16:31
Aside from my own ignorance, there does not appear to be any reason that I can not use a set instead which does in fact appear to behave as I would expect aside from having to implement my own comparator which isn't a big deal. If you'd care to turn your comment into an actual answer for me I'd love to be able mark this question as answered! — Chris Stavropoulos
– Chris Stavropoulos, Commented Mar 11, 2011 at 17:19

Vadim · Accepted Answer · 2011-03-11 17:22:38Z

1

If your collection doesn't need to be indexed you should probably use a set mapping instead. A set is a collection type that guarantees that there are no duplicates. A list is a collection type which can be indexed.

answered Mar 11, 2011 at 17:22

Vadim

18k5 gold badges41 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Stavropoulos Over a year ago

I do lose the ability to access my collection with an index but sifting through the set is made easier in that regard with LINQ anyway.

Vadim Over a year ago

@Chris, you can still index your collection in code, by exposing a List as a property, a good example can be found here stackoverflow.com/questions/824587/…

MarioDS Over a year ago

I have the exact same problem as this question, but with a genuine need to use a list mapping. How can I solve this still?

Collectives™ on Stack Overflow

NHibernate mapping a component list with a unique key constraint

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related