Repository Layer

中文 | English

As an extension, FreeSql.Repository realizes the functions of the common DAL. There is a certain standard definition for the repository layer. FreeSql.Repository refers to the interface design of Abp vNext, defines and implements the basic repository layer for CURD operations.

Features

• Select/Attach: Snapshot object, the corresponding Update only updates the changed fields.
• Insert: Insert data, adapt to each database to optimize execution ExecuteAffrows, ExecuteIdentity or ExecuteInserted;
• InsertOrUpdate: Insert or update data.
• SaveMany: Quickly save navigation objects (one-to-many, many-to-many).

Install

Situation 1: .NET Core or .NET 5.0+

dotnet add package FreeSql.Repository

Situation 2、.NET Framework

Install-Package FreeSql.DbContext

Declaring

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.Sqlite, connectionString)
    //Automatically synchronize the entity structure to the database.
    .UseAutoSyncStructure(true) 
    //Be sure to define as singleton mode
    .Build(); 

public class Song {
    [Column(IsIdentity = true)]
    public int Id { get; set; }
    public string Title { get; set; }
}

Usage

Method 1. The extension method of IFreeSql

var curd = fsql.GetRepository<Song>();

Note: Repository objects are not safe under multiple threads, so you should not operate them on multiple threads at the same time.

• Does not support using the same repository instance in different threads at the same time

Method 2. Inheritance

public class SongRepository : BaseRepository<Song, int> {
    public SongRepository(IFreeSql fsql) : base(fsql, null, null) {}

    //Do something except CURD. 
}

Method 3: Dependency Injection

public void ConfigureServices(IServiceCollection services) {
    
    services.AddSingleton<IFreeSql>(Fsql);
    services.AddFreeRepository(filter => filter
        .Apply<ISoftDelete>("SoftDelete", a => a.IsDeleted == false)
        .Apply<ITenant>("Tenant", a => a.TenantId == 1)
        ,
        this.GetType().Assembly
    );
}

//Use in the controller
public SongsController(IBaseRepository<Song> repos1) {
}

Dependency injection can realize the global setting of filtering and verification, which is convenient for the design of tenant functions.

For more information: 《Filters and Global Filters》

State Management

Only update the changed properties:

var repo = fsql.GetRepository<Topic>();
var item = repo.Where(a => a.Id == 1).First();  //Take a snapshot of item at this time
item.Title = "newtitle";
repo.Update(item); //Compare with snapshots to get changes

//UPDATE `tb_topic` SET `Title` = @p_0
//WHERE (`Id` = 1)

Or further streamline:

var repo = fsql.GetRepository<Topic>();
var item = new Topic { Id = 1 };
repo.Attach(item); //Take a snapshot of item at this time
item.Title = "newtitle";
repo.Update(item); //Compare with snapshots to get changes

//UPDATE `tb_topic` SET `Title` = @p_0
//WHERE (`Id` = 1)

Filtering and Verification

Suppose we have two entities: User and Topic, and two repositories are defined in the domain class:

var userRepository = fsql.GetGuidRepository<User>();
var topicRepository = fsql.GetGuidRepository<Topic>();

In practice, we always worry about the data security of topicRepository, that is, it is possible to query or change the topic of other users. Therefore, we have made improvements in the v0.0.7 version, adding the filter lambda expression parameter.

var userRepository = fsql.GetGuidRepository<User>(a => a.Id == 1);
var topicRepository = fsql.GetGuidRepository<Topic>(a => a.UserId == 1);

• Attach this condition when querying/modifying/deleting, so that the data of other users will not be modified.
• When adding, use expressions to verify the legality of the data, if not legal, an exception will be thrown.

Sharding Tables and Database

FreeSql 提供 AsTable 分表的基础方法，GuidRepository 作为分存式仓储将实现了分表与分库（不支持跨服务器分库）的封装。

var logRepository = fsql.GetGuidRepository<Log>(null, oldname => $"{oldname}_{DateTime.Now.ToString("YYYYMM")}");

上面我们得到一个日志仓储按年月分表，使用它 CURD 最终会操作 Log_201903 表。

注意事项：

• v0.11.12以后的版本可以使用 CodeFirst 迁移分表；
• 不可在分表分库的实体类型中使用《延时加载》；

Compatibility Problems

SqlServer 提供的 output inserted 特性，在表使用了自增或数据库定义了默认值的时候，使用它可以快速将 insert 的数据返回。PostgreSQL 也有相应的功能，如此方便但不是每个数据库都支持。

当采用了不支持该特性的数据库（Sqlite/MySql/Oracle/达梦/MsAccess），并且实体使用了自增属性，仓储批量插入将变为逐条执行，可以考虑以下改进：

• 使用 uuid 作为主键（即 Guid）；
• 避免使用数据库的默认值功能；

Cascade

请移步文档《联级保存》

APIs

属性	返回值	说明
EntityType	Type	仓储正在操作的实体类型，注意它不一定是 TEntity
UnitOfWork	IUnitOfWork	正在使用的工作单元
Orm	IFreeSql	正在使用的 Orm
DbContextOptions	DbContextOptions	正在使用的 DbContext 设置，修改设置不影响其他
DataFilter	IDataFilter<TEntity>	仓储过滤器，本对象内生效
Select	ISelect<TEntity>	准备查询数据

方法	返回值	参数	说明
AsType	void	Type	改变仓储正在操作的实体类型
Get	TEntity	TKey	根据主键，查询数据
Find	TEntity	TKey	根据主键，查询数据
Delete	int	TKey	根据主键删除数据
Delete	int	Lambda	根据 lambda 条件删除数据
Delete	int	TEntity	删除数据
Delete	int	IEnumerable<TEntity>	批量删除数据
Insert	-	TEntity	插入数据，若实体有自增列，插入后的自增值会填充到实体中
Insert	-	IEnumerable<TEntity>	批量插入数据
Update	-	TEntity	更新数据
Update	-	IEnumerable<TEntity>	批量更新数据
InsertOrUpdate	-	TEntity	插入或更新数据
FlushState	-	无	清除状态管理数据
Attach	-	TEntity	附加实体到状态管理，可用于不查询就更新或删除
Attach	-	IEnumerable<TEntity>	批量附加实体到状态管理
AttachOnlyPrimary	-	TEntity	只附加实体的主键数据到状态管理
SaveMany	-	TEntity, string	保存实体的指定 ManyToMany/OneToMany 导航属性（完整对比）
BeginEdit	-	List<TEntity>	准备编辑一个 List 实体
EndEdit	int	无	完成编辑数据，进行保存动作

状态管理，可实现 Update 只更新变化的字段（不更新所有字段），灵活使用 Attach 和 Update 用起来非常舒服。

Reference

• 《FreeSql 101, Part 1: Insert Data》
• 《FreeSql 101, Part 2: Delete Data》
• 《FreeSql 101, Part 3: Update Data》
• 《FreeSql 101, Part 4: Query Data》
• 《FreeSql Optimization: Lazy Loading》
• 《FreeSql Optimization: Greed Loading》
• 《Sharding Tables and Database》
• 《Tenant》
• 《Filters and Global Filters》
• 《AOP》
• 《UnitOfWork》
• 《DbContext》