LINQ系列
在實務上在這記錄一些比較常用的LINQ使用的操作
public class Program
{
public static List<Student> students = new List<Student>
{
new Student{Id = "1",Name = "張三",Age = 25, Score = 90,ClassId = "101"},
new Student{Id = "2",Name = "兆四",Age = 33, Score = 70, ClassId = "101"},
new Student{Id = "3",Name = "兆四",Age =33, Score = 77,ClassId = "102"},
new Student{Id = "4",Name = "張三",Age = 25, Score = 65,ClassId = "102"},
new Student{Id = "5",Name = "張三",Age = 27, Score = 72,ClassId = "103"},
new Student{Id = "6",Name = "李六",Age = 28, Score = 66,ClassId = "103"},
};
public static List<StudentClass> studentClass = new List<StudentClass>
{
new StudentClass{ClassId = "101",ClassName = "一年一班" ,},
new StudentClass{ClassId = "102",ClassName = "一年二班"},
new StudentClass{ClassId = "103",ClassName = "一年三班"},
new StudentClass{ClassId = "104",ClassName = "一年四班"},
};
private static void Main(string[] args)
{
//找到第一位名叫張三的學生
var qryData1 = students.Where(x => x.Name == "張三").FirstOrDefault();
Console.WriteLine("");
Console.WriteLine($"Name={qryData1.Name},Age={qryData1.Age}");
//找出超過三十歲的學生
var qryData2 = students.Where(x => x.Age > 30).ToList();
foreach (var i in qryData2)
{
Console.WriteLine(i.Name);
}
//由小到大
Console.WriteLine("");
Console.WriteLine("Id排序");
var qryData3 = students.OrderBy(x => x.Id);
foreach (var i in qryData3)
{
Console.WriteLine(i.Id);
}
// 由大到小
Console.WriteLine("");
Console.WriteLine("Id排序 Desc");
var qryData4 = students.OrderByDescending(x => x.Id);
foreach (var i in qryData4)
{
Console.WriteLine(i.Id);
}
//Join用法
Console.WriteLine("");
Console.WriteLine("Join 用法");
var qryJoin = studentClass.Join(students,
c => c.ClassId,
s => s.ClassId,
(c, s) => new
{
ClassName = c.ClassName,
Name = s.Name,
ClassId = c.ClassId
}).OrderBy(cs => cs.ClassId).Where(cs => cs.ClassName == "一年一班");
foreach (var i in qryJoin)
{
Console.WriteLine($"班級：{i.ClassName} 名稱：{i.ClassName} 姓名：{i.Name}");
}
//Left Join
Console.WriteLine("");
Console.WriteLine("GroupJoin 用法1");
var qryLeftJoin1 = studentClass.GroupJoin(students,
c => c.ClassId,
s => s.ClassId,
(c, s) => new
{
ClassId = c.ClassId,
ClassName = c.ClassName,
s,
}).SelectMany(x => x.s.DefaultIfEmpty(), //要使用DefaultIfEmpty 才能轉換成Left Join
(c, s) => new
{
ClassName = c.ClassName,
Name = (s == null ? "" : s.Name)
});
foreach (var i in qryLeftJoin1)
{
Console.WriteLine($"班級：{i.ClassName} 學生姓名:{i.Name}");
}
Console.WriteLine("");
Console.WriteLine("GroupJoin 用法2");
var qryLeftJoin2 = studentClass.GroupJoin(students,
c => c.ClassId,
s => s.ClassId,
(c, s) => new
{
ClassName = c.ClassName,
s = s
});
foreach (var i in qryLeftJoin2)
{
Console.WriteLine($"班級：{i.ClassName}");
foreach (var s in i.s)
{
Console.WriteLine($"姓名:{s.Name}");
}
}
//Group By用法
Console.WriteLine("");
Console.WriteLine("Name Group");
var result1 = students.GroupBy(x => new { x.Name }).Select(x =>
new Student { Name = x.Key.Name }).ToList();
foreach (var i in result1)
{
Console.WriteLine($"Name={i.Name}");
}
Console.WriteLine("");
Console.WriteLine("Name Group Age Min Score Min");
var result2 = students.GroupBy(x => new { x.Name, }).Select(x => new Student
{
Name = x.Key.Name,
Age = x.Min(y => y.Age),
Score = x.Min(y => y.Score)
}).ToList();
foreach (var i in result2)
{
Console.WriteLine($"Name={i.Name},Age={i.Age},Score={i.Score}");
}
Console.WriteLine("");
Console.WriteLine("Name Group Age Max Score Max");
var result3 = students.GroupBy(x => new { x.Name, }).Select(x => new Student
{
Name = x.Key.Name,
Age = x.Max(y => y.Age),
Score = x.Max(y => y.Score)
}).ToList();
foreach (var i in result3)
{
Console.WriteLine($"Name={i.Name},Age={i.Age},Score={i.Score}");
}
Console.WriteLine("");
Console.WriteLine("Name Group Age Max Score Max");
var result4 = students.GroupBy(x => new { x.Name, }).Select(x => new Student
{
Name = x.Key.Name,
Age = x.Sum(y => y.Age),
Score = x.Max(y => y.Score)
}).ToList();
foreach (var i in result4)
{
Console.WriteLine($"Name={i.Name},Age={i.Age},Score={i.Score}");
}
Console.WriteLine("");
Console.WriteLine("Name Group Age Sum Score Sum");
var result5 = students.GroupBy(x => new { x.Name }).Select(x => new Student
{
Name = x.Key.Name,
Age = x.Sum(y => y.Age),
Score = x.Sum(y => y.Score)
}).ToList();
foreach (var i in result5)
{
Console.WriteLine($"Name={i.Name},Age={i.Age},Score={i.Score}");
}
}
}
public class Student
{
public string Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public int Score { get; set; }
public string ClassId { get; set; }
}
public class StudentClass
{
public string ClassId { get; set; }
public string ClassName { get; set; }
}
上面的是專門記錄比較常用在LINQ的常用部分 ，因為
早期學SQL的時候也是從最基本最常用的SELECT, FROM,
WHERE ,ORDER BY ,GROUP BY,INNER JOIN ,LEFT JOIN
這幾個很常用，可以應付很多的狀況。
註記：那關於ToLookup與GruopBy差異?，在延遲執行
Sample如下
var sampleGroup = students.GroupBy(s => s.ClassId);
var sampleToLookup = students.ToLookup(s => s.ClassId);
students.RemoveAll(s => true);
Console.WriteLine($"------GroupBy Sample------");
foreach (var i in sampleGroup)
{
Console.WriteLine($"classid={i.Key}");
foreach (var item in i)
{
Console.WriteLine($"name={item.Name},score={item.Score}");
}
}
Console.WriteLine($"------ToLookup Sample------");
foreach (var i in sampleToLookup)
{
Console.WriteLine($"classid={i.Key}");
foreach (var item in i)
{
Console.WriteLine($"name={item.Name},score={item.Score}");
}
}主要是GroupBy會等首次抓資料內容時才跑查詢，在之前就已經Clear了，因為資料清空，這樣結果
就是延遲執行所造成。
補充：如果要用類似WHERE IN的語法如下：
List<temp> tmps = new List<temp>
{
new temp(){Seq = 1,Qty = 1,Amt = 200},
new temp(){Seq = 1,Qty = 2,Amt = 250},
new temp(){Seq = 2,Qty = 1,Amt = 250},
new temp(){Seq = 3,Qty = 3,Amt = 250},
new temp(){Seq = 3,Qty = 2,Amt = 450},
};
var seqlist = new List<int>(){1,3};
var qry = tmps.Where(x => seqlist.Contains(x.Seq)).ToList();我針對序號1和3進行查詢，就會撈到四筆資料
案例：如果要把１跟３的序號資料，單價＊數量並加總起來該怎麼做呢？
List<temp> tmps = new List<temp>
{
new temp(){Seq = 1,Qty = 1,Amt = 200},
new temp(){Seq = 1,Qty = 2,Amt = 250},
new temp(){Seq = 2,Qty = 1,Amt = 250},
new temp(){Seq = 3,Qty = 3,Amt = 250},
new temp(){Seq = 3,Qty = 2,Amt = 450},
};
var seqlist = new List<int>(){1,3};
var qry = tmps.Where(x => seqlist.Contains(x.Seq)).ToList();
decimal total = qry.GroupBy(x => new {x.Seq}).Select(x => new
{
Amt = x.Sum(y=> y.Qty * y.Amt)
}).ToList().Sum(x=> x.Amt);
上述就能把你要的東西給加總起來。
之前有記錄了常用LINQ應用部分，這次是稍微複雜一點就是，我要把每個部門，找出分數最高的那一筆資料出來，該怎麼做呢?
解法一
List<Depart> departs = new List<Depart>
{
new Depart(){ Id=1,Name = "業務部"},
new Depart(){ Id=2,Name = "行銷部"},
new Depart(){ Id=3,Name = "生產部"}
};
List<ScoreReport> reports = new List<ScoreReport>
{
new ScoreReport(){No = 1,Id=1,Score = 60},
new ScoreReport(){No = 2,Id=1,Score = 70},
new ScoreReport(){No = 3,Id=1,Score = 65},
new ScoreReport(){No = 4,Id=2,Score = 30},
new ScoreReport(){No = 5,Id=2,Score = 25},
new ScoreReport(){No = 6,Id=2,Score = 60},
new ScoreReport(){No = 7,Id=3,Score = 40},
new ScoreReport(){No = 8,Id=3,Score = 50},
new ScoreReport(){No = 9,Id=3,Score = 85},
};
var qry = departs.Join(reports,
depart => depart.Id,
report => report.Id, (d,r) => new {d, r })
.GroupBy(g => new { g.d.Name})
.Select(g => new
{
Name = g.Key.Name,
Score = g.Max(p => p.r.Score)
}).ToList();
foreach (var item in qry)
{
Console.WriteLine(item.Name + " " + item.Score);
}解法一有個缺點是它只能針對彙整的屬性來顯示。
所以這時候延伸出解法二
var qry2 = departs.Join(reports,
depart => depart.Id,
report => report.Id, (d, r) => new { d, r })
.GroupBy(g => new { g.d.Name })
.Select(g => new { su= g.OrderByDescending(i => i.r.Score)})
.Select(x=>x.su.FirstOrDefault()).ToList();
foreach (var item in qry2)
{
Console.WriteLine(item.d.Name + " " + item.r.Score);
}
解法二是先把分數進行排序後，找出最大的那一筆資料出來。
近期遇到英文排序的問題，若是要按照大寫排序和小寫排序，可考慮用以下用法
List<string> strArr = new List<string>() { "D","d","A", "a", "B", "b", "C", "c" };
strArr = strArr.OrderBy(x => x, StringComparer.Ordinal).ToList();老E隨手寫