¿Cómo insertar por lotes (una matriz) en dos tablas relacionadas en PostgreSQL usando Dapper?

c# dapper npgsql postgresql

Pregunta

Intento insertar un lote de registros en dos tablas relacionadas

p.ej. las tablas son

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)
);

Para insertar un lote de parent y sus children , hago lo siguiente: las clases a continuación están mapeadas en las tablas anteriores

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;}
}

Por conveniencia estoy usando 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);
}

Obviamente, esto no funcionará porque parent_id no está disponible para la segunda inserción de lote. ¿Hay alguna forma de obtener el parent_id insertado desde la primera inserción y usarlo con la segunda, dado que la segunda usa una matriz parametrizada? ¿O hay otra forma de lograr el mismo resultado, es decir, los datos relacionados con la inserción de lotes?

Nota 1: Esto se puede hacer fácilmente con un CTE modificador de datos si los valores insertados no son una matriz con parámetros https://dba.stackexchange.com/questions/89451/inserting-into-related-tables

Nota 2: Otro enfoque que puedo pensar es llenar previamente las claves primarias en el código (antes de insertar) al asignar previamente los valores de secuencia con algo como select nextval('parent_id_seq') from generate_series(1, <number of parents inserting which we know beforehand>) pero este no parece el enfoque correcto para mí.

Respuesta popular

Su segunda nota - valores de secuencia de "preasignación" - es una forma bastante legítima de hacerlo, y a veces se lo conoce como hi-lo. El proveedor de Entity Framework Core admite este comportamiento como una característica estándar.



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué