LINQ查詢運算式 - inner join、left join、Cartesian Product
完整的資料結構範例放在文章的最下面，
其他說明則以 main function 為主。
七、join(inner join)
1、根據專案名稱列出所有問題單的摘要與專案連絡 mail
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
var query = from defect in SampleData.AllDefects
join subscription in SampleData.AllSubscriptions
on defect.Project equals subscription.Project
select new { defect.Summary, subscription.EmailAddress };
foreach (var item in query)
{
Console.WriteLine($"{item.Summary} - {item.EmailAddress}");
}
}
}
}
執行結果為
說明：
a、其實架構還蠻像MSSQL的語法，只是on後面的判斷式是由 defect 與 subscription 範圍變數所帶出的，
另外其判斷式的等於符號已改成 equals 英文寫法。
b、on 後面判斷式的連結索引鍵位置不能亂放，
他是對等於區域變數的位置，如果順序對調會出錯。
c、當兩資料要 join 時，通常會將資料多的放在左邊，
資料少的放在 join 的右邊，左邊是循序存取區，右邊是記憶體緩衝區，
目的是儘量不占記憶體空間。
2、要對來源資料進行篩選，在 join 之前會來的更有效率
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
var query = from defect in SampleData.AllDefects
where defect.Status == Status.Accepted
join subscription in SampleData.AllSubscriptions
on defect.Project equals subscription.Project
select new { defect.Summary, subscription.EmailAddress };
foreach (var item in query)
{
Console.WriteLine($"{item.Summary} - {item.EmailAddress}");
}
}
}
}
執行結果為
3、承上結果相同，但對 defect 使用子查詢的寫法
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
var query = from subscription in SampleData.AllSubscriptions
join defect in (
from defect in SampleData.AllDefects
where defect.Status == Status.Accepted
select defect
)
on subscription.Project equals defect.Project
select new { defect.Summary, subscription.EmailAddress };
foreach (var item in query)
{
Console.WriteLine($"{item.Summary} - {item.EmailAddress}");
}
}
}
}
執行結果為
八、join...into(left join)
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
var query = from defect in SampleData.AllDefects
join subscription in SampleData.AllSubscriptions
on defect.Project equals subscription.Project
into theInnerjoin
select new { Defect = defect, innerjoin = theInnerjoin };
foreach (var entry in query)
{
foreach (var item in entry.innerjoin)
{
Console.WriteLine($"{entry.Defect.Summary} - {item.EmailAddress}");
}
}
}
}
}執行結果為
說明：
into 是用來把 inner join 後的結果投影到一個範圍變數，
一個 defect 有多筆的 subscription，而以 left join 效果來說，
以 defect 為基底，找出每一個 defect 所對應到多筆的 subscription (裡的 E-mail)。
註：雖然效果等同於 left join，但是其跟 T-SQL 做法很不一樣，很「程式化」的感覺。
九、from (Cartesian Product)
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
SampleData data = new SampleData();
var query = from user in SampleData.AllUsers
from project in SampleData.AllProjects
select new { User = user, Project = project };
foreach (var pair in query)
{
Console.WriteLine("{0}/{1}",
pair.User.Name,
pair.Project.Name);
}
}
}
}
執行結果為
說明：
AllUsers 有六位，AllProjects 有三個，其 Cartesian Product 的效果為
將 AllUsers 與 AllProjects 作相乘動作，其共會有 6 * 3 = 18 種組合。
using System;
using System.Collections.Generic;
using System.Linq;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
}
}
public class SampleData
{
static List<Project> projects;
public static IEnumerable<Project> AllProjects
{
get { return projects; }
}
static List<NotificationSubscription> subscriptions;
public static IEnumerable<NotificationSubscription> AllSubscriptions
{
get { return subscriptions; }
}
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()
{
projects = new List<Project>
{
Projects.SkeetyMediaPlayer,
Projects.SkeetyTalk,
Projects.SkeetyOffice
};
subscriptions = new List<NotificationSubscription>
{
new NotificationSubscription { Project=Projects.SkeetyMediaPlayer, EmailAddress="media-bugs@skeetysoft.com" },
new NotificationSubscription { Project=Projects.SkeetyTalk, EmailAddress="talk-bugs@skeetysoft.com" },
new NotificationSubscription { Project=Projects.SkeetyOffice, EmailAddress="office-bugs@skeetysoft.com" },
new NotificationSubscription { Project=Projects.SkeetyMediaPlayer, EmailAddress="theboss@skeetysoft.com"}
};
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 NotificationSubscription
{
/// <summary>
/// Project for which this subscriber is notified
/// </summary>
public Project Project { get; set; }
/// <summary>
/// The address to send the notification to
/// </summary>
public string EmailAddress { get; set; }
}
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,
}
}