sqlsugar的sum的用法

 

 

通过论坛的检索 查到三篇文章是关于sum的

http://www.codeisbug.com/Ask/9/4531

http://www.codeisbug.com/Ask/14/4128

 

文章1：
2018/10/12

我自己已经找到解决办法了,分享一下

   var query = DB.FullClient.Queryable<ContractOrders, Contracts>((co, cs) => new object[]{

                JoinType.Inner,co.ContractId==cs.Id

            })

            .WhereIF(!string.IsNullOrEmpty(customerId), (co, cs) => cs.CustomerId == customerId)

            .WhereIF(startDate.HasValue, (co, cs) => cs.ActivateDate >= startDate.Value)

            .WhereIF(endDate.HasValue, (co, cs) => cs.ActivateDate <= endDate.Value.AddDays(1))

            .WhereIF(status.HasValue, co => co.Status == status.Value)

            .Select((co, cs) => new ContractOrdersP<Contracts>

            {

                Code = co.Code,

                Id = co.Id,

                CreateTime = co.CreateTime,

                ContractId = co.ContractId,

                Remark = co.Remark,

                Status = co.Status,

                Contracts = cs,

                TotalPrice = SqlFunc.Subqueryable<ProductConfigs>()

               .Where(r => r.ContractOrderId == co.Id)

                .GroupBy(r => r.ContractOrderId)

                .Select(r => SqlFunc.AggregateSum(r.Quantity * r.DiscountPrice))

            });

用这个函数可以解决, SqlSugar作者 定义了AggregateSum 这种方法, 一般人还真不知道

 文章2：

4.x

.Max(it => it.id);

.Sum(it => it.id);

 官方文档的查询函数

 

SqlFunc.Subqueryable子查询的写法

 //子查询的版本
                var list = db.Queryable<TC020_Purchase>()
                        .Select(a => new
                        {
                            guid = a.TC020_Guid,
                            compony = a.TC020_Company,
                            SignDate = a.TC020_SignDate,
                            ProjectTitle = a.TC020_ProjectTitle,
                            Signer = a.TC020_Signer,
                            ContractID = a.TC020_ContractID,
                            PaymentClaus = a.TC020_PaymentClause,
                            TotalCount = a.TC020_TotalAmount,

                            paid = SqlFunc.Subqueryable<TC022_PurchasePaymentRecord>()
                                .Where(b => b.TC020_Guid == a.TC020_Guid)
                                .Select(b => SqlFunc.AggregateSum(b.TC022_NotPaid.Value)).ToString(),

                            notpaid = SqlFunc.Subqueryable<TC022_PurchasePaymentRecord>()
                                .Where(b => b.TC020_Guid == a.TC020_Guid)
                                .Select(b => SqlFunc.AggregateSum(b.TC022_Paid.Value)).ToString()

                        }).ToList();

 

 Group By的写法

  //Group by 版本
                var group = db.Queryable<TC020_Purchase, TC022_PurchasePaymentRecord>((a, b) => new object[] {
                            JoinType.Left,a.TC020_Guid==b.TC020_Guid})

                        .GroupBy(a => new {
                            a.TC020_Guid,
                            a.TC020_Company,
                            a.TC020_SignDate,
                            a.TC020_ProjectTitle,
                            a.TC020_Signer,
                            a.TC020_ContractID,
                            a.TC020_PaymentClause,
                            a.TC020_SalesContractID,
                            a.TC020_TotalAmount
                        })
                        .Select((a,b)=> new {
                            paid=SqlFunc.AggregateSum(b.TC022_NotPaid.Value).ToString(),
                            notpaid= SqlFunc.AggregateSum(b.TC022_Paid.Value).ToString()

                        }).ToList();