How to use a varchar column other than Id for PK?

Question

I have a table which has Code as PK, but I get the exception below in DefaultEditionCreator.cs once I try to run the application.

[Table("Test")]
public class Test: FullAuditedEntity<string>
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    new public int Id { get; set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [MaxLength(NVarcharLength14), DataType(DataType.Text)]
    public virtual string Code { get; set; }
}

Declared repository:

private readonly IRepository<Article, string> _articleRepository;

Exception:

System.InvalidOperationException: 'The specified field 'k__BackingField' of type 'int' cannot be used for the property 'Article.Id' of type 'string'. Only backing fields of types that are assignable from the property type can be used.'

I'm getting the same error while running Update-Database and Add-Migration.

Update 1

@aaron Thanks a lot for your help. I have tried the steps as suggested by you, but I'm getting an error while updating and deleting records.

Exception:

ERROR 2018-02-12 06:13:23,049 [30 ] Mvc.ExceptionHandling.AbpExceptionFilter - An error occurred while updating the entries. See the inner exception for details. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot update identity column 'Id'.

public async Task UpdateTest()
{
   var entity = GetAll().Where(x => x.TestId == "One").FirstOrDefault();
   await UpdateAsync(entity);
}

public async Task DeleteTest()
{
   await DeleteAsync(x => x.TestId == "One"); 
}

public class Test : FullAuditedEntity
{
   // PK
   public string TestId { get; set; }

   // Unique constraint
   public int TestId2 { get; set; }
}

Update 2

I'm trying to disable SoftDelete by referring to Disable SoftDelete for AbpUserRole, but it's still doing SoftDelete, not deleting the row from DB. Please find the screenshot:

public class TestAppService : MyProjectAppServiceBase, ITestAppService
{
    public Task DeleteTest()
    {
        using (CurrentUnitOfWork.DisableFilter(AbpDataFilters.SoftDelete))
        {
            return _testRepository.DeleteTest();
        }
    }
}

MyDBContext.cs:

protected override void CancelDeletionForSoftDelete(EntityEntry entry)
{
    if (IsSoftDeleteFilterEnabled)
    {
        base.CancelDeletionForSoftDelete(entry);
    }
}

The solution works fine, But its giving following exception while running test case to create Test entity.

SQLite Error 19: 'NOT NULL constraint failed: Test.Id'.

Answer 1

The exception is because you inherited FullAuditedEntity<string>, which specifies that Id is of type string, and then did new to change the type to int. This hiding results in a conflict for EF.

Here's how you can:

1. Have an auto-increment Id column of type int
2. Have a primary key column of type string
3. Have a unique constraint column (as requested in a related forum)

Code:

public class Test: FullAuditedEntity
{
    // PK
    [MaxLength(NVarcharLength14), DataType(DataType.Text)]
    public virtual string Code { get; set; }

    // Unique constraint
    public int MyUniqueId { get; set; }
}

public class AbpProjectNameDbContext : AbpZeroDbContext<Tenant, Role, User, AbpProjectNameDbContext>
{
    /* Define a DbSet for each entity of the application */    
    public DbSet<Test> Tests { get; set; }

    public AbpProjectNameDbContext(DbContextOptions<AbpProjectNameDbContext> options) : base(options) {}

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Test>().Property(t => t.Id).ValueGeneratedOnAdd(); // Auto-increment
        modelBuilder.Entity<Test>().HasAlternateKey(t => t.Id);                // Auto-increment, closed-wont-fix: https://github.com/aspnet/EntityFrameworkCore/issues/7380
        modelBuilder.Entity<Test>().HasKey(t => t.Code);                       // PK
        modelBuilder.Entity<Test>().HasIndex(t => t.MyUniqueId).IsUnique();    // Unique constraint
    }
}

Generated migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "Tests",
        columns: table => new
        {
            Code = table.Column<string>(nullable: false),
            Id = table.Column<int>(nullable: false)
                .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
            MyUniqueId = table.Column<int>(nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Tests", x => x.Code);
        });

    migrationBuilder.CreateIndex(
        name: "IX_Tests_MyUniqueId",
        table: "Tests",
        column: "MyUniqueId",
        unique: true);
}

Usage:

public async Task MyMethod()
{
    await _repository.InsertAndGetIdAsync(new Test
    {
        Code = "One",
        MyUniqueId = 1
    });

    // Valid
    await _repository.InsertAndGetIdAsync(new Test
    {
        Code = "Two",
        MyUniqueId = 2
    });

    try
    {
        await _repository.InsertAndGetIdAsync(new Test
        {
            Code = "One", // PK conflict
            MyUniqueId = 3
        });
    }
    catch (Exception e)
    {
    }

    try
    {
        await _repository.InsertAndGetIdAsync(new Test
        {
            Code = "Three",
            MyUniqueId = 1 // Unique constraint conflict
        });
    }
    catch (Exception e)
    {
        throw;
    }

    return null;
}

---

For completeness sake, this question is the first of a series of other Stack Overflow questions:

1. This question. (Sep 11)
2. Getting Ambiguous match found exception while calling DeleteAsync (Sep 11)
3. Cannot delete record from table which has Identity column (Sep 12)
4. How to make composite unique key in ASP.NET Boilerplate? (Sep 13)

Answer 2

Do you mean use varchar type as primary key? Just declare the entity class like this:

public class Article: Entity<string>
{
  //You should comment this line
  //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  //new public int Id { get; set; }
}

Then you can use repository:

private readonly IRepository<Article, string> _articleRepository;