Do not allow saving duplicate records

Question

I am developing a system using ASP.Net MVC and Entity Framework.

How do I not allow equal data to be recorded in a table? For example, don’t allow login: x to be registered more than once.

Answer 1

On the other hand, with the EntityFramework 6.1 you can use of Indexattribute.

public abstract class User
{
    [Required]
    [StringLength(50)]
    [Index("IX_User_Login", IsUnique = true)]
    public string Login { get; set; }
    ....
}

An example for a composite key:

public abstract class User
{
    [Required]
    [Index("IX_User_Empresa", 1, IsUnique = true)]
    public int EmpresaId { get; set; }

    [Required]
    [StringLength(50)]
    [Index("IX_User_Empresa", 2, IsUnique = true)]
    public string Login { get; set; }
    ....
}

Answer 2

A way to do this, for a class User, for example, would put the field Login as Primary Key.

public class User 
{
    [Key]
    public string Login { get; set; }
    public string Password { get; set; }
    ...
}

The Entityframework does not have the attribute UniqueKey. You could raise him.

Create the attribute UniqueKey would imply in creating a validation for your back-end at least and make a mechanism to insert into the database the unions.

The Attribute:

public class UniqueKeyAttribute : Attribute
{ 
    ... // incluir a validação para o back-end
}

Creating the unions, composed, in the database:

ATTENTION: I can’t guarantee this way of creating unions as generic for all databases.

public class DatabaseUniqueKeyConfiguration
{
    // Cria as Unique keys no banco de dados
    public static void CreateUniqueKeys(Context context)
    {
        //Fetch all the father class's public properties 
        var masterProperties = typeof(DbContext).GetProperties().Select(x => x.Name).ToList();

        //Percorre cada DBSet<> do DbContext
        foreach (var item in context.GetType().GetProperties().Where(p => masterProperties.IndexOf(p.Name) < 0).Select(x => x))
        {
            //busca o tipo de "T" 
            Type entityType = item.PropertyType.GetGenericArguments()[0];

            // Cria as chaves únicas
            var fields = from f in entityType.GetProperties()
                         where f.GetCustomAttributes(typeof(Domain.Attributes.UniqueKeyAttribute), true).Count() > 0
                         select f.Name;

            var uniqueKeys = "";
            foreach (string s in fields)
            {
                if (string.IsNullOrEmpty(uniqueKeys) || string.IsNullOrWhiteSpace(uniqueKeys))
                    uniqueKeys = s;
                else
                    uniqueKeys += ", " + s;
            }

            var tableName = entityType.Name;
            var attr = System.Attribute.GetCustomAttributes(entityType).SingleOrDefault(x=>x.GetType() == typeof(TableAttribute)) as TableAttribute;
            if (attr != null)
                tableName = attr.Name;

            if (!string.IsNullOrEmpty(uniqueKeys) && !string.IsNullOrWhiteSpace(uniqueKeys))
                context.Database.ExecuteSqlCommand("alter table " + tableName + " add unique(" + uniqueKeys + ")");
        }
    }
}

Dai then you could create your class by adding the property UniqueKey.

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [UniqueKey]
    public string Login
}

Good, so you would have the desired attribute validating on back-end and could create a validation front-end consulting by AJAX.

Answer 3

Your question has become too broad, you need to go into a little more detail. For example: What type of database are you using?

The control of the records is done directly in the database and you must treat this rule directly in it.

The database has a property called PRIMARY KEY, I believe you’ve heard of it. It controls the records not allowing you to duplicate a given field, follows link with examples in several database languages of how to apply to PK.

http://www.w3schools.com/sql/sql_primarykey.asp

EXAMPLE: Mysql / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (CAMPO_A_SER_APLICADO)

Answer 4

You can do it many ways. The solution of @andervoc, in the model is perfect. I would only add this command in the database, with this you would be more secure:

ALTER TABLE TABELA ADD CONSTRAINT NOMEDATABELA_UK01 UNIQUE (CAMPOS);

Practical example:

http://sqlfiddle.com/#! 6/f10a5/1

With this the bank would make the mistake:

Violation of UNIQUE KEY constraint 'USUARIO_UK01'. Cannot insert duplicate key in object 'dbo.USUARIO'. The duplicate key value is (Maria).: insert into USUARIO values ('Maria')

Answer 5

In your data class, using code first, you can include the "Key" attribute above the property. So you indicate that field is primary key.

[Key, MaxLength(36), MinLength(36)]
public string CampoId { get; set; }