Relations

Overview

RoomSharp provides a simple way to handle relationships between entities. Unlike full-blown ORMs that use lazy loading (which can lead to the N+1 problem), RoomSharp uses explicit loading via the RelationLoader. This ensures you are always aware of when database queries are being executed.

Defining Relations

Relations are defined using the [Relation] attribute on properties within your entity classes. These properties are usually lists or single instances of other entities.

Note: Relation properties should be marked with [Ignore] if you don't want them to be mapped as columns in the database table itself. However, the [Relation] attribute implies that the property is populated from a relation query, not a direct column mapping.

One-to-Many Relationships

A one-to-many relationship exists when one entity is related to multiple instances of another entity. For example, a User has many Posts.

public class User
{
    [PrimaryKey(AutoGenerate = true)]
    public long Id { get; set; }
    public string Name { get; set; }

    [Relation(Entity = typeof(Post), ParentColumn = "Id", EntityColumn = "UserId")]
    public List<Post> Posts { get; set; }
}

public class Post
{
    [PrimaryKey(AutoGenerate = true)]
    public long Id { get; set; }
    public long UserId { get; set; }
    public string Title { get; set; }
}

Parameters:

• Entity: The type of the related entity (e.g., typeof(Post)).
• ParentColumn: The column in the parent entity (User) that matches the child's foreign key (e.g., "Id").
• EntityColumn: The column in the child entity (Post) that refers to the parent (e.g., "UserId").

Many-to-Many Relationships

Many-to-many relationships require a junction table (associative table). You can define this using the AssociateBy property in the [Relation] attribute.

public class Student
{
    [PrimaryKey]
    public long Id { get; set; }
    public string Name { get; set; }

    [Relation(Entity = typeof(Course), ParentColumn = "Id", EntityColumn = "Id", AssociateBy = "StudentCourse")]
    public List<Course> Courses { get; set; }
}

public class Course
{
    [PrimaryKey]
    public long Id { get; set; }
    public string Title { get; set; }
}

// Junction Table
[Entity(TableName = "StudentCourse")]
public class StudentCourse
{
    public long StudentId { get; set; }
    public long CourseId { get; set; }
}

Parameters:

• AssociateBy: The name of the junction table.
• ParentColumn: The column in the junction table that matches the parent entity's key.
• EntityColumn: The column in the junction table that matches the related entity's key.

Loading Relations

Relations are not loaded automatically when you query an entity. You must explicitly load them using the WithRelationsAsync extension method.

using RoomSharp.Relations;

// Load a single entity with relations
var user = await db.UserDao.FindByIdAsync(1);
if (user != null)
{
    await user.WithRelationsAsync(db.Connection);
    // user.Posts is now populated
}

// Load a list of entities with relations
var users = await db.UserDao.GetAllAsync();
await users.WithRelationsAsync(db.Connection);

Performance Considerations

Loading relations involves executing additional SQL queries.

• One-to-Many: Executes one additional query per parent entity to fetch children (N+1 query pattern if not careful, though WithRelationsAsync on a list optimizes this by running queries in parallel or batching if implemented). Note: Current implementation runs parallel tasks for list loading.
• Many-to-Many: Executes a JOIN query against the junction table.

Use WithRelationsAsync only when you actually need the related data.