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();
}
