static void InnerJoin() { var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, Name = t.Name }; var racersAndTeams = (from r in racers join t in teams on r.Year equals t.Year orderby t.Year select new { Year = r.Year, Champion = r.Name, Constructor = t.Name }).Take(10); Console.WriteLine("Year World Champion\t Constructor Title"); foreach (var item in racersAndTeams) { Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
/// <summary> /// 内连接 /// </summary> public void Fun4() { var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; var teams = from r in Formula1.GetConstructorChampions() from y in r.Years select new { Year = y, Name = r.Name }; var query = from r in racers join t in teams on r.Year equals t.Year select new { Year = r.Year, Racer = r.Name, Team = t.Name }; foreach (var item in query) { Console.WriteLine(item.Racer + "--" + item.Team + "--" + item.Year); } Console.Read(); }
//内连接 public static void InnerJoin() { var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, Name = t.Name }; var racersAndTeams = (from r in racers join t in teams on r.Year equals t.Year select new { r.Year, Champion = r.Name, Constructor = t.Name }).Take(10); racers = Formula1.GetChampions() .SelectMany(r => r.Years, (r1, year) => new //这里的r1就是Formula1.GetChampions()的项,year就是r.Years的每一项 { Year = year, Name = r1.FirstName + " " + r1.LastName }); teams = Formula1.GetConstructorChampions() .SelectMany(t => t.Years, (t, year) => new { Year = year, t.Name }); racersAndTeams = racers.Join( teams, //第一个参数传车队 r => r.Year, //外部选择器(racers的选择器) t => t.Year, //内部选择器(teams的选择器) (r, t) => //定义结果选择器 new { Year = r.Year, Champion = r.Name, Constructor = t.Name }).OrderBy(item => item.Year).Take(10); Console.WriteLine("Year World Champion\t Constructor Title"); foreach (var item in racersAndTeams) { Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
//做外连接 public static void LeftOutJoin() { var racers = Formula1.GetChampions() .SelectMany(r => r.Years, (r1, year) => new //这里的r1就是Formula1.GetChampions()的项,year就是r.Years的每一项 { Year = year, Name = r1.FirstName + " " + r1.LastName }); var teams = Formula1.GetConstructorChampions() .SelectMany(t => t.Years, (t, year) => new { Year = year, t.Name }); var racersAndTeams = (from r in racers join t in teams on r.Year equals t.Year into rt from t in rt.DefaultIfEmpty() orderby r.Year select new { Year = r.Year, Champion = r.Name, Constructor = t == null ? "no constructor championshit" : t.Name }).Take(10); racersAndTeams = racers.GroupJoin( teams, r => r.Year, t => t.Year, (r, ts) => new { Year = r.Year, Champion = r.Name, Constructor = ts }) .SelectMany( rt => rt.Constructor.DefaultIfEmpty(), (r, t) => new { Year = r.Year, Champion = r.Champion, Constructor = t?.Name ?? "no constructor championshit" }); foreach (var item in racersAndTeams) { Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
/// <summary> /// 内连接 /// </summary> public void Fun5() { //使用join子句可以根据特定的条件合并两个数据源,但之前要获得两个要连接的列表. //赛车手 var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; //车队 var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, Name = t.Name }; //根据赛车手获得冠军的年份和车队获得冠军的年份进行连接 var racersAndTeam = from r in racers join t in teams on r.Year equals t.Year select new { r.Year, Champion = r.Name, Constructor = t.Name }; foreach (var item in racersAndTeam) { Console.WriteLine($"{item.Year}年,冠军:{item.Champion,-20},冠军车队:{item.Constructor}"); } //foreach (var item in teams.OrderBy(i=>i.Year)) //{ // Console.WriteLine($"Year:{item.Year} , Name:{item.Name}"); //} //foreach (var item in racers.OrderBy(i=>i.Year)) //{ // Console.WriteLine($"Year:{item.Year} , Name:{item.Name}"); //} }
static void LeftOuterJoinWithMethods() { var racers = Formula1.GetChampions() .SelectMany(r => r.Years, (r1, year) => new { Year = year, Name = $"{r1.FirstName} {r1.LastName}" }); var teams = Formula1.GetConstructorChampions() .SelectMany(t => t.Years, (t, year) => new { Year = year, Name = t.Name }); var racersAndTeams = racers.GroupJoin( teams, r => r.Year, t => t.Year, (r, ts) => new { Year = r.Year, Champion = r.Name, Constructors = ts }) .SelectMany( item => item.Constructors.DefaultIfEmpty(), (r, t) => new { Year = r.Year, Champion = r.Champion, Constructor = t?.Name ?? "no constructor championship" }); Console.WriteLine("Year Champion\t\t Constructor Title"); foreach (var item in racersAndTeams) { Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
static void GroupJoin() { // var q = //from c in categories //join p in products on c equals p.Category into ps //select new { Category = c, Products = ps }; var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, Name = t.Name }; var racersAndTeams = from r in racers join t in teams on r.Year equals t.Year into ts select new { Year = r.Year, Racer = r.Name, Constructor = ts }; foreach (var r in racersAndTeams) { WriteLine($"{r.Year} {r.Racer}"); foreach (var t in r.Constructor) { WriteLine($"\t{t.Name}"); } } }
// 左外连接 public static void LeftOuterJoin() { var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, t.Name }; /* * 左外连接用join子句和DefaultIfEmpty的方法定义。 * 如果查询的左侧(赛车手)没有匹配的车队冠军, * 就使用DefaultIfEmpty方法定义其右侧默认值。 */ var racersAndTeams = (from r in racers join t in teams on r.Year equals t.Year into rt from t in rt.DefaultIfEmpty() orderby r.Year select new { r.Year, Champion = r.Name, Constructor = t == null ? "no constructor championship" : t.Name }).Take(10); Console.WriteLine("Year Champion\t\t Constructor Title"); foreach (var item in racersAndTeams) { Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
public static void InnerJoinWithMethods() { /* * 调用Join方法,通过第一个参数传递车队,把他们与赛车手连接起来, * 指定外部和内部集合的关键字选择器,并通过最后一个参数定义结果选择器。 */ var racers = Formula1.GetChampions() .SelectMany(r => r.Years, (r1, year) => new { Year = year, Name = $"{r1.FirstName} {r1.LastName}" }); var teams = Formula1.GetConstructorChampions() .SelectMany(t => t.Years, (t, year) => new { Year = year, t.Name }); var racersAndTeams = racers.Join( teams, r => r.Year, t => t.Year, (r, t) => new { r.Year, Champion = r.Name, Constructor = t.Name }).OrderBy(item => item.Year).Take(10); Console.WriteLine("Year World Champion\t Constructor Title"); foreach (var item in racersAndTeams) { Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
public static void InnerJoinWithMethods() { Console.WriteLine("Inner join with method syntax - combining driver and constructor championship based on the same year"); var racers = Formula1.GetChampions() .SelectMany(r => r.Years, (r1, year) => new { Year = year, Name = $"{r1.FirstName} {r1.LastName}" }); var teams = Formula1.GetConstructorChampions() .SelectMany(t => t.Years, (t, year) => new { Year = year, t.Name }); var racersAndTeams = racers.Join( teams, r => r.Year, t => t.Year, (r, t) => new { r.Year, Champion = r.Name, Constructor = t.Name }).OrderBy(item => item.Year).Take(10); Console.WriteLine("Year World Champion\t Constructor Title"); foreach (var item in racersAndTeams) { Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
static void LeftOuterJoin() { var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, Name = t.Name }; var racersAndTeams = from r in racers join t in teams on r.Year equals t.Year into rt from t in rt.DefaultIfEmpty() orderby r.Year select new { r.Year, Champion = r.Name, Construtor = t == null ? "no constructor championship" : t.Name }; foreach (var i in racersAndTeams) { Console.WriteLine($"{ i.Year,-5}{ i.Champion,-15}{i.Construtor}"); } Console.WriteLine(); }
public static void InnerJoin() { Console.WriteLine("Inner join with a LINQ query - combining driver and constructor championship based on the same year"); var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, t.Name }; var racersAndTeams = (from r in racers join t in teams on r.Year equals t.Year orderby t.Year select new { r.Year, Champion = r.Name, Constructor = t.Name }).Take(10); Console.WriteLine("Year World Champion\t Constructor Title"); foreach (var item in racersAndTeams) { Console.WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
/// <summary> /// 左外连接用join子句和DefaultIfEmpty方法定义 /// 使用左外连接,返回左边序列中的全部元素,即使他们在右边的序列中没有匹配的元素 /// </summary> public void Fun6() { //赛车手 var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; //车队 var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, Name = t.Name }; //由于赛车手冠军从1950年就有,赛车冠军从1958才有。使用左连接就能获取所有的赛车手冠军,即使他们在右边的序列中没有匹配的元素 var racersAndTeam = from r in racers join t in teams on r.Year equals t.Year into rt from a in rt.DefaultIfEmpty() orderby r.Year select new { Year = r.Year, Champion = r.Name, Constructor = a == null ? "no constructor championship" : a.Name }; foreach (var item in racersAndTeam) { Console.WriteLine($"{item.Year}年,冠军:{item.Champion,-20},冠军车队:{item.Constructor}"); } }
static void LeftOuterJoin() { var racers = from r in Formula1.GetChampions() from y in r.Years select new { Year = y, Name = r.FirstName + " " + r.LastName }; var teams = from t in Formula1.GetConstructorChampions() from y in t.Years select new { Year = y, Name = t.Name }; var racersAndTeams = (from r in racers join t in teams on r.Year equals t.Year into rt from t in rt.DefaultIfEmpty() orderby r.Year select new { Year = r.Year, Champion = r.Name, Constructor = t == null ? "no constructor championship" : t.Name }).Take(10); WriteLine("Year Champion\t\t Constructor Title"); foreach (var item in racersAndTeams) { WriteLine($"{item.Year}: {item.Champion,-20} {item.Constructor}"); } }
static void LeftOuterJoinWithMethod() { var racers = Formula1.GetChampions() .SelectMany(r => r.Years, (r1, year) => new { Year = year, Name = $"{r1.FirstName}{r1.LastName}" }) .Where(x => x.Year <= 1970) .OrderBy(r => r.Year); int k = 0; //foreach (var i in racers) //{ // Console.WriteLine($"{++k} { i.Year,-5}{ i.Name}"); //} //Console.WriteLine(); var teams = Formula1.GetConstructorChampions() .SelectMany(t => t.Years, (t1, year) => new { Year = year, Name = $"{t1.Name}" }) .Where(x => x.Year <= 1970) .OrderBy(r => r.Year); k = 0; //foreach (var i in teams) //{ // Console.WriteLine($"{++k} {i.Year,-15} { i.Name}"); //} //Console.WriteLine(); var racersAndteams = racers.GroupJoin( teams, r => r.Year, t => t.Year, (r, ts) => new { r.Year, Champion = r.Name, Construtors = ts }) .SelectMany( rt => rt.Construtors.DefaultIfEmpty(), (r, t) => new { Year = r.Year, Champion = r.Champion, Constructor = t?.Name ?? "No constructor" }); k = 0; foreach (var i in racersAndteams) { Console.WriteLine($"{++k} {i.Year,-15} { i.Champion,-25} { i.Constructor}"); //Console.WriteLine($"{++k} {i.Year,-15} { i.Champion,-25}"); } Console.WriteLine(); }
static void InnerJoin() { //var racers = from r in Formula1.GetChampions() // from y in r.Years // select new // { // Year = y, // Name = r.FirstName + " " + r.LastName // }; //var teams = from t in Formula1.GetConstructorChampions() // from y in t.Years // select new // { // Year = y, // Name = t.Name // }; //var RacersAndTeams = (from r in racers // join t in teams on r.Year equals t.Year // orderby r.Year // select new // { // r.Year, // Champion = r.Name, // Construtor = t.Name // }).Take(10); //foreach (var i in RacersAndTeams) //{ // Console.WriteLine($"{ i.Year,-5}{ i.Champion,-15}{i.Construtor}"); //} //Console.WriteLine(); //---- In just one query----- var RacersAndTeams1 = from r in from r1 in Formula1.GetChampions() from yr in r1.Years select new { Year = yr, Name = r1.FirstName + " " + r1.LastName } join t in from t1 in Formula1.GetConstructorChampions() from yt in t1.Years select new { Year = yt, Name = t1.Name } on r.Year equals t.Year orderby r.Year select new { Year = r.Year, Racer = r.Name, Team = t.Name }; foreach (var i in RacersAndTeams1) { Console.WriteLine($"{ i.Year,-5}{ i.Racer,-15}{i.Team}"); } Console.WriteLine(); }
static void InnerJoinWithMethods() { //var racers = Formula1.GetChampions() // .SelectMany(r => r.Years, (r1, year) => // new // { // Year = year, // Name = $"{r1.FirstName}{r1.LastName}" // }); //var teams = Formula1.GetConstructorChampions() // .SelectMany(t => t.Years, (t1, year) => // new // { // Year = year, // Name = $"{t1.Name}" // }); //var racersAndTeams = (racers.Join( // teams, // r => r.Year, // t => t.Year, // (r, t) => new // { // r.Year, // Champion = r.Name, // Construtor = t.Name // })).Take(10); //---- In just one query----- var racersAndTeams1 = (Formula1.GetChampions() .SelectMany(r => r.Years, (r1, year) => new { Year = year, Name = $"{r1.FirstName}{r1.LastName}" }) .Join( Formula1.GetConstructorChampions() .SelectMany(t => t.Years, (t1, year) => new { Year = year, Name = $"{t1.Name}" }), r => r.Year, t => t.Year, (r, t) => new { r.Year, Champion = r.Name, Construtor = t.Name })).Take(10); foreach (var i in racersAndTeams1) { Console.WriteLine($"{ i.Year,-5}{ i.Champion,-15}{i.Construtor}"); } Console.WriteLine(); }