using Dapper;

using System;

using System.Data;

using System.Collections.Generic;

using System.Linq;

using Microsoft.Data.Sqlite;

public class Program

{

    public static void Main()

    {

        var peeps = new [] {

            new Person { Name = "Dennie", NickName = "mOrphie", Tags = new List<string>(new[] { "Vrolijk", "Bijtje", "Superpapa" })},

            new Person { Name = "Rob", NickName = "RobIII", Tags = new List<string>(new[] { "Zeurkous", "Nijlpaardje", "Superpapa" })},

            new Person { Name = "Elon", NickName = "Elonator", Tags = new List<string>(new[] { "StinkendRijk", "Tesla", "Paypal", "Doge" })},

        };

        using (var conn = new SqliteConnection("Data Source=:memory:")) {

            conn.Open();

            // Set up tables (usually in an external file like 'createdb.sql' or 'migration_043242423.sql')

            conn.Execute("CREATE TABLE persons (id INTEGER NOT NULL, name TEXT NOT NULL, nickname TEXT NOT NULL, PRIMARY KEY(id AUTOINCREMENT))");

            conn.Execute("CREATE TABLE tags (person_id INTEGER NOT NULL, tag TEXT NOT NULL, UNIQUE(person_id,tag), FOREIGN KEY(person_id) REFERENCES persons (id) ON DELETE CASCADE);");

            // Insert peeps

            using (var t = conn.BeginTransaction()) {

                foreach (var p in peeps) {

                    var personid = conn.ExecuteScalar<int>("INSERT INTO persons (name, nickname) values (@Name, @NickName); SELECT last_insert_rowid()", p, t);

                    conn.Execute("INSERT INTO tags (person_id, tag) values (@personid, @tag)", p.Tags.Select(tag => new { personid, tag }), t);

                }

                t.Commit();

            }

            // Dump table

            DumpPeeps(conn);

            // Change peep

            var rob = new Person {

                Id = 2,

                Name = "Rob",

                NickName = "RobIII",

                Tags = new List<string>() { "Lief", "Sexy", "Superpapa" }

            };

​

            // Update peep

            using (var t = conn.BeginTransaction()) {

                conn.Execute("DELETE FROM tags WHERE person_id = @Id", rob, t);

                conn.Execute("UPDATE persons SET name = @Name, nickname = @NickName WHERE id = @Id", rob, t);

                conn.Execute("INSERT INTO tags (person_id, tag) values (@Id, @tag)", rob.Tags.Select(tag => new { rob.Id, tag }), t);

                t.Commit();

            }

            DumpPeeps(conn);

            conn.Close();

        }          

    }

    private static IDictionary<int, Person> DumpPeeps(IDbConnection conn) {

        // Select peeps

        var newpeeps = new Dictionary<int, Person>();

        conn.Query<Person, string, Person>("SELECT p.id, p.name, p.nickname, t.tag FROM persons p LEFT OUTER JOIN tags t on t.person_id = p.id ORDER BY p.name, t.tag", (pd, t) =>

            {

               Person p;

               if(!newpeeps.TryGetValue(pd.Id, out p))

                   newpeeps.Add(pd.Id, p = pd);

​

               p.Tags.Add(t);

               return pd;

            },

            splitOn: "tag"

        );

​

        // Dump peeps           

        Console.WriteLine($"ID    Name       Nickname        Tags");

        Console.WriteLine(new String('=',80));

        foreach (var p in newpeeps.Values)

            Console.WriteLine($"{p.Id,-5} {p.Name,-10} {p.NickName,-15} {string.Join(", ", p.Tags),-40}");

        Console.WriteLine(new String('-',80));

        Console.WriteLine();

        return newpeeps;

    }

}

​

public record Person {

    public int Id { get; init; }

    public string Name { get; init; }

    public string NickName { get; init; }

    public List<string> Tags { get; init; } = new List<string>();

}