一、首字母分表

1. 安裝 hyjiacan.pinyin4net

   > dotnet add package hyjiacan.pinyin4net --version 4.1.1
   

2. 創建分表服務

   /// <summary>
   /// Apricot 分表
   /// </summary>
   public class ApricotSplitTableService : ISplitTableService
   {
      /// <summary>
      /// sqlsugar 默認分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="EntityInfo"></param>
      /// <param name="tableInfos"></param>
      /// <returns></returns>
      public List<SplitTableInfo> GetAllTables(ISqlSugarClient db, EntityInfo EntityInfo, List<DbTableInfo> tableInfos)
      {
         List<SplitTableInfo> result = new List<SplitTableInfo>();
         foreach (var item in tableInfos)
         {
               if (item.Name.Contains("apricot")) //區分標識如果不用正則符復雜一些，防止找錯表
               {
                  SplitTableInfo data = new SplitTableInfo()
                  {
                     TableName = item.Name //要用item.name不要寫錯了
                  };
                  result.Add(data);
               }
         }
         return result.OrderBy(it => it.TableName).ToList();//打斷點看一下有沒有查出所有分表
      }
   
      /// <summary>
      /// sqlsugar 默認分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="entityInfo"></param>
      /// <param name="splitType"></param>
      /// <param name="entityValue"></param>
      /// <returns></returns>
      public object GetFieldValue(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object entityValue)
      {
         var column = entityInfo.Columns.FirstOrDefault(it => it.PropertyInfo.GetCustomAttribute<SplitFieldAttribute>() != null);
   
         var value = column.PropertyInfo.GetValue(entityValue, null);
   
         return value;
      }
   
      /// <summary>
      /// sqlsugar 默認分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="entityInfo"></param>
      /// <returns></returns>
      public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo)
      {
         return entityInfo.DbTableName;
      }
   
      /// <summary>
      /// sqlsugar 默認分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="entityInfo"></param>
      /// <param name="type"></param>
      /// <returns></returns>
      public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType type)
      {
         return entityInfo.DbTableName;//目前模式少不需要分類(自帶的有 日、周、月、季、年等進行區分)
      }
   
      /// <summary>
      /// 自定義分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="entityInfo"></param>
      /// <param name="splitType"></param>
      /// <param name="fieldValue"></param>
      /// <returns></returns>
      public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object fieldValue)
      {
         return $"{entityInfo.DbTableName}_{GetPinyin($"{fieldValue}")}"; //根據值按首字母
      }
      
      /// <summary>
      /// 獲取拼音首字母
      /// </summary>
      /// <param name="data"></param>
      /// <returns></returns>
      private static char GetPinyin(string data)
      {
         return Pinyin4Net.GetPinyin(data, PinyinFormat.None).FirstOrDefault();
      }
   }
   

3. 創建表實體

   // 指定自定義分表
   [SplitTable(splitType: SplitType._Custom01, CustomSplitTableService = typeof(ApricotSplitTableService))]
   public class Apricot
   {
      [SugarColumn(IsPrimaryKey = true)]
      public long Id { get; set; }
   
      // [SplitField] //標識一下分表字段
      public string Name { get; set; }
   
      // 分表字段、并且不存庫
      [SplitField, SugarColumn(IsIgnore = true)]
      public string SplitName { get; set; }
   
      public DateTime CreateTime { get; set; }
   }
   

4. 寫入數據 自動分表

   using var scope = app.Services.CreateScope();
   
   var context = scope.ServiceProvider.GetRequiredService<ISqlSugarClient>();
   
   var datas = new List<Apricot>()
      {
         new Apricot()
         {
            CreateTime = DateTime.Now,
            Name="測試",
            SplitName ="測試"
         } ,
         new Apricot()
         {
            CreateTime = DateTime.Now,
            Name="分表",
            SplitName ="分表"
         },
         new Apricot()
         {
            CreateTime = DateTime.Now,
            Name="數據",
            SplitName ="數據"
         }
      };
   
   List<long> ids = context.CopyNew().Insertable(datas).SplitTable().ExecuteReturnSnowflakeIdList();
   

5. 分表查詢

   using var scope = app.Services.CreateScope();
   
   var context = scope.ServiceProvider.GetRequiredService<ISqlSugarClient>();
   
   var apricot = await context.Queryable<Apricot>().Where(p => p.Name == "測試").SplitTable().ToListAsync();
   

6. 參考
   • 官網