Repodb: Bug: RepoDb.Exceptions.PrimaryFieldNotFoundException: The non-identity primary field must be present during insert operation.

[VincenzoCarlino]

Bug Description

I'm not able to create record into table. The problem is that if I don't specify the value of the PK (an int, autoincrement), the operation fails with exception reported in the title,

Exception Message:

RepoDb.Exceptions.PrimaryFieldNotFoundException: The non-identity primary field must be present during insert operation.
   at RepoDb.StatementBuilders.BaseStatementBuilder.CreateInsert(QueryBuilder queryBuilder, String tableName, IEnumerable`1 fields, DbField primaryField, DbField identityField, String hints)
   at RepoDb.StatementBuilders.PostgreSqlStatementBuilder.CreateInsert(QueryBuilder queryBuilder, String tableName, IEnumerable`1 fields, DbField primaryField, DbField identityField, String hints)
   at RepoDb.CommandTextCache.GetInsertTextInternal(InsertRequest request, IEnumerable`1 fields)
   at RepoDb.CommandTextCache.GetInsertTextAsync(InsertRequest request, CancellationToken cancellationToken)
   at RepoDb.Contexts.Providers.InsertExecutionContextProvider.CreateAsync[TEntity](IDbConnection connection, String tableName, IEnumerable`1 fields, String hints, IDbTransaction transaction, IStatementBuilder statementBuilder, CancellationToken cancellationToken)
   at RepoDb.DbConnectionExtension.InsertAsyncInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder, CancellationToken cancellationToken)
   at RepoDb.DbRepository`1.InsertAsync[TEntity,TResult](TEntity entity, IEnumerable`1 fields, String hints, IDbTransaction transaction, CancellationToken cancellationToken)
   at IssueTracker_BE.Persistence.Repositories.UserRepository.InsertUserAsync(User user) in C:\Users\Vincenzo\Documents\personale\IssueTracker\IssueTracker-BE\src\IssueTracker-BE.Persistence\Repositories\UserRepository.cs:line 22
   at IssueTracker_BE.Application.Services.UserService.InsertUserAsync(UserInput input) in C:\Users\Vincenzo\Documents\personale\IssueTracker\IssueTracker-BE\IssueTracker-BE.Application\Services\UserService.cs:line 38
   at IssueTracker_BE.WebApi.Controllers.UsersController.AddUser(UserInput input) in C:\Users\Vincenzo\Documents\personale\IssueTracker\IssueTracker-BE\src\IssueTracker-BE.WebApi\Controllers\UsersController.cs:line 24
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
!!! The non-identity primary field must be present during insert operation.

Schema and Model:

        internal class UserMapping
    {
        public static void Map()
        {
            var userMappingDefinition = FluentMapper.Entity<User>();
            userMappingDefinition
                .Table($"public.{Constants.DbConstants.User.TableName}")

                .Identity(new Field("id"))
                .Primary(new Field("id"))
                .Column(e => e.Id, Constants.DbConstants.User.Id)
                .Column(e => e.FirstName, Constants.DbConstants.User.FirstName)
                .Column(e => e.LastName, Constants.DbConstants.User.LastName)
                .Column(e => e.Email, Constants.DbConstants.User.Email)
                .Column(e => e.EmailConfirmed, Constants.DbConstants.User.EmailConfirmed)
                .Column(e => e.PasswordHash, Constants.DbConstants.User.PasswordHash)
                .Column(e => e.SecurityStamp, Constants.DbConstants.User.SecurityStamp)
                .ClassHandler<UserClassHandler>();
        }
    }

    class UserClassHandler : IClassHandler<User>
    {
        public User Get(User entity, DbDataReader reader)
        {
            return entity;
        }

        public User Set(User entity)
        {
            return entity;
        }
    }

public class User
    {
        public int? Id { get; }

        public string FirstName { get; }

        public string LastName { get; }

        public string Email { get; }

        public bool EmailConfirmed { get; }

        public string PasswordHash { get; }

        public string SecurityStamp { get; }

        public User(
            int id,
            string firstName,
            string lastName,
            string email,
            bool emailConfirmed,
            string passwordHash,
            string securityStamp)
        {
            Id = id;
            FirstName = firstName;
            LastName = lastName;
            Email = email;
            EmailConfirmed = emailConfirmed;
            PasswordHash = passwordHash;
            SecurityStamp = securityStamp;
        }

        public User(
            string firstName,
            string lastName,
            string email,
            bool emailConfirmed,
            string passwordHash,
            string securityStamp)
        {
            Id = null;
            FirstName = firstName;
            LastName = lastName;
            Email = email;
            EmailConfirmed = emailConfirmed;
            PasswordHash = passwordHash;
            SecurityStamp = securityStamp;
        }
    }

Insert operation:

    class UserRepository : BaseRepository<User, NpgsqlConnection>, IUserRepository
    {
        public UserRepository(string connectionString) : base(connectionString)
        {
        }

        public Task<IEnumerable<User>> GetUsersAsync()
            => QueryAllAsync();

        public async Task InsertUserAsync(User user)
        {
           await InsertAsync<User>(user, fields: Field.Parse<User>(u => new {
                    u.FirstName,
                    u.LastName,
                    u.Email,
                    u.EmailConfirmed,
                    u.PasswordHash,
                    u.SecurityStamp
            }));
        }
    }
````

**Library Version:**

<PackageReference Include="Npgsql" Version="5.0.3" />
<PackageReference Include="RepoDb" Version="1.12.7" />
<PackageReference Include="RepoDb.PostgreSql" Version="1.1.3" />

```

[mikependon]

There is a recent report that is very identical to this. Exactly same scenario.

Did you use the GENERATED IDENTITY AS ALWAYS to define your identity column?

Also, it is good if you can share the schema so we can investigate it.

[VincenzoCarlino]

We are chatting on gitter, but it would be helpful to continue the thread also there...
Here is my migration for table user
I'm using FluentMigrator:

    [Migration(1)]
    public class AddUsersTable : Migration
    {
        public override void Up()
        {
            Create.Table(User.TableName)
                .WithColumn(User.Id).AsInt32().PrimaryKey().Identity()
                .WithColumn(User.FirstName).AsString(User.FirstNameLength).NotNullable()
                .WithColumn(User.LastName).AsString(User.LastNameLength).NotNullable()
                .WithColumn(User.Email).AsString(User.EmailLength).NotNullable()
                .WithColumn(User.EmailConfirmed).AsBoolean().NotNullable()
                .WithColumn(User.PasswordHash).AsString().NotNullable()
                .WithColumn(User.SecurityStamp).AsString().NotNullable();
        }

        public override void Down()
        {
            Delete.Table(User.TableName);
        }
    }

Here is the generated table

[VincenzoCarlino]

I report here the schema:

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.0
-- Dumped by pg_dump version 13.0

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP INDEX IF EXISTS public."UC_Version";
ALTER TABLE IF EXISTS ONLY public.users DROP CONSTRAINT IF EXISTS "PK_users";
ALTER TABLE IF EXISTS public.users ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE IF EXISTS public.users_id_seq;
DROP TABLE IF EXISTS public.users;
DROP TABLE IF EXISTS public."VersionInfo";
SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: VersionInfo; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public."VersionInfo" (
    "Version" bigint NOT NULL,
    "AppliedOn" timestamp without time zone,
    "Description" character varying(1024)
);


--
-- Name: users; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.users (
    id integer NOT NULL,
    first_name character varying(256) NOT NULL,
    last_name character varying(256) NOT NULL,
    email character varying(256) NOT NULL,
    email_confirmed boolean NOT NULL,
    password_hash text NOT NULL,
    security_stamp text NOT NULL
);


--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.users_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;


--
-- Name: users id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass);


--
-- Data for Name: VersionInfo; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public."VersionInfo" ("Version", "AppliedOn", "Description") FROM stdin;
1   2021-03-13 19:28:59 AddUsersTable
\.


--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.users (id, first_name, last_name, email, email_confirmed, password_hash, security_stamp) FROM stdin;
\.


--
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.users_id_seq', 1, true);


--
-- Name: users PK_users; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT "PK_users" PRIMARY KEY (id);


--
-- Name: UC_Version; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX "UC_Version" ON public."VersionInfo" USING btree ("Version");


--
-- PostgreSQL database dump complete
--

[mikependon]

Thanks. The alternative is to use the GENERATED IDENTITY AS ALWAYS up until we rectified this. Can you wait a week from now?

[VincenzoCarlino]

No problem I can wait.

[VincenzoCarlino]

I' ve created a PR for fix the problem in PostrgesSQL on insert with fields without PK value if has default value:
https://github.com/mikependon/RepoDb/pull/788

[mikependon]

I am validating this. Have you tried checking whether the Id column is really an Identity in the database?

[mikependon]

When I explicitly set it, then the query below returns the correct values.

SELECT C.column_name
    , CAST((CASE WHEN C.column_name = TMP.column_name THEN 1 ELSE 0 END) AS BOOLEAN) AS IsPrimary
    , CAST(C.is_identity AS BOOLEAN) AS IsIdentity
    , CAST(C.is_nullable AS BOOLEAN) AS IsNullable
    , C.data_type AS DataType
FROM information_schema.columns C
LEFT JOIN
(
    SELECT C.table_schema
        , C.table_name
        , C.column_name
    FROM information_schema.table_constraints TC 
    JOIN information_schema.constraint_column_usage AS CCU USING (constraint_schema, constraint_name) 
    JOIN information_schema.columns AS C ON C.table_schema = TC.constraint_schema
        AND TC.table_name = C.table_name
        AND CCU.column_name = C.column_name
    WHERE TC.constraint_type = 'PRIMARY KEY'
) TMP ON TMP.table_schema = C.table_schema
    AND TMP.table_name = C.table_name
    AND TMP.column_name = C.column_name
WHERE C.table_name = 'Person'
    AND C.table_schema = 'public';

Screenshot.

Note: I had copied over your schema and executed it on my local machine.

Can you please verify whether the FluentMigrator has really set the ID column as Identity in your side?

[mikependon]

By the way, the SQL script above is being used by RepoDB to extract the definitions of the table as can be seen here. Therefore, it is important to see whether the Id column is really an identity from your side so the mentioned exception message will be rectified. Otherwise, the library requires you to insert your manual value on the Primary Key column Id.

[mikependon]

The fixes has been added to support the SERIAL-based identity column. I am pre-assuming that you are using the SERIAL-based identity column (or FluentMigrator does that), which is covered by the recent fixes.

I will close this issue now and please do not hesitate to let me know if you need a beta release for this, otherwise, the fixes will be available on the next version > RepoDb.PostgreSql v1.1.4-beta1.