Question

I've got a model that I need to add a unique index field (this is a new field);

So I've done

[Serializable]
[HierarchyRoot]
[Index("UniqueCode", Unique = true)]
public class NamedItem : Entity, IControlledObject, IAuditTrail
{
    /// <summary>
    /// key field
    /// </summary>
    [Field, Key]
    public int Id { get; private set; }
    ...
    ...
    /// <summary>
    /// Unique Item Code
    /// </summary>
    [Field(Length=5)]
    public string UniqueCode { get; set; }
}

during the model upgrade I get the following error:

SQL error details 'Type: Unknown;'
Query 'CREATE UNIQUE INDEX [NamedItem.IX_UniqueCode] ON [dbo].[NamedItem] ([UniqueCode] ASC)  INCLUDE ([TypeId]);'
Original message 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.NamedItem' and the index name 'NamedItem.IX_UniqueCode'. The duplicate key value is (<NULL>).
The statement has been terminated.'

This unique code is in addition to the numeric Key field.

I understand why I'm getting the SQL error - what I'm not sure about is the DO.net way of fixing this.

What I really want is a way of ensuring that if the UniqueCode is present the DB will enforce the uniqueness.

Accepted Answer

Greetings Richard,

Sorry for the delay.

I suppose you use SQL Server 2008 or higher. If so, you could use one of the exciting features of DataObjects.Net - partial indexes support. Here is an example:

[HierarchyRoot]
[Index("UniqueCode", Unique = true, Filter = "UniqueCodeIndex")]
public class NamedItem : Entity, IControlledObject, IAuditTrail {

public static Expression<Func<NamedItem, bool>> UniqueCodeIndex()
{
  return i => i.UniqueCode != null;
}

[Field, Key]
public int Id { get; private set; }
...
...
[Field(Length=5)]
public string UniqueCode { get; set; }

}

P.S.
The feature was implemented in DataObjects.Net 4.4.1.

Subscription:

Related questions