Unique keys in Entity Framework 4

Question

An existing DB schema has unique, 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?

Grossman

Answer 1

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.

Grossman

Answer 2

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.

Wolbrom

This compiles for me, but i get a runtime error that Column 'Email' in table 'dbo.Users' is of a type that is invalid for use as a key column in an index. Email is a public string, just like MyUniqueProperty. – Fellini

It is working fine here, and I made many attempts to reproduce your error, but I wasnt able to. Would you open a new question giving all the details involved, including EF version and the full stack trace and your class? Please add a comment here with the link so I can investigate. tx! – Wolbrom

My issue is because I'm using a code-first implementation, which creates the Email column as NVARCHAR(MAX) by default. I made an edit to address this case. Your solution works fine for me now! – Fellini

@Fellini Thats odd. I am using SQL Compact here and CF too, drop-create strategy and I got no errors. Maybe it is database related? Tx for the update tho. – Wolbrom

I'm using SQL Server 2014. That could be the difference. – Fellini

Answer 3

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.

Flexion

Unfortunately the future release will not be the next one (EF 5.0 and .NET 4.5) – Daladier

Answer 4

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!

Molar

Answer 5

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.

Geiss