sql语句聚合函数、多表连接、Orderby、Groupby转Lambda表达式

先赋一个sql语句，然后转lambda表达式。

//select a.`Name` as 停车场, 
            //sum(b.ShouldPayAmount) as 总金额,
            //count(b.ShouldPayAmount) as 总条数
            //from parkinglot a left
            //join paymentorder b on b.ParkingLotId = a.Id
            //where a.TenantId = 3 and a.IsDeleted != 1 group by a.'Name'
            //ORDER by sum(b.ShouldPayAmount)

对多表进行连接，然后进行分组（这里只对一个分组，可以多个分组x=>new{x.t1,x.t2}），最后查询排序。

完整的代码：

class Program
    {
        static void Main(string[] args)
        {
            //select a.`Name` as 停车场, 
            //sum(b.ShouldPayAmount) as 总金额,
            //count(b.ShouldPayAmount) as 总条数
            //from parkinglot a left
            //join paymentorder b on b.ParkingLotId = a.Id
            //where a.TenantId = 3 and a.IsDeleted != 1
            //ORDER by sum(b.ShouldPayAmount)
            List<parkinglot> l1 = new List<parkinglot> {
                new parkinglot(){Id=1,Name="测试1",TenantId=3,IsDeleted=false},
                new parkinglot(){Id=2,Name="测试2",TenantId=3,IsDeleted=false},
                new parkinglot(){Id=3,Name="测试3",TenantId=2,IsDeleted=true}
            };
            List<paymentorder> l2 = new List<paymentorder>
            {
                new paymentorder(){Id=1,ParkingLotId=1,ShouldPayAmount=300},
                new paymentorder(){Id=2,ParkingLotId=2,ShouldPayAmount=200},
                new paymentorder(){Id=3,ParkingLotId=1,ShouldPayAmount=300}
            };
            var data = l1.Join(l2, p => p.Id, o => o.ParkingLotId, (p, o) => new { p, o.ShouldPayAmount })
                .Where(x => x.p.TenantId == 3 && x.p.IsDeleted != true).GroupBy(x => x.p.Name);
            var res = data.Select(x => new
            {
                停车场 = x.Key,                
                最大值= x.Max(s=>s.ShouldPayAmount),
                最小值= x.Min(s=>s.ShouldPayAmount),
                平均值= x.Average(s=>s.ShouldPayAmount),
                总金额 = x.Sum(s => s.ShouldPayAmount),
                总条数 = x.Count()
            }).OrderBy(x => x.总金额).ThenBy(x => x.总条数)//.Distinct()
                .ToList();
            var jsonData = JsonConvert.SerializeObject(res);
            System.Console.WriteLine(jsonData);
            System.Console.Read();
        }
        private class parkinglot
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public int TenantId { get; set; }
            public bool IsDeleted { get; set; }
        }
        private class paymentorder
        {
            public int Id { get; set; }
            public int ParkingLotId { get; set; }
            public double ShouldPayAmount { get; set; }
        }
    }