LiteOrm在线文档

Associations

This page explains how LiteOrm models relationships with ForeignType, TableJoin, ForeignColumn, and AutoExpand.

1. Core Concepts

2. Usage Examples

2.1 Minimal Working Example

The following example is suitable for first-time LiteOrm association query users to get started:

[Table("Users")]
public class User
{
    [Column("Id", IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }

    [Column("UserName")]
    public string? UserName { get; set; }
}

[Table("Orders")]
public class Order
{
    [Column("Id", IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }

    [Column("UserId")]
    [ForeignType(typeof(User))]
    public int UserId { get; set; }
}

public class OrderView : Order
{
    [ForeignColumn(typeof(User), Property = nameof(User.UserName))]
    public string? UserName { get; set; }
}

var orders = await orderService.SearchAsync<OrderView>();

If OrderView.UserName retrieves the correct value, the most basic ForeignType + ForeignColumn association chain is working.

2.2 ForeignType (Property-Level)

Use ForeignType for a normal single-column foreign key.

[Table("Orders")]
public class Order
{
    [Column("Id", IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }

    [Column("UserId")]
    [ForeignType(typeof(User), Alias = "U", JoinType = TableJoinType.Left, AutoExpand = false)]
    public int UserId { get; set; }

    [Column("Amount")]
    public decimal Amount { get; set; }
}

Explanation: ForeignType is used to annotate the foreign key column corresponding to the external entity. When querying a view, ForeignColumn in the view class can automatically generate JOINs and read external table columns.

2.2.1 Multiple ForeignType Declarations on One Column

The same column can now declare multiple ForeignType entries. This is useful when one key needs to expose multiple readable relationship paths.

[Table("Documents")]
public class Document
{
    [Column("OwnerId")]
    [ForeignType(typeof(User), Alias = "Owner")]
    [ForeignType(typeof(Department), Alias = "OwnerDept")]
    public int OwnerId { get; set; }
}

public class DocumentView : Document
{
    [ForeignColumn("Owner", Property = nameof(User.UserName))]
    public string? OwnerName { get; set; }

    [ForeignColumn("OwnerDept", Property = nameof(Department.Name))]
    public string? OwnerDeptName { get; set; }
}

Notes:

2.3 TableJoin (Type-Level)

TableJoin is suitable for expressing complex association relationships.

If the target table uses a composite primary key, you can use ForeignKeys = "Key1,Key2" to provide multiple foreign key columns in order; ForeignType does not support this multi-column association scenario.

If you have a compatibility-driven mapping that must join by non-primary target fields, you can explicitly override the target join keys with PrimeKeys = "Code" or PrimeKeys = "Key1,Key2". This overrides the default “join by target primary key” behavior, but it is not the recommended style for normal models.

[TableJoin(typeof(OrderItem), "OrderId,LineNo", AliasName = "Item")]
public class Shipment
{
    [Column("OrderId")]
    public long OrderId { get; set; }

    [Column("LineNo")]
    public int LineNo { get; set; }
}

In this model, Shipment.OrderId + Shipment.LineNo will associate with the composite primary key of OrderItem in order.

2.4 Multi-Level Relationships and AutoExpand

// SalesRecord example: SalesUserId relates to User, and automatically expands User's relationships (such as Department)
[Column("SalesUserId")]
[ForeignType(typeof(User), AutoExpand = true)]
public int SalesUserId { get; set; }

public class SalesRecordView : SalesRecord
{
    [ForeignColumn(typeof(User))]
    public string? UserName { get; set; }

    [ForeignColumn(typeof(Department), Property = nameof(Department.Name))]
    public string? DepartmentName { get; set; }
}

// When querying SalesRecordView, LiteOrm can continue to resolve DepartmentName along the relationship path already defined in User.

Note: The core purpose of AutoExpand is to “make the next level of relationship path resolvable”. Whether more JOINs are actually generated still depends on whether the query really references fields or conditions on those paths.

2.4.1 AutoExpand Switch Comparison

Scenario AutoExpand = false AutoExpand = true
Only need first-level foreign table fields Recommended Works but usually unnecessary
Need to read across second-level relationships Need to manually declare more joins Recommended
Large tables, complex views, performance-sensitive Safer Evaluate carefully
Want to reduce view declaration complexity Normal More convenient

2.5 Cascade Example

LiteOrm.Demo\Models\User.cs provides a practical secondary relationship expansion model:

[Table("Sales_{0}")]
public class SalesRecord : ObjectBase, IArged
{
    [Column("SalesUserId")]
    [ForeignType(typeof(User), AutoExpand = true)]
    public int SalesUserId { get; set; }
}

public class SalesRecordView : SalesRecord
{
    [ForeignColumn(typeof(User))]
    public string? UserName { get; set; }

    [ForeignColumn(typeof(Department), Property = nameof(Department.Name))]
    public string? DepartmentName { get; set; }
}

Key points:

Without AutoExpand enabled, fields like DepartmentName at the secondary level typically require additional join path declarations. This is also the most common and worthwhile use case for AutoExpand: filling in resolvable paths for multi-level relationships.

2.6 Multi-Level Relationship Example

Demonstrates “Department + Parent Department” two-level relationship:

[Table("Users")]
[TableJoin("Dept", typeof(Department), nameof(Department.ParentId), AliasName = "Parent")]
public class User
{
    [Column("DeptId")]
    [ForeignType(typeof(Department), Alias = "Dept")]
    public int? DeptId { get; set; }
}

public class UserView : User
{
    [ForeignColumn("Dept", Property = "Name")]
    public string? DeptName { get; set; }

    [ForeignColumn("Parent", Property = "Name")]
    public string? ParentDeptName { get; set; }
}

This pattern is suitable for stable multi-level read scenarios like “User → Department → Parent Department”.

2.7 Query Example

Verifying that multi-level relationship fields can be used for filtering:

var usersByDept = await viewService.SearchAsync(u => u.DeptName == "Sub Dept");
var usersByParentDept = await viewService.SearchAsync(u => u.ParentDeptName == "Root Dept");

var combinedUsers = await viewService.SearchAsync(
    u => u.DeptName == "Sub Dept" && u.ParentDeptName == "Root Dept"
);

2.7.1 Association Field Sorting and Pagination

Association fields can directly participate in sorting and pagination:

using static LiteOrm.Common.Expr;
var expr1 = From<TestUserView>()
    .Where<TestUserView>(u => u.DeptName != null)
    .OrderBy((nameof(TestUserView.DeptName), true))
    .OrderBy((nameof(TestUser.Age), false))
    .Section(0, 3);

var users1 = await viewService.SearchAsync(expr1);

And for deeper parent department fields:

using static LiteOrm.Common.Expr;
var expr2 = From<TestUserView>()
    .Where<TestUserView>(u => u.ParentDeptName == "Parent Dept")
    .OrderBy(nameof(TestUserView.ParentDeptName))
    .OrderBy(nameof(TestUserView.DeptName))
    .OrderBy(nameof(TestUser.Age))
    .Section(0, 5);

var users2 = await viewService.SearchAsync(expr2);

This shows that ForeignColumn can not only display data, but also directly participate in:

3. ExistsRelated

When you don’t want to explicitly expose association fields in the view model but just want to “filter the main table by association table conditions”, you can use ExistsRelated.

3.1 Matching Rules

ExistsRelated follows these priority rules when constructing relationship paths:

Association matching order:

  1. Forward association first: First try foreign key associations from the main table (e.g., Order.UserId -> User.Id)
  2. Reverse association fallback: If the main table has no forward association to the target type, try reverse inference from the target table (e.g., User.DeptId -> Department.Id)

This “matching” is not based on hard-coded property-name guessing. It uses relationship metadata already declared in the model:

In other words, ExistsRelated<TestDepartment>(...) depends on declared metadata such as ForeignType and TableJoin. It does not try to infer a relationship just because a field name “looks like” a foreign key at runtime.

That also means:

In other words, ForeignExpr / ExistsRelated<T> now follows inheritance only in the “declared type -> subclass” direction. Parent-type fallback is no longer part of the matching rule.

Multi-path merge logic:

You can think of it as:

(path1.key1 = key1 AND path1.key2 = key2 ...)
OR
(path2.key1 = key1 AND path2.key2 = key2 ...)

using static LiteOrm.Common.Expr;
// Query departments that "have users named ERRev_User1"
var expr = ExistsRelated<TestUser>(Prop("Name") == "ERRev_User1");
var results = await objectViewDAO.Search(expr).ToListAsync();

Even if TestDepartment itself does not have a directly declared ForeignType to TestUser, the framework can still complete reverse inference through the known association TestUser.DeptId -> TestDepartment.Id.

Usage guidance:

3.2 Combination Filtering

using static LiteOrm.Common.Expr;
// 1. Forward: filter users by associated department
var expr = ExistsRelated<TestDepartment>(Prop("Name") == "ER_IT");
var users = await objectViewDAO.Search(expr).ToListAsync();

// 2. Negation: exclude users belonging to the target department
var notInIT = await objectViewDAO.Search(
    !ExistsRelated<TestDepartment>(Prop("Name") == "ERNot_IT")
).ToListAsync();

// 3. Combine with regular field conditions
var matureItUsers = await objectViewDAO.Search(
    ExistsRelated<TestDepartment>(Prop("Name") == "ERCombo_IT")
    & (Prop("Age") >= 30)
).ToListAsync();

Usage recommendations:

4. API Key Points

In implementation, LiteOrm merges ForeignType and TableJoin information during the metadata phase to generate JoinedTable / ForeignTable structures. For fixed-filter metadata and SQL injection details, see Permission Filtering and User Scope Control.

5. Best Practices

6. FAQ

This site is open source. Improve this page.