Navigation Properties

FreeSql offers six types of navigation properties: OneToMany, ManyToOne, ManyToMany, OneToOne, Parent, and PgArrayToMany.

What can navigation properties do?

• Multi-table Queries Where(a => a.Parent.Parent.Name == "xx") Where(a => a.Childs.Any(b => b.title == "xxx"))
• Eager Loading Include/IncludeMany
• Lazy Loading
• Tree Table Queries
• Cascade Saving
• Cascade Deletion
• Aggregate Root Repository

Navigation properties make multi-table queries very convenient, allowing direct use of navigation objects with lambda expressions—so comfortable!

Custom Configuration

Types supported by OneToMany/ManyToMany: ICollection<T>, List<T>, ObservableCollection<T>

//OneToMany
class Group
{
    [Navigate(nameof(User.GroupId))]
    public List<User> Users { get; set; }
    //Links GroupId in User to the primary key of this entity
}

//ManyToOne
class User
{
    public int GroupId { get; set; }
    [Navigate(nameof(GroupId))]
    public Group Group { get; set; }
    //Links GroupId in this entity to the primary key of Group
}

//ManyToMany
[Navigate(ManyToMany = typeof(TagSong))]
public List<Tag> Items { get; set; }

---

You can also use Fluent API to set navigation relationships externally:

fsql.CodeFirst.ConfigEntity<T>(a => a
    .Navigate(b => b.roles, null, typeof(TMid))
    .Navigate(b => b.users, "uid")
);

Note:

1. Navigation properties will be invalid if the property is set with Column(IsIgnore = true).
2. The strings set in Navigate are class property names, not table column names!

Pre-warming Note: Due to the need to resolve circular references, navigation property configuration may fail on first use when relationships are complex. It should succeed on the second attempt. A solution is to pre-warm all entity classes at application startup by executing fsql.Select<object>().AsType(EntityClass); in a loop.

Non-Primary Key Associations

//OneToMany
[Navigate(nameof(User.GroupId), TempPrimary = nameof(Code))]
public List<User> Users { get; set; }

//ManyToOne
[Navigate(nameof(GroupId), TempPrimary = nameof(Group.Code))]
public Group Group { get; set; }

Non-primary key associations are supported for OneToMany/ManyToOne relationships and are only effective during queries (not supported for cascade saving or cascade deletion).

Checking Navigation Properties

To check if a navigation property is valid:

var tbref = fsql.CodeFirst
    .GetTableByEntity(typeof(T))
    .GetTableRef("Children", true);

GetTableRef(string propertyName, bool isThrow);

OneToOne

class User
{
    [Key]
    public int Id { get; set; }
    [Navigate(nameof(Id))]
    public UserExt Ext { get; set; }
    //...
}
class UserExt
{
    [Key]
    public int UserId { get; set; }
    [Navigate(nameof(UserId))]
    public User User { get; set; }
    //...
}

In a one-to-one relationship, both sides must use the Navigate attribute with the entity's primary key (supports cascade saving and cascade deletion).

PgArrayToMany

class User
{
    public int[] RoleIds { get; set; }
    [Navigate(nameof(RoleIds))]
    public List<Role> Roles { get; set; }
}
class Role
{
    public int Id { get; set; }
    [Navigate(nameof(User.RoleIds))]
    public List<User> Users { get; set; }
}

More information: #1145

Convention Naming (No Need to Specify Navigate)

Tip: This section is for general understanding; not mandatory knowledge. You can skip it if needed.

class Group
{
    public int Id { get; set; } // Id, GroupId, Group_id

    public List<User> AUsers { get; set; }
    public List<User> BUsers { get; set; }

    public int ParentId { get; set; } // ParentId, Parent_id
    public Group Parent { get; set; }

    public List<Group> Childs { get; set; }
}
class User
{
    public int Id { get; set; } // Id, UserId, User_id
    public UserExt Ext { get; set; }

    public int AGroupId { get; set; }
    public Group AGroup { get; set; }

    public int BGroupId { get; set; }
    public Group BGroup { get; set; }

    public List<Role> Roles { get; set; }
}
class UserExt
{
    public int UserId { get; set; }
    public User User { get; set; }
}
class Role
{
    public int Id { get; set; }
    public string Name { get; set; }

    public List<User> Users { get; set; }
}
class UserRole
{
    public int UserId { get; set; }
    public User User { get; set; }

    public int RoleId { get; set; }
    public Role Role { get; set; }
}