[LINQ] Pivot

在 Line 群組上看見有人發問，基本上就是 TSQL Pivot 轉置就可以完成的需求，故意用 LINQ 來練習看看

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Leave> Data = Leave.GetData();

            var result = Data.GroupBy(g => g.Employee)
                .Select(g => new
                {
                    Employee = g.Key,
                    Personal = g.Where(w => w.Kind == "事假").Sum(s => s.Hours),
                    Sick = g.Where(w => w.Kind == "事假").Sum(s => s.Hours),
                    Company = g.Where(w => w.Kind == "公假").Sum(s => s.Hours),
                    Paternity = g.Where(w => w.Kind == "陪產假").Sum(s => s.Hours)
                });

            Console.WriteLine(" 員工 - 事假 - 病假 - 公假 - 陪產假");
            foreach (var item in result)
            {
                Console.WriteLine($"{item.Employee} - {item.Personal.ToString("00.0")} - {item.Sick.ToString("00.0")} - {item.Company.ToString("00.0")} - {item.Paternity.ToString("00.0")}");
            }
        }
    }

    public class Leave
    {
        public string Employee { get; set; }
        public DateTime Date { get; set; }
        public string Kind { get; set; }
        public decimal Hours { get; set; }

        public static List<Leave> GetData()
        {
            return new List<Leave>()
            {
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,1) , Kind = "事假" , Hours = 3.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,2) , Kind = "事假" , Hours = 8.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,3) , Kind = "事假" , Hours = 4.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,10) , Kind = "病假" , Hours = 8.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,20) , Kind = "病假" , Hours = 1.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,25) , Kind = "公假" , Hours = 8.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,26) , Kind = "公假" , Hours = 8.0m} ,
                new Leave() { Employee = "AAAAA" , Date = new DateTime(2010,10,27) , Kind = "公假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,1) , Kind = "事假" , Hours = 6.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,9) , Kind = "病假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,19) , Kind = "病假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,25) , Kind = "陪產假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,26) , Kind = "陪產假" , Hours = 8.0m} ,
                new Leave() { Employee = "BBBBB" , Date = new DateTime(2010,10,27) , Kind = "陪產假" , Hours = 8.0m}
            };
        }
    }
}

延伸閱讀
• [SQL] PIVOT 和 UNPIVOT
• [SQL] AdventureWork T-SQL 練習

參考資料
• StackOverFlow
• LINQ Pivot

[LINQ] XML Delete

學習 LINQ to XML 筆記，該篇是紀錄如何利用 Remove() 來移除 XML 檔案內的 Element

Employee Class

namespace Helper
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public bool Gender { get; set; }
        public int Salary { get; set; }
    }
}

DataHelper Class

namespace Helper
{
    public static class DataHelper
    {
        public static string XMLPath = @"D:\Temp\XMLDemo.xml";

        public static List<Employee> GetEmployees()
        {
            return new List<Employee>
            {
                new Employee {ID = 1, Name = "張三", Gender = false, Salary = 50000},
                new Employee {ID = 2, Name = "李四", Gender = true,  Salary = 45000},
                new Employee {ID = 3, Name = "王五", Gender = false, Salary = 65000},
                new Employee {ID = 4, Name = "丁六", Gender = true,  Salary = 28000}
            };
        }

        public static void XMLFileExist()
        {
            if (File.Exists(XMLPath) == false)
            {
                throw new FileNotFoundException(XMLPath);
            }
        }
    }
}

[LINQ] XML Update

學習 LINQ to XML 筆記，該篇是紀錄如何利用 SetValue()、SetElementValue() 和 SetAttributeValue() 來對 XML 檔案內的 Element 進行資料更新

Employee Class

namespace Helper
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public bool Gender { get; set; }
        public int Salary { get; set; }
    }
}

DataHelper Class

namespace Helper
{
    public static class DataHelper
    {
        public static string XMLPath = @"D:\Temp\XMLDemo.xml";

        public static List<Employee> GetEmployees()
        {
            return new List<Employee>
            {
                new Employee {ID = 1, Name = "張三", Gender = false, Salary = 50000},
                new Employee {ID = 2, Name = "李四", Gender = true,  Salary = 45000},
                new Employee {ID = 3, Name = "王五", Gender = false, Salary = 65000},
                new Employee {ID = 4, Name = "丁六", Gender = true,  Salary = 28000}
            };
        }

        public static void XMLFileExist()
        {
            if (File.Exists(XMLPath) == false)
            {
                throw new FileNotFoundException(XMLPath);
            }
        }
    }
}

[LINQ] XML Insert

學習 LINQ to XML 筆記，該篇是紀錄如何利用 Add()、AddFirst()、AddBeforeSelf() 和 AddAfterSelf() 來對 XML 檔案插入 Element 資料

Employee Class

namespace Helper
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public bool Gender { get; set; }
        public int Salary { get; set; }
    }
}

DataHelper Class

namespace Helper
{
    public static class DataHelper
    {
        public static string XMLPath = @"D:\Temp\XMLDemo.xml";

        public static List<Employee> GetEmployees()
        {
            return new List<Employee>
            {
                new Employee {ID = 1, Name = "張三", Gender = false, Salary = 50000},
                new Employee {ID = 2, Name = "李四", Gender = true,  Salary = 45000},
                new Employee {ID = 3, Name = "王五", Gender = false, Salary = 65000},
                new Employee {ID = 4, Name = "丁六", Gender = true,  Salary = 28000}
            };
        }

        public static void XMLFileExist()
        {
            if (File.Exists(XMLPath) == false)
            {
                throw new FileNotFoundException(XMLPath);
            }
        }
    }
}

[LINQ] XML Query

學習 LINQ to XML 筆記，該篇是紀錄如何利用 Descendants() 和 Elements() 來搜尋 XML 檔案

Employee Class

namespace Helper
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public bool Gender { get; set; }
        public int Salary { get; set; }
    }
}

DataHelper Class

namespace Helper
{
    public static class DataHelper
    {
        public static string XMLPath = @"D:\Temp\XMLDemo.xml";

        public static List<Employee> GetEmployees()
        {
            return new List<Employee>
            {
                new Employee {ID = 1, Name = "張三", Gender = false, Salary = 50000},
                new Employee {ID = 2, Name = "李四", Gender = true,  Salary = 45000},
                new Employee {ID = 3, Name = "王五", Gender = false, Salary = 65000},
                new Employee {ID = 4, Name = "丁六", Gender = true,  Salary = 28000}
            };
        }

        public static void XMLFileExist()
        {
            if (File.Exists(XMLPath) == false)
            {
                throw new FileNotFoundException(XMLPath);
            }
        }
    }
}

[LINQ] 建立 XML 檔案

學習 LINQ to XML 筆記，該篇是紀錄如何利用 XDocument 和 XElement 來建立 XML 檔案

Employee Class

namespace Helper
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public bool Gender { get; set; }
        public int Salary { get; set; }
    }
}

DataHelper Class

namespace Helper
{
    public static class DataHelper
    {
        public static string XMLPath = @"D:\Temp\XMLDemo.xml";

        public static List<Employee> GetEmployees()
        {
            return new List<Employee>
            {
                new Employee {ID = 1, Name = "張三", Gender = false, Salary = 50000},
                new Employee {ID = 2, Name = "李四", Gender = true,  Salary = 45000},
                new Employee {ID = 3, Name = "王五", Gender = false, Salary = 65000},
                new Employee {ID = 4, Name = "丁六", Gender = true,  Salary = 28000}
            };
        }

        public static void XMLFileExist()
        {
            if (File.Exists(XMLPath) == false)
            {
                throw new FileNotFoundException(XMLPath);
            }
        }
    }
}

[git] 檔案移除版控

之前把某個 Project 送進版控時，該 Solution 沒有 .gitignore 檔案，最近有新專案也放進該 Solution 內，build 時才發現為什麼會有 bin 資料夾內的 change 產生阿，把 .gitignore 建立後也還是會有，查資料發現檔案進入版控後，建立或修改 .gitignore 檔案，必須手動把檔案從版控內移除並 commit 才算真的移除

建立 .gitignore 檔案後，build 後 bin 資料夾內還是會有 change 出現

git rm --cached WindowsFormsApp2/bin/Debug/\*

git status

git commit -m "Remove Version Control"

再進行 build，bin 資料夾內就不會再有 change 出現啦

參考資料
• 解决.gitignore文件忽略规则无效git依然跟踪修改的问题
• Udemy 課程問題回答