Modeling

Introduction

An object-relational mapper (ORM) will attempt to map query results to objects automatically. A database query result is typically considered to be rows and columns, but in C# that is represented by a list of dictionaries. To avoid unnecessary complexity when using query results, the object-relational mapper will make it's best attempt to map the list of dictionaries to a list of concrete objects.

The ORM used by uMod is considered a "micro-orm" and has a limited set of capabilities compared to some fully-featured ORM products. The micro-orm simply assists in the mapping of results with complex relationships and will not create queries or perform any querying that is hidden from view.

Eager-loading

Eager-loading is a key optimization to reduce the number of queries needed when reading large amount of data from a database. An eager-loaded relationship will populate multiple nested models with data from results combined from multiple tables.

Primary key

It is recommended to specify a primary key field on every table with few exceptions (M2M, etc). A primary key will provide an index to map eager-loaded relationships and increase the speed of queries.

Implement a simple user model with the primary key specified using the PrimaryKey field/property attribute

[Model]
public class User
{
    [PrimaryKey]
    public int Id;
    
    public string Name;
}

Create a table using the following query (Sqlite)

CREATE TABLE "users" ("Name" TEXT, "Id" INTEGER PRIMARY KEY AUTOINCREMENT);

Insert a row into the users table

INSERT INTO "users" ("Name") VALUES ("Calytic");

Query the users table and map the results automatically

connection.Query<List<User>>("SELECT * FROM users;")
    .Done((results) => {
        if (results == null) return;

        foreach(User user in results)
        {
            Logger.Info($"Found user: {user.Name} ({user.Id})");
        }
    });

One to one relationship

A one-to-one relationship is a type of cardinality which refers to the relationship between two entities, such as tables, where one entity contains a relationship to one other entity (in the same table or a different table).

Using our previous example, implement a one-to-one relationship by creating a new model and referring to it in the User model with a OneToOne attribute.

[Model]
public class Address
{
    [PrimaryKey]
    public int Id;
    
    public int UserId;
    public string Address;
    public string City;
    public string State;
    public string ZipCode;
    public string Country;
}

[Model]
public class User
{
    [PrimaryKey]
    public int Id;
    
    public string Name;
    
    [OneToOne]
    public Address Address;
}

Create the tables using the following query (Sqlite)

CREATE TABLE "users" ("Name" TEXT, "Id" INTEGER PRIMARY KEY AUTOINCREMENT);
CREATE TABLE "addresses" ("Address" TEXT, "City" TEXT, "State" TEXT, "ZipCode" TEXT, "Country" TEXT, "UserId" INTEGER, "Id" INTEGER PRIMARY KEY AUTOINCREMENT);

Insert some rows into both tables

INSERT INTO "users" ("Name") VALUES ("Calytic");
INSERT INTO "addresses" ("Address", "City", "State", "ZipCode", "Country", "UserId") VALUES ("123 Abc Lane", "Disneyworld", "Florida", "111111", "United States", 1);

Query the user and the address at the same time

connection.Query<List<User>>("SELECT u.Id, u.Name, a.Id, a.Country FROM users u LEFT JOIN addresses a on u.Id = a.UserId;", new[] { "Address" })
    .Done((results) => {
        if (results == null) return;

        foreach(User user in results)
        {
            Logger.Info($"User {user.Name} ({user.Id}) lives in {user.Address.Country}");
        }
    });

One to many relationship

A one-to-many relationship is a type of cardinality that refers to a relationship between two entities, such as tables, where one entity is related to many other entities (in the same table or a different table).

Again expanding on previous examples, implement a one-to-many relationship by creating a new model and referring to it in the User model with a OneToMany attribute.

[Model]
public class Note
{
    [PrimaryKey]
    public int Id;
    
    public int UserId;
    
    public string Message;
}

[Model]
public class User
{
    [PrimaryKey]
    public int Id;
    
    public string Name;
    
    [OneToMany]
    public List<Note> Notes;
}

Create the tables using the following query (Sqlite)

CREATE TABLE "users" ("Name" TEXT, "Id" INTEGER PRIMARY KEY AUTOINCREMENT);
CREATE TABLE "notes" ("Message" TEXT, "UserId" INTEGER, "Id" INTEGER PRIMARY KEY AUTOINCREMENT);

Insert some rows into both tables

INSERT INTO "users" ("Name") VALUES ("Calytic");
INSERT INTO "notes" ("Message", "UserId") VALUES ("One bad hombre", 1);

Query the user and their notes at the same time

connection.Query<List<User>>("SELECT u.Id, u.Name, n.Id, n.Message FROM users u LEFT JOIN notes n on u.Id = n.UserId;", new[] { "Notes" })
    .Done((results) => {
        if (results == null) return;

        foreach(User user in results)
        {
            StringBuilder stringBuilder = new StringBuilder();
            
            stringBuilder.AppendLine($"User {user.Name} ({user.Id}) notes:");
            foreach(Notes note in user.Notes)
            {
                stringBuilder.AppendLine(note.Message);
            }
        }
    });

Dictionary or list

A OneToMany relationship field/property may be implemented as a List (as above) or a Dictionary. When using a dictionary, the ORM will key the dictionary by the primary key of the related table. If the primary key is not id or Id then the key field must be defined and annotated with the [Key] attribute.

Multi-mapping

Multi-mapping is an advanced technique to achieve eager-loading by splitting query results and mapping them onto nested objects.

Eager-loading cardinal relationships requires using JOIN clauses where each row from the combined results are split according to one or multiple keys specified by the SplitOn option. The split results are mapped to nested dynamic objects and then converted to custom [Model] objects using JSON deserialization. The nesting is achieved by reading the annotated OneToOne and OneToMany fields.

To use multi-mapping effectively, it is important to remember..

1. Selected fields should be explicitly selected, starting with the key, and should not include wildcards
2. The order of the selected fields matters
3. The mapping parameter must include the names of the relationships to be mapped in the same order as the selected fields
4. The split-on key option must include a (comma-delimited) list of the first field (key) of each type to be mapped (excluding the parent type)

Split-on key

When mapping multi-typed query results from a JOIN clause (such as those above), the ORM will split the rows according to the keys specified by the splitOn query option. By default, the ORM will assume the relevent key is named Id or id.

Custom binding

Implement contract interfaces that are annotated with the Model attribute to create custom injection behavior.

For example, wrap the IPlayer interface with a custom abstraction that contains domain-specific or context dependent information about the player.

[Model]
interface IPlayerFriends
{
    IPlayer Player { get; }
    List<IPlayer> Friends { get; }
}

This abstraction represents a player who has friends.

Provide a concrete implementation of the above interface.

class PlayerFriends : IPlayerFriends
{
    public IPlayer Player { get; }
    
    [OneToMany]
    public List<IPlayer> Friends { get; }
}

In any concrete implementation of the PlayerFriends model, also implement one or multiple static Resolve methods.

class PlayerFriends : IPlayerFriends
{
    public IPlayer Player { get; }
    
    [OneToMany]
    public List<IPlayer> Friends { get; }
    
    public PlayerFriends (IPlayer player)
    {
        Player = player;
        Friends = new List<IPlayer>();
    }
    
    public static Dictionary<string, IPlayerFriends> playerFriends = new Dictionary<string, IPlayerFriends>();
    
    // Resolves IPlayerFriends from string
    public static IPlayerFriends Resolve(string playerId)
    {
          if (playerFriends.TryGetValue(playerId, out IPlayerFriends playerFriends))
          {
              return playerFriends;
          }
          
          return default;
    }
    
    // Resolves IPlayerFriends from an IPlayer object
    public static IPlayerFriends Resolve(IPlayer player)
    {
          if (playerFriends.TryGetValue(player.Id, out IPlayerFriends playerFriends))
          {
              return playerFriends;
          }
          
          return default;
    }
}

Now, any hook which accepts an IPlayerFriends as a parameter may be supplied an IPlayer or a string instead and the related IPlayerFriends will be resolved automatically..