Skip to content

Entity Relationship

Jump to bottom
2881099 edited this page Nov 22, 2023 · 19 revisions

中文 | English

FreeSql provides OneToMany, ManyToOne, ManyToMany, OneToOne, Parent, PgArrayToMany Six navigation attribute relationships.

What can navigation attributes do?

Custom Navigation Relationship

OneToMany/ManyToMany supported types: ICollection<T>、List<T>、ObservableCollection<T>

//OneToMany
class Group
{
    [Navigate(nameof(User.GroupId))]
    public List<User> Users { get; set; }
    //Find the GroupId property in User and associate it with this ENTITY.PrimaryKey
}

//ManyToOne
class User
{
    public int GroupId { get; set; }
    [Navigate(nameof(GroupId))]
    public Group Group { get; set; }
    //Find the GroupId property in THIS ENTITY and associate it with the Group.PrimaryKey
}

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

You can also use FluentApi to set the navigation relationship externally:

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

Note:

  1. Set Column(IsIgnore = true) on Property, then the navigation property will be invalid

  2. The string set by Navigate is the property name of the type, NOT THE TABLE IR FIELD NAME.

Warm-up description: The navigation attribute configuration is loaded because it is necessary to solve the dead cycle reference. When the mutual reference relationship is very complex, it may cause the first use of navigation attributes to fail. The second time is enough. The solution is to warm up all entity classes when the program starts, and execute fsql.Select<object>().AsType(entityClass) in a loop;

Associate with non-primary key

//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 association rights support OneToMany/ManyToOne relationships and can only be valid when querying. (Cascade saving and deletion are not supported)

Detect Navigation Properties

How to detect whether a navigation property is configured to take effect:

fsql.CodeFirst.GetTableByEntity(typeof(T))
    .TestNavigate("roles")
    .TestNavigate("users");

Method signature:

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; }
    //...
}

OneToOne, Require both sides to use the Navigate feature to associate with their own 'primary key'. (Supports cascading save and 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.. #1145

Naming convention (no need to specify Navigate)

Tip: You can understand the content of this section a little. It is not necessary to master it. You can skip it.

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; }
}

Reference

Basic

使用指南

学习与交流

信息

Clone this wiki locally