Insert with PK specified ignores explicit PK value suppplied

[b2soft]

Hello. I have 2 tables representing 1-1 relationship:

• user_auth_data containing user_id as PK auto incerement value
• user_special_data which also has user_auth_data as PK + FK constraint to user_auth_data.user_id

Classes are mapped like this:

[Table("user_auth_data")]
public class UserAuthDataModel
{
    [Key, Identity]
    public int UserId { get; set; }
    public string Foo { get;set; }
}

[Table("user_special_data")]
public class UserSpecialDataModel
{
    [Key, Identity]
    public int UserId { get; set; }
    public string Bar { get; set; }
        public string Baz { get; set; }
}

I insert a record to user_auth_data to get generated user_id, and now I have to add a row to user_special_data and it inserts null user_id.

var id = .. // get id from user_auth_data;
var newData = new UserSpecialDataModel{ UserId = id };
await repository.InsertAsync(newData);

I quickly logged the queries generated (Postgresql), and it seems like sql generated is not correct: 2023-08-12 02:45:15.716 CEST [14780] LOG: execute <unnamed>: INSERT INTO user_special_data (bar, baz) VALUES ($1, $2) RETURNING user_id so it seems like user_id is ignored completely.

To note, direct query await repository.Connection.ExecuteAsync($"INSERT INTO user_special_data (user_id) VALUES ({id})"); works as expected.

Is it a bug or intended behavior?

[phnx47]

@b2soft Intended behavior.

Look at tests: https://github.com/phnx47/dapper-repositories/blob/078484976c0f393cac994154b01a4c21b9bc7523/tests/SqlGenerator.Tests/MsSqlGeneratorTests.cs#L378-L384

[b2soft]

Thanks for the answer, so the only way to put the PK into - raw SQL query?

[phnx47]

@b2soft If you don't want to use auto increment and put value yourself you should remove Identity attribute.

Look at: https://github.com/phnx47/dapper-repositories/blob/4eb234a348d5cd436b3b9bd1cc273c3439ba7509/src/SqlGenerator/SqlGenerator.GetInsert.cs#L17-L20