Sqlsugar自动分库分表操作

1.重写AuditSqlLog 类

[Tenant(configId: "log")]
//[SugarTable("AuditSqlLog_20231201", "Sql审计日志")]
[SplitTable(SplitType.Month)] //按月分表 （自带分表支持 年、季、月、周、日）
[SugarTable($@"{nameof(AuditSqlLog)}_{{year}}{{month}}{{day}}")]
public class AuditSqlLog : BaseLog
{

}

2.给Repository层基类添加方法

/// <summary>
/// 分表查询
/// </summary>
/// <param name="whereExpression"></param>
/// <param name="orderByFields"></param>
/// <returns></returns>
Task<List<TEntity>> QuerySplit(Expression<Func<TEntity, bool>> whereExpression, string orderByFields = null);

/// <summary>
/// 写入实体数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
Task<List<long>> AddSplit(TEntity entity);

/// <summary>
/// 分表查询
/// </summary>
/// <param name="whereExpression">条件表达式</param>
/// <param name="orderByFields">排序字段，如name asc,age desc</param>
/// <returns></returns>
public async Task<List<TEntity>> QuerySplit(Expression<Func<TEntity, bool>> whereExpression, string orderByFields = null)
{
    return await _db.Queryable<TEntity>()
        .SplitTable()
        .OrderByIF(!string.IsNullOrEmpty(orderByFields), orderByFields)
        .WhereIF(whereExpression != null, whereExpression)
        .ToListAsync();
}

/// <summary>
/// 写入实体数据
/// </summary>
/// <param name="entity">数据实体</param>
/// <returns></returns>
public async Task<List<long>> AddSplit(TEntity entity)
{
    var insert = _db.Insertable(entity).SplitTable();
    //插入并返回雪花ID并且自动赋值ID　
    return await insert.ExecuteReturnSnowflakeIdListAsync();
}

3.给Service层基类添加方法

/// <summary>
/// 分表查询
/// </summary>
/// <param name="whereExpression"></param>
/// <param name="orderByFields"></param>
/// <returns></returns>
Task<List<TEntity>> QuerySplit(Expression<Func<TEntity, bool>> whereExpression, string orderByFields = null);

/// <summary>
/// 写入实体数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
Task<List<long>> AddSplit(TEntity entity);

/// <summary>
/// 分表查询
/// </summary>
/// <param name="whereExpression"></param>
/// <param name="orderByFields"></param>
/// <returns></returns>
public async Task<List<TEntity>> QuerySplit(Expression<Func<TEntity, bool>> whereExpression, string orderByFields = null)
{
    return await _baseRepository.QuerySplit(whereExpression, orderByFields);
}

/// <summary>
/// 写入实体数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public async Task<List<long>> AddSplit(TEntity entity)
{
    return await _baseRepository.AddSplit(entity);
}

4.在control层中使用

[HttpGet(Name = "GetWeatherForecastByName")] // or [HttpGet("one")]
public async Task<object> GetTwo()
{
    TimeSpan timeSpan = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
    var id = timeSpan.TotalSeconds.ObjToLong();
    await _auditSqlLogService.AddSplit(new AuditSqlLog()
    {
        Id = id,
        DateTime = Convert.ToDateTime("2025-08-20"),
    });

    var rltList = await _auditSqlLogService.QuerySplit(d => d.DateTime >= Convert.ToDateTime("2025-08-20"));

    Console.WriteLine("api request end...");
    return rltList;
}