C# Linq – LINQ主要的用途就是在資訊流當中, 把資訊源過濾, 計算, 排序等, 最後產生資料結果. 而資料源可以是資料庫中的資料表, 也可以為List, array等的資料.
Linq的好處
- 使用Linq的好處當然就是易於使用, 雖然不用LINQ也能用C#的FOR, FOR EACH, IF等寫出邏輯, 但這方法往往產生比LINQ更長的Code, 令程式碼看起來不夠精簡. 而配合ENTITY FRAMEWORK的使用, 更可把資料由LINQ轉化為SQL (Linq to SQL).
- 比起用ADO.NET的Dataset及Datatable等, 由EF及Linq產生出來的資料本身是實化 (Class Object), 令寫起來更方便.
LINQ的用法
LINQ的概念有些像PIPELINE的概念, 只需由DATA SOURCE至TOLIST等的中間加入你需要用的LINQ FUNCTION(如過濾, 排序)便可. 例如如果我只想過濾List, 只在中間加上WHERE便可了. 再想在過濾後加上排序, 只要在WHERE後加上Orderby便可.
那現在介紹7個最常用的LINQ METHOD:
- SQL般的過濾用法: Linq Where, Linq Orderby, Linq Select , Linq Distinct,
- SQL般的合併用法: Linq Join, Linq Left join, Linq Merge
- SQL般的統計用法: Linq Aggregate, Linq Group by, Linq max, Linq min, Linq sum, Linq avg, Linq count.
Let’s Code!
| 初始化 |
| public class Student{ public string studentId{get;set;} public string classFid{get;set;} public string studentName{get;set;} public int Age{get;set;} } public class SchoolClass{ public string classId{get;set;} public string className{get;set;} } List<Student> students = new List<Student>{ new student(){ studentId = ‘A101’, studentName = “陳佐治”, Age = 19 }, ……. } |
| 過濾例子 (Where) |
| var studentResults = students.Where(s=>s.Age >= 18).ToList(); from x in students where x.Age >= 18 select x; |
| 排序例子(Orderby) |
| var queryOrder = from e in students orderby e. studentName, e. Age select e; var queryOrder = students.OrderBy (e => e. studentName).ThenBy(e => e. Age) |
| 選取例子(Distinct, Select) |
var student = from p in students group p by new {p.studentId } into mygroup select mygroup.FirstOrDefault(); var query = students.DistinctBy(p => p.studentName); var query = students.Distinct(); |
| 綜合例子(Where, Orderby, Distinct, Select) |
| Inner Join例子(Join) |
| var query = from person in students join class in classes on person.classFid equals class.classId select new { StudentName = c.studentName, ClassName = o.className }; students.Join ( classes, c => c.classFid, o => o. classId, (c, o) => new{ StudentName = c.studentName, ClassName = o.className } ) |
| Left Join |
| var query = from person in students join class in classes on person.classFid equals class.classId into gj from subclass in gj.DefaultIfEmpty() select new { StudentName = c.studentName, ClassName = subclass?.Name ?? string.Empty }; |
| Merge 例子 (merge) |
| Option 1: var result = students1.Concat(students2.OrderBy(x => x.studentName).ToList(); Option 2: var result = students1.Union(students2).ToList(); Option 3: var result = (from obj In students1.Union(students2) Order By obj.studentName Select obj).ToList(); |
| Aggregate例子 |
| //Find Max By Aggregate int test3 = students.Aggregate(func: (result, item) => { return result.Age > item.Age ? item : result; }); |
| Foreach 例子 |
| students.ToList().ForEach(i => i.DoStuff()); |
| Groupby例子 |
| var firstStudent = from student in students group data by student.studentName into g select g.First(); var query = students.GroupBy( student => Math.Floor(student.Age), student => student.Age, (studentAges, ages) => new { Key = studentAges, Count = studentAges.Count(), Min = studentAges.Min(), Max = studentAges.Max() }); |
| max, min, sum, avg, count例子 |
| var maxValue = students.Max(x => x.Age); var minValue = students.Min(x => x.Age); var sumValue = students.Sum(x => x.Age); var avgValue = students.Avg(x => x.Age); var cntValue = students.Count(); |