How to batch-insert (an array) into two related tables in PostgreSQL using Dapper?

Question

I'm trying to insert a batch of records to two related tables

eg. the tables are

CREATE TABLE public.parent
(
  parent_id integer NOT NULL DEFAULT nextval('parent_id_seq'::regclass),
  name integer,
  CONSTRAINT parent_pkey PRIMARY KEY (parent_id)
);

CREATE TABLE public.child
(
  child_id integer NOT NULL DEFAULT nextval('child_id_seq'::regclass),
  parent_id integer,
  name integer,
  CONSTRAINT child_pkey PRIMARY KEY (child_id),
  CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES public.parent (parent_id)
);

To insert a batch of parents and their children, I do the following - the classes below are mapped to the above tables

public class parent 
{
    public int parent_id {get; set;}
    public string name {get; set;}
    public List<child> children {get; set;}
}

public class child 
{
    public int child_id {get; set;}
    public string name {get; set;}
    public int parent_id {get; set;}
}

For convenience I'm using Dapper

var parents = new parent[] { ... }
var children = parents.SelectMany(p => p.children);

using (var connection = new NpgsqlConnection("..."))
{
    // map class to tables
    connection.MapComposite<parent>("public.parent");
    connection.MapComposite<child>("public.child");

    await connection.ExecuteAsync(@"
        insert into parent (name) 
        select 
            p.name
        from
            unnest(@parents) p;

        insert into child (parent_id, name) 
        select 
            c.parent_id,
            c.name
        from
            unnest(@children) c;
        ",
        new[] { 
            new NpgsqlParameter("parents", parents), 
            new NpgsqlParameter("children", children) 
        }, CommandType.Text);
}

Obviously, this won't work because the parent_id is not available for the second batch insert. Is there a way to get the inserted parent_id from the first insert and use it with the second one, given the second one uses a parameterized array? Or is there another way to achieve the same result i.e. batch insert related data?

Note 1: This can be done easily with a data-modifying CTE if the inserted values are not a parameterized array https://dba.stackexchange.com/questions/89451/inserting-into-related-tables

Note 2: Another approach I can think of is pre-populating the primary keys in the code (before insert) by pre-allocating the sequence values with something like select nextval('parent_id_seq') from generate_series(1, <number of parents inserting which we know beforehand>) but this doesn't look like the right approach to me.

Shay Rojansky · Accepted Answer · 2017-09-07 14:31:18Z

1

Your second note - "preallocating" sequence values - is quite a legitimate way to do this, and is sometimes known as hi-lo. The Entity Framework Core provider supports this behavior as a standard feature.

answered Sep 7, 2017 at 14:31

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to batch-insert (an array) into two related tables in PostgreSQL using Dapper?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related