Similar to Tobias Schittkowski's answer but C# and has the capability to have multiple fields in the constrtaints.
To use this, just place a [Unique] on any field you wish to be unique. For strings, you will have to do something like (note the MaxLength attribute):
[Unique]
[MaxLength(450)] // nvarchar(450) is max allowed to be in a key
public string Name { get; set; }
because the default string field is nvarchar(max) and that will not be allowed in a key.
For multiple fields in the constraint you can do:
[Unique(Name="UniqueValuePairConstraint", Position=1)]
public int Value1 { get; set; }
[Unique(Name="UniqueValuePairConstraint", Position=2)]
public int Value2 { get; set; }
First, the UniqueAttribute:
/// <summary>
/// The unique attribute. Use to mark a field as unique. The
/// <see cref="DatabaseInitializer"/> looks for this attribute to
/// create unique constraints in tables.
/// </summary>
internal class UniqueAttribute : Attribute
{
/// <summary>
/// Gets or sets the name of the unique constraint. A name will be
/// created for unnamed unique constraints. You must name your
/// constraint if you want multiple fields in the constraint. If your
/// constraint has only one field, then this property can be ignored.
/// </summary>
public string Name { get; set; }
/// <summary>
/// Gets or sets the position of the field in the constraint, lower
/// numbers come first. The order is undefined for two fields with
/// the same position. The default position is 0.
/// </summary>
public int Position { get; set; }
}
Then, include a useful extension to get the database table name from a type:
public static class Extensions
{
/// <summary>
/// Get a table name for a class using a DbContext.
/// </summary>
/// <param name="context">
/// The context.
/// </param>
/// <param name="type">
/// The class to look up the table name for.
/// </param>
/// <returns>
/// The table name; null on failure;
/// </returns>
/// <remarks>
/// <para>
/// Like:
/// <code>
/// DbContext context = ...;
/// string table = context.GetTableName<Foo>();
/// </code>
/// </para>
/// <para>
/// This code uses ObjectQuery.ToTraceString to generate an SQL
/// select statement for an entity, and then extract the table
/// name from that statement.
/// </para>
/// </remarks>
public static string GetTableName(this DbContext context, Type type)
{
return ((IObjectContextAdapter)context)
.ObjectContext.GetTableName(type);
}
/// <summary>
/// Get a table name for a class using an ObjectContext.
/// </summary>
/// <param name="context">
/// The context.
/// </param>
/// <param name="type">
/// The class to look up the table name for.
/// </param>
/// <returns>
/// The table name; null on failure;
/// </returns>
/// <remarks>
/// <para>
/// Like:
/// <code>
/// ObjectContext context = ...;
/// string table = context.GetTableName<Foo>();
/// </code>
/// </para>
/// <para>
/// This code uses ObjectQuery.ToTraceString to generate an SQL
/// select statement for an entity, and then extract the table
/// name from that statement.
/// </para>
/// </remarks>
public static string GetTableName(this ObjectContext context, Type type)
{
var genericTypes = new[] { type };
var takesNoParameters = new Type[0];
var noParams = new object[0];
object objectSet = context.GetType()
.GetMethod("CreateObjectSet", takesNoParameters)
.MakeGenericMethod(genericTypes)
.Invoke(context, noParams);
var sql = (string)objectSet.GetType()
.GetMethod("ToTraceString", takesNoParameters)
.Invoke(objectSet, noParams);
Match match =
Regex.Match(sql, @"FROM\s+(.*)\s+AS", RegexOptions.IgnoreCase);
return match.Success ? match.Groups[1].Value : null;
}
}
Then, the database initializer:
/// <summary>
/// The database initializer.
/// </summary>
public class DatabaseInitializer : IDatabaseInitializer<PedContext>
{
/// <summary>
/// Initialize the database.
/// </summary>
/// <param name="context">
/// The context.
/// </param>
public void InitializeDatabase(FooContext context)
{
// if the database has changed, recreate it.
if (context.Database.Exists()
&& !context.Database.CompatibleWithModel(false))
{
context.Database.Delete();
}
if (!context.Database.Exists())
{
context.Database.Create();
// Look for database tables in the context. Tables are of
// type DbSet<>.
foreach (PropertyInfo contextPropertyInfo in
context.GetType().GetProperties())
{
var contextPropertyType = contextPropertyInfo.PropertyType;
if (contextPropertyType.IsGenericType
&& contextPropertyType.Name.Equals("DbSet`1"))
{
Type tableType =
contextPropertyType.GetGenericArguments()[0];
var tableName = context.GetTableName(tableType);
foreach (var uc in UniqueConstraints(tableType, tableName))
{
context.Database.ExecuteSqlCommand(uc);
}
}
}
// this is a good place to seed the database
context.SaveChanges();
}
}
/// <summary>
/// Get a list of TSQL commands to create unique constraints on the given
/// table. Looks through the table for fields with the UniqueAttribute
/// and uses those and the table name to build the TSQL strings.
/// </summary>
/// <param name="tableClass">
/// The class that expresses the database table.
/// </param>
/// <param name="tableName">
/// The table name in the database.
/// </param>
/// <returns>
/// The list of TSQL statements for altering the table to include unique
/// constraints.
/// </returns>
private static IEnumerable<string> UniqueConstraints(
Type tableClass, string tableName)
{
// the key is the name of the constraint and the value is a list
// of (position,field) pairs kept in order of position - the entry
// with the lowest position is first.
var uniqueConstraints =
new Dictionary<string, List<Tuple<int, string>>>();
foreach (PropertyInfo entityPropertyInfo in tableClass.GetProperties())
{
var unique = entityPropertyInfo.GetCustomAttributes(true)
.OfType<UniqueAttribute>().FirstOrDefault();
if (unique != null)
{
string fieldName = entityPropertyInfo.Name;
// use the name field in the UniqueAttribute or create a
// name if none is given
string constraintName = unique.Name
?? string.Format(
"constraint_{0}_unique_{1}",
tableName
.Replace("[", string.Empty)
.Replace("]", string.Empty)
.Replace(".", "_"),
fieldName);
List<Tuple<int, string>> constraintEntry;
if (!uniqueConstraints.TryGetValue(
constraintName, out constraintEntry))
{
uniqueConstraints.Add(
constraintName,
new List<Tuple<int, string>>
{
new Tuple<int, string>(
unique.Position, fieldName)
});
}
else
{
// keep the list of fields in order of position
for (int i = 0; ; ++i)
{
if (i == constraintEntry.Count)
{
constraintEntry.Add(
new Tuple<int, string>(
unique.Position, fieldName));
break;
}
if (unique.Position < constraintEntry[i].Item1)
{
constraintEntry.Insert(
i,
new Tuple<int, string>(
unique.Position, fieldName));
break;
}
}
}
}
}
return
uniqueConstraints.Select(
uc =>
string.Format(
"ALTER TABLE {0} ADD CONSTRAINT {1} UNIQUE ({2})",
tableName,
uc.Key,
string.Join(",", uc.Value.Select(v => v.Item2))));
}
}
ObjectContextorDbContext. – Ritualize