LINQ查詢運算式 - where、order by、let
完整的資料結構範例放在文章的最下面，
其他說明則以 main function 為主。
四、where
where 子句位置是放在 from 句子之後(之下)， select 子句之上。
可以使用多個 where 條件去篩選
例：在眾多問題單中選出 bug 狀態還沒被處理掉，並且是由 Tim 負責的問題單。
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
User tim = SampleData.Users.TesterTim;
var query = from bug in SampleData.AllDefects
where bug.Status != Status.Closed
where bug.AssignedTo == tim
select bug;
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
}
執行結果為
多項 where 條件子句也可以合成一個 where 子句，使用「&&」運算元
var query = from bug in SampleData.AllDefects
where bug.Status != Status.Closed &&
bug.AssignedTo == tim
select bug;
五、order by
order by 子句為放在 where 子句下面，select 子句上面。
from 後的範圍變數會提供 order by 來操作
例：承上例，將該篩選出來的資料依嚴重性做排序，預設為由小到大(遞增)
Serverity 的權重順序為
namespace Chapter11.Model
{
public enum Severity : byte
{
Trivial,
Minor,
Major,
Showstopper,
}
}
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
User tim = SampleData.Users.TesterTim;
var query = from bug in SampleData.AllDefects
where bug.Status != Status.Closed
where bug.AssignedTo == tim
orderby bug.Severity
select bug;
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
}
執行結果為
如果要由大到小(遞減)排序則 order by 子句請加上「descending」
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
User tim = SampleData.Users.TesterTim;
var query = from bug in SampleData.AllDefects
where bug.Status != Status.Closed
where bug.AssignedTo == tim
orderby bug.Severity descending
select bug;
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
}
執行結果為
如果要考慮多個排序因素的話，則使用「,」符號來處理，
例如：依 Severity 做遞減排序後，再依 LastModified 做遞增排序。
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
User tim = SampleData.Users.TesterTim;
var query = from bug in SampleData.AllDefects
where bug.Status != Status.Closed
where bug.AssignedTo == tim
orderby bug.Severity descending, bug.LastModified
select bug;
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
}
執行結果為
注意，不要拆成兩個order by子句
orderby bug.Severity descending
orderby bug.LastModified
這樣的結果會變成針對servirity排序後，不管之前做好的排序，
再針對LastModified做一次重新排序。
orderby 子句在編譯時會轉成 OrderBy、OrderByDescending、ThenBy、ThenByDescending 擴充方法
六、let
let 子句可以新增一個範圍變數與其他子句操作
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
User tim = SampleData.Users.TesterTim;
var query = from user in SampleData.AllUsers
let theLength = user.Name.Length
select new { Name = user.Name, Length = theLength };
foreach (var item in query)
{
Console.WriteLine($"{item.Name} - {item.Length}");
}
}
}
}
執行結果為
資料結構範例
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
}
}
public class SampleData
{
static List<Defect> defects;
public static IEnumerable<Defect> AllDefects
{
get { return defects; }
}
static List<User> users;
public static IEnumerable<User> AllUsers
{
get { return users; }
}
public static class Users
{
public static readonly User TesterTim = new User("Tim Trotter", UserType.Tester);
public static readonly User TesterTara = new User("Tara Tutu", UserType.Tester);
public static readonly User DeveloperDeborah = new User("Deborah Denton", UserType.Developer);
public static readonly User DeveloperDarren = new User("Darren Dahlia", UserType.Developer);
public static readonly User ManagerMary = new User("Mary Malcop", UserType.Manager);
public static readonly User CustomerColin = new User("Colin Carton", UserType.Customer);
}
public static class Projects
{
public static readonly Project SkeetyMediaPlayer = new Project { Name = "Skeety Media Player" };
public static readonly Project SkeetyTalk = new Project { Name = "Skeety Talk" };
public static readonly Project SkeetyOffice = new Project { Name = "Skeety Office" };
}
public SampleData()
{
users = new List<User>
{
Users.TesterTim,
Users.TesterTara,
Users.DeveloperDeborah,
Users.DeveloperDarren,
Users.ManagerMary,
Users.CustomerColin
};
defects = new List<Defect>
{
new Defect
{
Project = Projects.SkeetyMediaPlayer,
Created = May(1),
CreatedBy = Users.TesterTim,
Summary = "MP3 files crash system",
Severity = Severity.Showstopper,
AssignedTo = Users.DeveloperDarren,
Status = Status.Accepted,
LastModified = May(23)
},
new Defect
{
Project = Projects.SkeetyMediaPlayer,
Created = May(6),
CreatedBy = Users.TesterTim,
Summary = "Installation is slow",
Severity = Severity.Trivial,
AssignedTo = Users.TesterTim,
Status = Status.Fixed,
LastModified = May(15)
},
new Defect
{
Project = Projects.SkeetyMediaPlayer,
Created = May(8),
CreatedBy = Users.DeveloperDarren,
Summary = "Subtitles only work in Welsh",
Severity = Severity.Major,
AssignedTo = Users.TesterTim,
Status = Status.Fixed,
LastModified = May(23)
},
new Defect
{
Project = Projects.SkeetyTalk,
Created = May(9),
CreatedBy = Users.TesterTim,
Summary = "User interface should be more caramelly",
Severity = Severity.Trivial,
AssignedTo = Users.DeveloperDarren,
Status = Status.Created,
LastModified = May(9)
},
new Defect
{
Project = Projects.SkeetyMediaPlayer,
Created = May(12),
CreatedBy = Users.TesterTim,
Summary = "Play button points the wrong way",
Severity = Severity.Major,
AssignedTo = Users.TesterTim,
Status = Status.Fixed,
LastModified = May(17)
},
new Defect
{
Project = Projects.SkeetyTalk,
Created = May(13),
CreatedBy = Users.TesterTim,
Summary = "Server crashes under heavy load (3 users)",
Severity = Severity.Major,
AssignedTo = Users.DeveloperDeborah,
Status = Status.Accepted,
LastModified = May(17)
},
new Defect
{
Project = Projects.SkeetyMediaPlayer,
Created = May(17),
CreatedBy = Users.TesterTim,
Summary = "Modern music sounds rubbish",
Severity = Severity.Trivial,
AssignedTo = Users.DeveloperDarren,
Status = Status.Created,
LastModified = May(17)
},
new Defect
{
Project = Projects.SkeetyTalk,
Created = May(18),
CreatedBy = Users.TesterTim,
Summary = "Webcam makes me look bald",
Severity = Severity.Showstopper,
AssignedTo = Users.TesterTim,
Status = Status.Fixed,
LastModified = May(27)
},
new Defect
{
Project = Projects.SkeetyMediaPlayer,
Created = May(22),
CreatedBy = Users.TesterTim,
Summary = "DVD Easter eggs unavailable",
Severity = Severity.Trivial,
AssignedTo = Users.DeveloperDarren,
Status = Status.Created,
LastModified = May(22)
},
new Defect
{
Project = Projects.SkeetyTalk,
Created = May(25),
CreatedBy = Users.TesterTim,
Summary = "Logs record confidential conversations",
Severity = Severity.Major,
AssignedTo = Users.DeveloperDarren,
Status = Status.Reopened,
LastModified = May(30)
},
new Defect
{
Project = Projects.SkeetyMediaPlayer,
Created = May(30),
CreatedBy = Users.TesterTim,
Summary = "Network is saturated when playing WAV file",
Severity = Severity.Minor,
AssignedTo = Users.TesterTim,
Status = Status.Fixed,
LastModified = May(31)
}
};
}
public static DateTime May(int day)
{
return new DateTime(2010, 5, day);
}
}
public class Defect
{
public Project Project { get; set; }
/// <summary>
/// Which user is this defect currently assigned to? Should not be null until the status is Closed.
/// </summary>
public User AssignedTo { get; set; }
public string Summary { get; set; }
public Severity Severity { get; set; }
public Status Status { get; set; }
public DateTime Created { get; set; }
public DateTime LastModified { get; set; }
public User CreatedBy { get; set; }
public int ID { get; private set; }
public Defect()
{
ID = StaticCounter.Next();
}
public override string ToString()
{
return string.Format("{0,2}: {1}\r\n ({2:d}-{3:d}, {4}/{5}, {6} -> {7})",
ID, Summary, Created, LastModified, Severity, Status, CreatedBy.Name,
AssignedTo == null ? "n/a" : AssignedTo.Name);
}
}
public static class StaticCounter
{
static int next = 1;
public static int Next()
{
return next++;
}
}
public class Project
{
public string Name { get; set; }
public override string ToString()
{
return string.Format("Project: {0}", Name);
}
}
public enum Severity : byte
{
Trivial,
Minor,
Major,
Showstopper,
}
public enum Status : byte
{
/// <summary>
/// Defect has been opened, but not verified as reproducible or an issue.
/// </summary>
Created,
/// <summary>
/// Defect has been verified as an issue requiring work.
/// </summary>
Accepted,
/// <summary>
/// Defect has been fixed in code, but not verified other than through developer testing.
/// </summary>
Fixed,
/// <summary>
/// Defect was fixed, but has now been reopened due to failing verification.
/// </summary>
Reopened,
/// <summary>
/// Defect has been fixed and tested; the fix is satisfactory.
/// </summary>
Closed,
}
public class User
{
public string Name { get; set; }
public UserType UserType { get; set; }
public User(string name, UserType userType)
{
Name = name;
UserType = userType;
}
public override string ToString()
{
return string.Format("User: {0} ({1})", Name, UserType);
}
}
public enum UserType : byte
{
Customer,
Developer,
Tester,
Manager,
}
}