七、SQLSugar 层级查询、拼接查询...

#region 层级查询
//获取树形数据,Country实体可以去看前面配置实体那篇文章
List<Country> country = db.Queryable<Country>().ToTree(t => t.CountryList, t => t.FPID, 0);
//获取是上级数据集
List<Country> country1 = db.Queryable<Country>().ToParentList(t => t.FPID, 1);
//获取子集
List<Country> country2 = db.Queryable<Country>().ToChildList(t => t.FPID, 1);
#endregion


#region 动态条件拼接查询
string key = "测试";//关键字

//以前的写法
//Expression<Func<Country, bool>> expression1 = s => true;
//if (!string.IsNullOrWhiteSpace(key))
//{
//    Expression<Func<Country, bool>> expression2 = s => s.FName.Contains(key);
//    Expression.Add(expression1, expression2);
//    ...
//}

Expressionable<Country> expressionable = Expressionable.Create<Country>();//实例化Expressionable
expressionable.AndIF(!string.IsNullOrWhiteSpace(key), c => c.FName.Contains(key));//添加条件
Expression<Func<Country, bool>> expression = expressionable.ToExpression();//将Expressionable转为Expression
db.Queryable<Country>().Where(expression);//
#endregion

#region 汇总查询
 dynamic d = await Task.Run(() => Db.Queryable<tSign>()
                .Where(expression)
                .Select((sign) => new
                {
                    FCreateDate = sign.FCreateDate.ToString(order),
                    FFakeVisitNum = sign.FFakeVisitNum == 0 ? sign.FVisitNum : sign.FFakeVisitNum,
                    sign.FVisitNum,
                }).MergeTable().GroupBy(g => new
                {
                    g.FCreateDate
                })
                .Select(t => new
                {
                    t.FCreateDate,
                    FFakeVisitNum = SqlFunc.AggregateSum(t.FFakeVisitNum),
                    FVisitNum = SqlFunc.AggregateSum(t.FVisitNum)
                }).OrderBy(t => t.FCreateDate).ToList());
#endregion