Linq

DaiWK發表於2024-07-11
//實體類定義

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();

相關文章