Reconstituting nested objects with many to many join manually, is there a better way?

Question

I have two objects: SettlementRole and SettlementType (see below for sqlite schema). There is a many to many relationship between the two, where each SettlmentRole has a collection of SettlementTypes, each with a weight specific to that pair, but each SettlementType may be in a number of SettlementRoles, each with different weights.

I'm not using an ORM for various reasons, and ORMs are not in scope here.

Question: Is there a cleaner, simpler way of doing the same process than the following? This is mostly a learning project and there are a bunch of other similar (but not identical) entity pairs/groups in the project, so I'd like to get one as clean as possible so I can follow the same process for the more complex ones.

The function to get all SettlementRoles looks like

public async Task<IList<SettlementRole>> GetAllRolesAsync()
        {
            using var connection = new SqliteConnection(dbName);
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = @"
                SELECT sr.id, sr.name, sr.description, t.id, t.name, t.description, t.minSize, t.maxSize, map.weight 
                FROM settlementRole sr
                JOIN settlementTypeRoleWeight map
                    ON map.roleId = sr.id
                JOIN settlementType t
                    ON t.id = map.typeId
                ;";
            var rawRoles = await command.ExecuteReaderAsync();
            var roles = new List<SettlementRole>();
            var map = new Dictionary<int, SettlementRole>();
            while (rawRoles.Read())
            {
                var id = rawRoles.GetInt32(0);
                var exists = map.TryGetValue(id, out var role);
                if (!exists)
                {
                    role = new SettlementRole() { Id = id, Name = rawRoles.GetString(1), Description = rawRoles.GetString(2) };
                    map.Add(id, role);
                }
                role!.AddType(new SettlementType()
                {
                    Name = rawRoles.GetString(3),
                    Description = rawRoles.GetString(4),
                    MinSize = rawRoles.GetInt32(5),
                    MaxSize = rawRoles.GetInt32(6),
                }, rawRoles.GetDouble(7));
            }
            return new List<SettlementRole>(map.Values);

Where settlementRole.AddType(SettlementType type, double weight) handles the internal weighting process of adding a type to the (default empty) collection.

and the schema for the three (including the join table) looks like

CREATE TABLE settlementRole(
id INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT NOT NULL DEFAULT ""
) ;
CREATE TABLE settlementType(
id INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT NOT NULL,
minSize INTEGER NOT NULL DEFAULT 1,
maxSize INTEGER,
professions TEXT NOT NULL DEFAULT ""
);
CREATE TABLE settlementTypeRoleWeight(
roleId INTEGER NOT NULL,
typeId INTEGER NOT NULL,
weight INTEGER NOT NULL DEFAULT 1,
PRIMARY KEY (roleId, typeId)
);

And the entities themselves (right now) are simple data objects (they'll have behavior, but that's out of scope here).