Postgres GENERATED AS IDENTITY conflicted with .NET Entity Model

Question

I am building and API using .NET Core, PostgresSQL, and SQLKata+dapper (NOT EF)

I have this 2 tables

CREATE TABLE "UserProfile"(
    "Id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "Deleted" BOOLEAN NOT NULL DEFAULT FALSE,
    "CreatedBy" VARCHAR(50) NOT NULL,
    "CreatedTime" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_DATE,
    "LastModifiedBy" VARCHAR(50),
    "LastModifiedTime" TIMESTAMP WITH TIME ZONE,
    "FirstName" VARCHAR(50) NOT NULL,
    "LastName" VARCHAR(50),
    "Email" VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE "UserLogin"(
    "Id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    "Deleted" BOOLEAN NOT NULL DEFAULT FALSE,
    "CreatedBy" VARCHAR(50) NOT NULL,
    "CreatedTime" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_DATE,
    "LastModifiedBy" VARCHAR(50),
    "LastModifiedTime" TIMESTAMP WITH TIME ZONE,
    "Username" VARCHAR(50) UNIQUE NOT NULL,
    "Password" VARCHAR(200) NOT NULL,
    "IsLocked" BOOLEAN NOT NULL DEFAULT FALSE,
    "IsActive" BOOLEAN NOT NULL DEFAULT FALSE,
    "UserProfileId" INT NOT NULL,
    CONSTRAINT "FK_UserLogin_UserProfile" FOREIGN KEY("UserProfileId") REFERENCES "UserProfile"("Id")
);

And in my C# I create entities to contain the table

public class BaseEntity
{
    public int Id { set; get; }
    public bool Deleted { set; get; }
    public string CreatedBy { set; get; }
    public DateTime CreatedTime { set; get; }
    public string LastModifiedBy { set; get; }
    public DateTime? LastModifiedTime { set; get; }

    public BaseEntity()
    {
        this.Deleted = false;
        this.CreatedBy = "SYSTEM"; //TODO: Change to UserPrincipal
        this.CreatedTime = DateTime.Now;
        this.LastModifiedBy = null;
        this.LastModifiedTime = null;
    }
}

public class UserLogin : BaseEntity 
{
    public string Username { set; get; }
    public string Password { set; get; }
    public bool IsLocked { set; get; }
    public bool IsActive { set; get; }
    public int UserProfileId { set; get; }
}

public class UserProfile:BaseEntity
{
    public string FirstName { set; get; }
    public string LastName { set; get; }
    public string Email { set; get; }

}

Then here's come the problem, if I want to insert new record to database

public async Task<ResponseBase> SignUp(SignUpRequest request)
    {
        ResponseBase result = new ResponseBase();
        try
        {
            using var conn = await db.CreateConnectionAsync();
            var query = new QueryFactory(conn, new PostgresCompiler());
            var scope = conn.BeginTransaction();

            var userProfileId = await query.Query(UserProfileConst.TableName)
                                           .InsertAsync(new UserProfile
                                           {
                                               FirstName = request.FirstName,
                                               LastName = request.LastName,
                                               Email = request.Email
                                           });

            var affectedUserLogin = await query.Query(UserLoginConst.TableName)
                                               .InsertAsync(new UserLogin
                                               {
                                                   Username = request.Username,
                                                   Password = Argon2.Hash(request.Password),
                                                   IsActive = true,
                                                   IsLocked = false,
                                                   UserProfileId = userProfileId
                                               });
            
            if (userProfileId > 0 && affectedUserLogin > 0)
            {
                scope.Commit();
                result.Success("Success Insert New User");
            }
            else
            {
                scope.Rollback();
                result.Failed("Success Insert New User");
            }
        }
        catch (Exception e)
        {
            result.Failed(e.Message);
        }
        return result;
    } 

The new UserLogin({ }) and new UserProfile({ }) will automatically set default value to Id from BaseEntity to 0 And it will resulting error in Postgres

{
    "acknowledge": 0,
    "message": "428C9: cannot insert into column \"Id\"",
    "isSuccess": false
}

I know i can just create new Object without defining its class, but I wonder if there are any other way

Anyone got an idea?

Answer 1

You can use the KeyAttribute annotation on the column to mark it as Primary key so it will be ignored on the Insert/Update queries.

Also you can use the Ignore attribute to ignore arbitrary fields

public class BaseEntity
{
    [Key]
    public int PrimaryKeyColumnWillBeIgnoredOnInsert { set; get; }
   
    [Ignore]
    public int ThisColumnWillBeIgnoredAlso { set; get; }
}

Check https://github.com/sqlkata/querybuilder/blob/master/QueryBuilder/ColumnAttribute.cs#L27