Unique keys in Entity Framework 4

An existing DB schema has uniqu开发者_运维技巧e, non-primary, keys, and some foreign keys that rely on them.

Is it possible to define unique keys, which are not primary keys, in Entity Framework v4? How?

The Entity Framework 6.1 now supports uniques with both Data Annotations and Fluent API.

Data Annotations (Reference)

public class MyEntityClass
{ 
    [Index(IsUnique = true)]
    [MaxLength(255)] // for code-first implementations
    public string MyUniqueProperty{ get; set; } 
}

Fluent API (Reference)

public class MyContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder 
                .Entity<MyEntityClass>() 
                .Property(t => t.MyUniqueProperty) 
                .HasMaxLength(255) // for code-first implementations
                .HasColumnAnnotation( 
                    "Index",  
                    new IndexAnnotation(new[] 
                        { 
                            new IndexAttribute("Index") { IsUnique = true } 
                        })));
        }
    }
}

You have to apply an index and set the unique property to true. By default, indexes are non-unique according to documentation.

And also you have to install the Entity Framework 6.1 NuGet package in your project in order to use the new API for indexes.

Note about code-first implementations: A VARCHAR(MAX) cannot be part of a unique constraint. You must specify the maximum length either as a Data Annotation or in the Fluent API.

See also this MSDN blog post: http://blogs.msdn.com/b/efdesign/archive/2011/03/09/unique-constraints-in-the-entity-framework.aspx. In brief, this isn't supported in V4, though the EF team seems to have plans to support it in future releases.

I came across the same problem not long ago.

I was given a database with a few tables (see below).

 public class ClinicDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Doctor> Doctors { get; set; }
    public DbSet<Patient> Patients { get; set; }
    public DbSet<Secretary> Secretarys { get; set; }
    public DbSet<Disease> Diseases { get; set; }
    public DbSet<Consultation> Consultations { get; set; }
    public DbSet<Administrator> Administrators { get; set; }
}

The Users table was described like this:

public class User
{
    [Key]
    public Guid UserId { get; set; }

    public string UserName { get; set; }

    public string Password { get; set; }

    public string Name { get; set; }
    public string Surname { get; set; }
    public string IdentityCardNumber { get; set; }
    public string PersonalNumericalCode { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string Address { get; set; }
}

Next, I was asked to make sure that all the 'UserName' attributes would be unique. Since there is no annotation for that, I had to figure out a work-around. And here it is:

First, I changed my database context class to look like this:

public class ClinicDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Doctor> Doctors { get; set; }
    public DbSet<Patient> Patients { get; set; }
    public DbSet<Secretary> Secretarys { get; set; }
    public DbSet<Disease> Diseases { get; set; }
    public DbSet<Consultation> Consultations { get; set; }
    public DbSet<Administrator> Administrators { get; set; }

    public class Initializer : IDatabaseInitializer<ClinicDbContext>
    {
        public void InitializeDatabase(ClinicDbContext context)
        {
            if (!context.Database.Exists() || !context.Database.CompatibleWithModel(false))
            {
                if (context.Database.Exists())
                {
                    context.Database.Delete();
                }
                context.Database.Create();

                context.Database.ExecuteSqlCommand("CREATE INDEX IX_Users_UserName ON dbo.Users ( UserName )");
            }
        }
    }
}

The important part from above is the sql command which alters the table by enforcing a unique index on our desired column -> UserName in our case.

This method can be called from the main class for example:

class Program
{
    static void Main(string[] args)
    {
        Database.SetInitializer<ClinicDbContext>(new ClinicDbContext.Initializer());

        using (var ctx = new ClinicDbContext())
        {
            Console.WriteLine("{0} products exist in the database.", ctx.Users.Count());
        }

        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }
}

The final issue, which occurred when trying to run the the Program class was the following: column in table is of a type that is invalid for use as a key column in an index

To solve this issue, I just added a [MaxLength(250)] annotation for the UserName attribute.

Here is how the User class looks in the end:

public class User
{
    [Key]
    public Guid UserId { get; set; }

    [MaxLength(250)]
    public string UserName { get; set; }

    public string Password { get; set; }

    public string Name { get; set; }
    public string Surname { get; set; }
    public string IdentityCardNumber { get; set; }
    public string PersonalNumericalCode { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string Address { get; set; }
}

Hope it will solve your problem too!

I've tried defining the following tables:

• Orders [Id (primary, identity), ClientName, FriendlyOrderNum (unique)]
• OrderItems [Id (primary, identity), FriendlyOrderNum (unique), ItemName]

And a foreign key mapping from OrderItems.FriendlyOrderNum (Mant) to Orders.FriendlyOrderNum (one).

If unique non-primary keys are possible the following SSDL should work:

<Schema Namespace="EfUkFk_DbModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
    <EntityContainer Name="EfUkFk_DbModelStoreContainer">
      <EntitySet Name="OrderItems" EntityType="EfUkFk_DbModel.Store.OrderItems" store:Type="Tables" Schema="dbo" />
      <EntitySet Name="Orders" EntityType="EfUkFk_DbModel.Store.Orders" store:Type="Tables" Schema="dbo" />
    </EntityContainer>
    <EntityType Name="OrderItems">
      <Key>
        <PropertyRef Name="RowId" />
      </Key>
      <Property Name="RowId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="OrderNum" Type="char" Nullable="false" MaxLength="5" />
      <Property Name="ItemName" Type="varchar" MaxLength="100" />
    </EntityType>
    <!--Errors Found During Generation:
  warning 6035: The relationship 'FK_OrderItems_Orders' has columns that are not part of the key of the table on the primary side of the relationship. The relationship was excluded.
  -->
    <EntityType Name="Orders">
      <Key>
        <PropertyRef Name="RowId" />
      </Key>
      <Property Name="RowId" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="ClientName" Type="varchar" MaxLength="100" />
      <Property Name="OrderNum" Type="char" Nullable="false" MaxLength="5" />
    </EntityType>

  <!-- AsafR -->
    <Association Name="FK_OrderItems_Orders">
      <End Role="Orders" Type="EfUkFk_DbModel.Store.Orders" Multiplicity="1">
      </End>
      <End Role="OrderItems" Type="EfUkFk_DbModel.Store.OrderItems" Multiplicity="*" />
      <ReferentialConstraint>
        <Principal Role="Orders">
          <PropertyRef Name="OrderNum" />
        </Principal>
        <Dependent Role="OrderItems">
          <PropertyRef Name="OrderNum" />
        </Dependent>
      </ReferentialConstraint>
    </Association>
  </Schema></edmx:StorageModels>

It doesn't. There's also no possibility for adding more <key> elements in an <EntityType>.

My conclusion is that non-primary unique keys are not support in EF 4.

You can use DataAnnotations validation as well.

I've created this (UniqueAttribute) class, that inherits ValidationAttribute, and when applied to a property, the values of that column will be retrieved and validated against, during validation.

You can grab the raw code from here.