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 provides a basic method of sharding tables through AsTable. As a distributed repository, and GuidRepository as a distributed storage, realizes the encapsulation of sharding tables and database (cross-server sharding-database is not supported).

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

Above we got a log repository, which corresponds to the shareding-table by year and month. Using CURD operation will finally take effect in the Log_201903 table.

Notice:

• Versions after v0.11.12 can use CodeFirst to migrate sharding tables.
• Do not use lazy loading in the entity type of sharding tables and database.

Compatibility Problems

The output inserted feature provided by SqlServer. When the table uses auto-increment or the database defines a default value, use this feature to quickly return the inserted data. PostgreSQL also has similar functions, but not every database supports it.

When a database that does not support this feature (Sqlite/MySql/Oracle/Damen/MsAccess) is used, and the entity uses auto-increment attributes, the batch insertion of the repository will be executed one by one. The following improvements can be considered:

• Use uuid as the primary key (ie Guid).
• Avoid using the default value function of the database.

Cascade Saving

Please view the documentation of Cascade Saving.

APIs

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

Method	Return	Parameter	Description
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	无	完成编辑数据，进行保存动作

State management can realize that Update only updates the changed fields (not all fields), and it is very comfortable to use Attach and Update flexibly.

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》