How to use other column as PK not ID column? #4605

manojreddy created 6 years ago

Hi,

I want to use Some other columns (datatype can be int, long, varchar) as PK or composite PK. But framework supports ID column as autoincrement and PK. This feature is giving me a lot of trouble.

I have posted a lot of queries on SO related to this query, but everyone suggested to use ID as PK. I also tried to make other columns as PK, but it was also giving me lot problems.

Please refer the following posts and please please please help.

[https://stackoverflow.com/questions/46153399/how-to-use-a-varchar-column-other-than-id-column-using-asp-net-boilerplate]) [https://stackoverflow.com/questions/46155968/getting-ambiguous-match-found-exception-while-calling-deleteasync]) [https://stackoverflow.com/questions/46173881/cannot-delete-record-from-table-which-has-identity-column]) [https://stackoverflow.com/questions/46192731/how-to-make-composite-unique-key-in-asp-net-boilerplate])

So my basic requirement is to Have a ID auto increment column Have a primary key column or a composite primary key Have some unique constraint columns I should not face the above mentioned issues.

14 Answer(s)

0

aaron created 6 years ago
Support Team

This seems to answer your question: https://stackoverflow.com/questions/46192731/how-to-make-composite-unique-key-in-asp-net-boilerplate/46196842#46196842

Can you give a concrete example?
0
manojreddy created 6 years ago
This link doesn’t answer my question.

My requirement is very simple, which I have already mentioned in question.

Have a ID auto increment column

Have a primary key column or a composite primary key

Have some unique constraint columns

I should not face the above mentioned issues.

I tried the link solution when I failed to create other columns as PK. Then I tried to create unique constraints on columns.
0

aaron created 6 years ago
Support Team

Can you give a concrete example?
0

manojreddy created 6 years ago

Requirement is very clear. What do you mean by concrete example?
0

manojreddy created 6 years ago

Suppose you have a table named as TestTbl, which has Id, TestId (varchar), TestId2 and some other columns. I need to have Id as autoincrement identity. TestId as PK. TestId2 unique constraint.

aaron created 6 years ago

Support Team

Code:

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

    // Unique constraint
    public int TestId2 { 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>().HasKey(t => t.TestId);                     // PK
        modelBuilder.Entity<Test>().HasIndex(t => t.TestId2).IsUnique();       // Unique constraint
    }
}

Generated migration:

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

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

Usage:

public async Task<IActionResult> Index()
{
    await _repository.InsertAndGetIdAsync(new Test
    {
        TestId = "One",
        TestId2 = 1
    });

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

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

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

    return Redirect("/swagger");
}

0

manojreddy created 6 years ago

@Aaron,

Thanks a lot, I will try this and let you know if I face any challenge.
0

manojreddy created 6 years ago

@Aaron,

You are implementing from Entity not from FullAuditedEntity. So any disadvantage or side effect of not using FullAuditedEntity.

What about CreatorUserId, CreationTime, ModificationTime, DeletionTime, DeleterUserId and SoftDelete functionality? I want all these features.
0
bbakermmc created 6 years ago
If you want to make life easy and use the pre-built stuff you just need to override the Id column.

This will let you use the IRepo etc which all use Id, but it maps to the column defined in the DB.

public partial class Answer : AuditBase<int>, IEntityDto<int> { [Column("AnswerId")] [Key] public override int Id { get; set; } } So you would inherit from Entity<type>.
0

aaron created 6 years ago
Support Team

You are implementing from Entity not from FullAuditedEntity. So any disadvantage or side effect of not using FullAuditedEntity.

No.
0

manojreddy created 6 years ago

So can I implement from FullAuditedEntity instead of Entity?
0

aaron created 6 years ago
Support Team

Yes, I tried it. Next time, you can too.

manojreddy created 6 years ago

@Aaron,

Thanks for your help always.

I have tried the steps provided by you, Its working fine for Insert records. But it's giving error while updating and deleting records.

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; }
}

public class TestRepository : MyCompanyRepositoryBase<Test, int>, ITestRepository
{
	private readonly IActiveTransactionProvider _transactionProvider;

	public TestRepository(IDbContextProvider<MyCompanyDbContext> dbContextProvider,
		IObjectMapper objectMapper)
	: base(dbContextProvider, objectMapper)
	{
	}

	public TestRepository(IDbContextProvider<MyCompanyDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider,
		IObjectMapper objectMapper)
	: base(dbContextProvider, objectMapper)
	{
		_transactionProvider = transactionProvider;
	}

	public async Task CreateTest()
	{

		await InsertAsync(new Test
		{
			TestId = "One",
			TestId2 = 1
		});

	}

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

	public Task GetSearchTest()
	{
		throw new NotImplementedException();
	}

	public Test GetTest()
	{
		throw new NotImplementedException();
	}

	public Test GetTestDetailsforEdit()
	{
		return GetAll().Where(x => x.TestId == "One").FirstOrDefault();
	}

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

0
bbakermmc created 6 years ago
Like I posted before. Not sure why youre trying to fight the system. If your DB is setup to Be TABLENAME<ID> then just override ID in your Model:

public partial class Answer : AuditBase<string>, IEntityDto<string> { [Column("TestId")] [Key] public override string Id { get; set; } }

Then you can just call the standard delete function w/out an override.

This works fine for us and all of our tables have a PK of TableNameId

Have an answer to this question? Log in and write your answer.