Linq
//实体类定义 public class User { public int Id { get; set; } public string Name { get; set; } } public class UserIncome { public int Id { get; set; } public int UserId { get; set; } public int Amount { get; set; } public DateTime IncomeDate { get; set; } }
//数据初始化 List<User> users = new List<User>(); users.Add(new User() { Id = 1, Name = "dwk" }); users.Add(new User() { Id = 2, Name = "zs" }); users.Add(new User() { Id = 3, Name = "ls" }); users.Add(new User() { Id = 4, Name = "ww" }); List<UserIncome> userIncomes = new List<UserIncome>(); userIncomes.Add(new UserIncome() { Id = 1, UserId = 2, Amount = 30, IncomeDate = new DateTime(2024, 2, 3) }); userIncomes.Add(new UserIncome() { Id = 2, UserId = 2, Amount = 131, IncomeDate = new DateTime(2020, 2, 3) }); userIncomes.Add(new UserIncome() { Id = 3, UserId = 3, Amount = 332, IncomeDate = new DateTime(2023, 5, 3) }); userIncomes.Add(new UserIncome() { Id = 4, UserId = 2, Amount = 100, IncomeDate = new DateTime(2022, 7, 3) }); userIncomes.Add(new UserIncome() { Id = 6, UserId = 2, Amount = 30, IncomeDate = new DateTime(2024, 9, 3) }); userIncomes.Add(new UserIncome() { Id = 7, UserId = 2, Amount = 131, IncomeDate = new DateTime(2020, 12, 3) }); userIncomes.Add(new UserIncome() { Id = 8, UserId = 4, Amount = 332, IncomeDate = new DateTime(2023, 3, 3) }); userIncomes.Add(new UserIncome() { Id = 9, UserId = 4, Amount = 100, IncomeDate = new DateTime(2022, 4, 3) }); userIncomes.Add(new UserIncome() { Id = 10, UserId = 2, Amount = 120, IncomeDate = new DateTime(2021, 2, 3) });
//Left、Right Join var Query = from user in users join income in userIncomes on user.Id equals income.UserId into tempIcomes from income in tempIcomes.DefaultIfEmpty() select new { user=user, income=income, }; //Inner Join //筛选出有收入的User var InnerQuery = (from user in users join income in userIncomes on user.Id equals income.UserId select new { user = user, // income = income, }).ToList().Distinct(); //Group //统计User每个年度收入 var GroupQuery = from user in users join income in userIncomes on user.Id equals income.UserId group income by (user.Id,user.Name,income.IncomeDate.Year) into g select new { Name=g.Key.Name, Year=g.Key.Year, YearIncome=g.Sum(c=>c.Amount) }; var result = Query.ToList(); var result1= InnerQuery.ToList(); var result2= GroupQuery.ToList();
喜欢 C#、SQL、Web