I am trying to insert a record with many to many relationship in EfCore to postgres table

When adding a simple record to Users...it works but when I introduced 1:N with User_Governments

It started giving me duplicate key value violates unique constraint "pk_user_governments"

I have tried a few things:

SELECT MAX(user_governments_id) FROM user_governments;
SELECT nextval('users_gov_user_id_seq');

This keeps incrementing everytime I run it in postgres..but the issue does not go

I am inserting it as follows:

User user = new();
            user.Organisation = organisation;
            user.Name = userName;
            user.Email = email;
            user.IsSafetyDashboardUser = isSafetyFlag;

            if (isSafetyFlag)
            {
                List<UserGovernment> userGovernments = new List<UserGovernment>();

                foreach (var govId in lgas)
                {
                    userGovernments.Add(new UserGovernment()
                    {
                        LocalGovId = govId,
                        StateId = 7                            
                    });
                    
                }

                user.UserGovernments = userGovernments;

            }


            _context.Users.Add(user);

            int rows_affected = _context.SaveChanges();
            

Table and column in db is as follows:

CREATE TABLE IF NOT EXISTS user_governments
(
    user_government_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    user_id integer NOT NULL,
    state_id integer NOT NULL,
    local_gov_id integer NOT NULL,
    CONSTRAINT pk_user_governments PRIMARY KEY (user_government_id),
    CONSTRAINT fk_user_governments_local_govs_local_gov_id FOREIGN KEY (local_gov_id)
        REFERENCES local_govs (local_gov_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fk_user_governments_states_state_id FOREIGN KEY (state_id)
        REFERENCES states (state_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fk_user_governments_users_user_id FOREIGN KEY (user_id)
        REFERENCES users (user_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

I have also tried running following command as per this post

SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('user_governments', 'user_government_id')), (SELECT (MAX("user_government_id")   1) FROM "user_governments"), FALSE);

but I get error:

ERROR: relation "user_governments" does not exist

CodePudding user response：

IDENTITY is an table integrated automatic increment. No needs to use PG_GET_SERIAL_SEQUENCE wich is dedicated for SEQUENCES that is another way to have increment outside the table. So you cannot use a query like :

SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('user_governments', 'user_government_id')), 
(SELECT (MAX("user_government_id")   1) FROM "user_governments"), FALSE)

If your purpose is to assigne the seed for an IDENTITY, the ways to do that is :

You must use a syntax like this one :

ALTER TABLE user_governments
   ALTER COLUMN user_government_id RESTART WITH (select Max(user_government_id)   1 from user_governments);

CodePudding user response：

It turned out that I did not build the model correctly.

The user_government table had an incremental key, but I had defined the model as follows

modelBuilder.Entity<UserGovernment>()
   .HasKey(bc => new { bc.UserId, bc.LocalGovId });

I replaced it with:

        modelBuilder.Entity<UserGovernment>()
   .HasKey(bc => new { bc.UserGovernmentId});

The Journey :)

Initially I found out that once I commented the following line

_context.UserGovernments.AddRange(userGovernments);

It just inserted data with user_government_id as 0 Then I tried manually giving a value to user_government_id and it also went successfully, this lead me to check my modelbuilder code!!