SqlSugar学习笔记八——自定义按区域分表查询

实体类

///<summary>
///
///</summary>
[SplitTable(SplitType._Custom01)]
[SugarTable("CommoditySubTableArea")]
public partial class CommoditySubTableArea
{
    /// <summary>
    /// 省份
    /// </summary> 
    [SplitField]
    public string? Province { get; set; }

    /// <summary>
    /// Desc:
    /// Default:
    /// Nullable:False
    /// </summary>           
    [SugarColumn(IsPrimaryKey = true)]
    public Guid Id { get; set; }

    /// <summary>
    /// Desc:
    /// Default:
    /// Nullable:True
    /// </summary>           
    public long? ProductId { get; set; }

    /// <summary>
    /// Desc:
    /// Default:
    /// Nullable:True
    /// </summary>           
    public int? CategoryId { get; set; }

    /// <summary>
    /// Desc:
    /// Default:
    /// Nullable:True
    /// </summary>           
    public string? Title { get; set; }

    /// <summary>
    /// Desc:
    /// Default:
    /// Nullable:True
    /// </summary>           
    public decimal? Price { get; set; }

    /// <summary>
    /// Desc:
    /// Default:
    /// Nullable:True
    /// </summary>           
    public string? Url { get; set; }

    /// <summary>
    /// Desc:
    /// Default:
    /// Nullable:True
    /// </summary>           
    public string? ImageUrl { get; set; }

    /// <summary>
    /// 创建时间
    /// </summary> 
    public DateTime Createtime { get; set; }

}

分表帮助类

public class AreaSubTableService : ISplitTableService
{
    //横向分表---以保存到某一些的数据为分表的维度

    public static Dictionary<string, string> _AreaDictionary;
    static AreaSubTableService()
    {
        _AreaDictionary = new Dictionary<string, string>()
        {
            {"Liaoning","辽宁" },
            {"Heilongjiang","黑龙江" },
            {"Jilin","吉林" },
            {"Hebei","河北" },
            {"Henan","河南" },
            {"Shandong","山东" },
            {"Shaanxi","陕西" },
            {"ShanxiS","山西" },
            {"Jiangsu","江苏" },
            {"Zhejiang","浙江" },
            {"Fujian","福建" },
            {"Guangdong","广东" },
            {"Hainan","海南" },
            {"Yunnan","云南" },
            {"Jiangxi","江西" },
            {"Hunan","湖南" },
            {"Hubei","湖北" },
            {"Sichuan","四川" },
            {"Gansu","甘肃" },
            {"Guizhou","贵州" }
        };
    }


    /// <summary>
    /// 获取所有的数据库中的表
    /// </summary>
    /// <param name="db"></param>
    /// <param name="EntityInfo"></param>
    /// <param name="tableInfos"></param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    public List<SplitTableInfo> GetAllTables(ISqlSugarClient db, EntityInfo EntityInfo, List<DbTableInfo> tableInfos)
    {
        List<SplitTableInfo> result = new List<SplitTableInfo>();
        //var keylist = _AreaDictionary.Select(c => c.Key).ToList();  
        foreach (var item in tableInfos)
        {
            if (item.Name.Contains("_Area_")) //区分标识如果不用正则符复杂一些，防止找错表
            {
                SplitTableInfo data = new SplitTableInfo()
                {
                    TableName = item.Name //要用item.name不要写错了
                };
                result.Add(data);
            }
        }
        return result.OrderBy(it => it.TableName).ToList();//打断点看一下有没有查出所有分表
    }

    /// <summary>
    /// 
    /// </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 splitColumn = entityInfo.Columns.FirstOrDefault(it => it.PropertyInfo.GetCustomAttribute<SplitFieldAttribute>() != null);

        return splitColumn.PropertyInfo.GetValue(entityValue, null);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="db"></param>
    /// <param name="EntityInfo"></param>
    /// <returns></returns>
    public string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo)
    {
        return EntityInfo.DbTableName + "_Area";
    }

    public string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo, SplitType type)
    {
        return EntityInfo.DbTableName + "_Area";
    }

    /// <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)
    {
        KeyValuePair<string, string> keyPar = _AreaDictionary.FirstOrDefault(c => fieldValue.ToString().Contains(c.Value));
        return entityInfo.DbTableName + "_Area_" + keyPar.Key; //根据值按首字母
    }
}

CRUD

/// <summary>
/// 自定义分表
/// </summary>
public static class CustomSubTableDemo
{
    /// <summary>
    /// 按年分表
    /// </summary>
    public static void ShowCustomSubTable()
    {
        var connectionconfig = new ConnectionConfig()
        {
            DbType = DbType.SqlServer,
            ConnectionString = "Data Source=DESKTOP-VF73RDU;Initial Catalog=CustomerDB;Persist Security Info=True;User ID=sa;Password=sa123",
            IsAutoCloseConnection = true
        };
        using (SqlSugarClient db = new SqlSugarClient(connectionconfig))
        {
            ///自己来制定定义的规则
            db.CurrentConnectionConfig.ConfigureExternalServices.SplitTableService = new AreaSubTableService();
            db.CodeFirst
                .SplitTables()//标识分表
                .InitTables<CommoditySubTableArea>(); //程序启动时加这一行,如果一张表没有会初始化一张

            var addModel = new CommoditySubTableArea()
            {
                CategoryId = 1,
                Createtime = DateTime.Now,
                ImageUrl = $"Images",
                Price = 345,
                ProductId = 2,
                Province = "湖北省",
                Title = "这里是一条测试数据",
                Url = "URL"
            };
            db.Insertable(addModel).SplitTable().ExecuteCommand();

            List<CommoditySubTableArea> addmodelList = new List<CommoditySubTableArea>();
            for (int i = 0; i < 10; i++)
            {
                foreach (var dic in AreaSubTableService._AreaDictionary)
                {
                    addmodelList.Add(new CommoditySubTableArea()
                                     {
                                         CategoryId = 1,
                                         Createtime = DateTime.Now,
                                         ImageUrl = $"Images_{i}",
                                         Price = 345,
                                         ProductId = i,
                                         Province = dic.Value,
                                         Title = "这里是一条测试数据",
                                         Url = "URL"
                                     });
                }
            }

            db.Insertable(addmodelList).SplitTable().ExecuteCommand();
            var listall = db.Queryable<CommoditySubTableArea>().Where(it => it.Province.Contains("湖北")).SplitTable(tas => tas.ContainsTableNames("_Area_")).ToList();

            CommoditySubTableArea admodel = new CommoditySubTableArea()
            {
                CategoryId = 1,
                Createtime = DateTime.Now,
                ImageUrl = $"Images_",
                Price = 345,
                ProductId = 34,
                Province = "广西",
                Title = "这里是一条测试数据",
                Url = "URL"
            };

            db.Insertable(admodel).SplitTable().ExecuteCommand();

            List<CommoditySubTableArea> pageList = db.Queryable<CommoditySubTableArea>().SplitTable(t => t.ContainsTableNames("CommoditySubTableArea_Area_")).ToPageList(1, 50);
        }
    }
}