/// <summary>
/// 左外联接
/// 返回第一个表的所有数据，和匹配到第二张表上的数据，没有匹配上，填充null
/// </summary>
public void LeftOuterJoinDemo()
{
var racers = from r in Formulal.GetChampions()
from y in r.Years
select new
{
Year = y,
Name = r.FirstName + " " + r.LastName
};
var teams = from t in Formulal.GetContructorChampions()
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,
Constructor = t == null ? "no constructor" : t.Name
}).Take(10);
Console.WriteLine("输出结果：");
foreach (var item in racersAndTeams)
{
Console.WriteLine($"{item.Year}:{item.Champion,-10} {item.Constructor}");
}
}
/// <summary>
/// ToLookUp ToList
/// </summary>
public void ToListDemo()
{
List <Racer> racers = (from r in Formulal.GetChampions()
where r.Starts > 150
orderby r.Starts descending
select r).ToList();
//ToLookup
var racers2 = (from r in Formulal.GetChampions()
from c in r.Cars
select new
{
Car = c,
Racer = r
})
.ToLookup(cr => cr.Car, cr => cr.Racer);
if (racers2.Contains("Williams"))
{
foreach (var winll in racers2["Williams"])
{
Console.WriteLine(winll);
}
}
var list = new ArrayList(Formulal.GetChampions() as ICollection);
//基于Object类型的ArrayList集合用Racer对象填充
var query = from r in list.Cast <Racer>()
where r.Country == "China"
orderby r.Wins descending
select r;
}
/// <summary>
/// 嵌套查询
/// </summary>
public void InnerQueryDemo()
{
var countries = from r in Formulal.GetChampions()
group r by r.Country into g
let count = g.Count()
orderby count descending, g.Key
where count >= 2
select new
{
Country = g.Key,
Count = count,
//使用内部子句嵌套
Racers = from r1 in g
orderby r1.LastName
select r1.FirstName + " " + r1.LastName
};
foreach (var item in countries)
{
Console.WriteLine($"{item.Country,-10} {item.Count}");
foreach (var name in item.Racers)
{
Console.Write(name + ";");
}
Console.WriteLine();
}
}
/// <summary>
/// 重命名
/// </summary>
public void LetRenameDemo()
{
var countries = from r in Formulal.GetChampions()
group r by r.Country into g
let count = g.Count()
orderby count descending, g.Key
where count >= 2
select new
{
Country = g.Key,
Count = count
};
var countries2 = Formulal.GetChampions()
.GroupBy(r => r.Country)
.Select(g => new { Group = g, Count = g.Count() }) //扩展方法重命名直接用Select
.OrderByDescending(g => g.Count)
.ThenBy(g => g.Group.Key)
.Where(g => g.Count >= 2)
.Select(g => new
{
Country = g.Group.Key,
Count = g.Count
});
}
/// <summary>
/// 分组
/// </summary>
public void GroupByDemo()
{
var countries = from r in Formulal.GetChampions()
group r by r.Country into g //将分组信息放入标识符g中
orderby g.Count() descending, g.Key
where g.Count() >= 2
select new
{
Country = g.Key,
Count = g.Count()
};
foreach (var item in countries)
{
Console.WriteLine(item.Country + item.Count);
}
var countries2 = Formulal.GetChampions()
.GroupBy(r => r.Country)
.OrderByDescending(g => g.Count())
.ThenBy(g => g.Key)
.Where(g => g.Count() >= 2)
.Select(g => new { Country = g.Key, Count = g.Count() });
foreach (var item in countries2)
{
Console.WriteLine(item.Country + item.Count);
}
}
/// <summary>
/// 热身
/// </summary>
public void QueryChinaChampions()
{
var query = from r in Formulal.GetChampions()
where r.Country == "China"
orderby r.Wins descending
select r;
//注意，执行foreach时，查询语句才会执行
foreach (Racer r in query)
{
Console.WriteLine($"{r:A}");
}
Console.WriteLine("----------------------------------");
var champions = new List <Racer>(Formulal.GetChampions());
IEnumerable <Racer> brazilChampions = champions.Where(r => r.Country == "China")
.OrderByDescending(r => r.Wins)
.Select(r => r);
foreach (Racer r in brazilChampions)
{
Console.WriteLine($"{r:A}");
}
}
/// <summary>
/// 多表查询
/// </summary>
public void SelectManyDemo()
{
//Formulal.GetChampions()返回Racer集合，每一个Racer的属性Cars是一个字符串数组
var ferrariDrivers = from r in Formulal.GetChampions()
from c in r.Cars
where c == "Ferrari"
orderby r.LastName
select r.FirstName + " " + r.LastName;
foreach (var item in ferrariDrivers)
{
Console.WriteLine(item);
}
var ferrariDrivers2 = Formulal.GetChampions()
.SelectMany(r => r.Cars, (r, c) => new { Racer = r, Car = c })
.Where(r => r.Car == "Ferrari")
.OrderBy(r => r.Racer.LastName)
.Select(r => r.Racer.FirstName + " " + r.Racer.LastName);
foreach (var item in ferrariDrivers2)
{
Console.WriteLine(item);
}
}
/// <summary>
/// 分区操作
/// </summary>
public void TakeDemo()
{
int pageSize = 5;
int numberPages = (int)Math.Ceiling(Formulal.GetChampions().Count() / (double)pageSize);
for (int page = 0; page < numberPages; page++)
{
Console.WriteLine("Page " + page);
var racers = (from r in Formulal.GetChampions()
orderby r.LastName, r.FirstName
select r.FirstName + " " + r.LastName)
.Skip(page * pageSize).Take(pageSize);
foreach (var name in racers)
{
Console.WriteLine(name);
}
Console.WriteLine();
}
}
/// <summary>
/// 筛选
/// </summary>
public void WhereDemo()
{
//1
var racers0 = from r in Formulal.GetChampions()
where r.Wins > 15 && (r.Country == "China" || r.Country == "UK")
select r;
foreach (var r in racers0)
{
Console.WriteLine(r.ToString());
}
var racers0_1 = Formulal.GetChampions()
.Where(r => r.Wins > 15 && (r.Country == "China" || r.Country == "UK"))
.Select(r => r);
foreach (var r in racers0_1)
{
Console.WriteLine(r.ToString());
}
//2 这种情况只能用 扩展函数
//var racers1 = from r in Formulal.GetChampions()
// where r.LastName.StartsWith("A") &&
// select r;
//foreach (var r in racers1)
//{
// Console.WriteLine(r.ToString());
//}
//index 是索引（这是扩展方法的重载）
var racers1_1 = Formulal.GetChampions()
.Where((r, index) => r.LastName.StartsWith("A") && index % 2 != 0);
foreach (var r in racers1_1)
{
Console.WriteLine(r.ToString());
}
}
/// <summary>
/// 排序
/// </summary>
public void OrderByDemo()
{
var racers = (from r in Formulal.GetChampions()
orderby r.Country, r.LastName, r.FirstName ascending
select r).Take(10);
foreach (var item in racers)
{
Console.WriteLine(item.Country + "\t" + item.LastName + "\t" + item.FirstName);
}
var racers2 = Formulal.GetChampions()
.OrderBy(r => r.Country)
.ThenBy(r => r.LastName)
.ThenByDescending(r => r.FirstName)
.Take(10);
foreach (var item in racers2)
{
Console.WriteLine(item.Country + "\t" + item.LastName + "\t" + item.FirstName);
}
}
/// <summary>
/// 集合操作
/// Intersect()：通过使用的默认相等比较器对值进行比较，生成两个序列的交集。
/// Distinct()：返回序列中通过使用指定的非重复元素。
/// Union()：通过使用默认的相等比较器生成的两个序列的并集。
/// Except()：通过使用默认的相等比较器对值进行比较，生成两个序列的差集。
/// </summary>
public void AggregateDemo()
{
Func <string, IEnumerable <Racer> > racersByCar =
car => from r in Formulal.GetChampions()
from c in r.Cars
where c == car
orderby r.LastName
select r;
Console.WriteLine("调用Intersect()方法，显示结果：");
foreach (var racer in racersByCar("Ferrari").Intersect(racersByCar("Lotus")))
{
Console.WriteLine(racer);
}
var racernames = from r in Formulal.GetChampions()
where r.Country == "Italy"
orderby r.Wins descending
select new
{
Name = r.FirstName + " " + r.LastName
};
var racerNamesAndStarts = from r in Formulal.GetChampions()
where r.Country == "Italy"
orderby r.Wins descending
select new
{
LastName = r.LastName,
Starts = r.Starts
};
//第一个集合中的第一项会与第二个集合中的第一项合并
//第一个集合中的第二项会与第二个集合中的第二项合并，依次类推
//如果两个序列的项数不同，Zip()方法就在到达较小集合的末尾时停止
var racers = racernames.Zip(racerNamesAndStarts
, (first, second) => first.Name + ", starts: " + second.Starts);
}
/// <summary>
/// 聚合操作
/// </summary>
public void Polymerize()
{
Console.WriteLine("Count():");
var query = from r in Formulal.GetChampions()
let numberYears = r.Years.Count()
where numberYears >= 3
orderby numberYears descending, r.LastName
select new
{
Name = r.FirstName + " " + r.LastName,
TimesChampion = numberYears
};
foreach (var r in query)
{
Console.WriteLine(r.Name + " " + r.TimesChampion);
}
Console.WriteLine();
Console.WriteLine("Sum():");
var countries = (from c in from r in Formulal.GetChampions()
group r by r.Country into c
select new
{
Country = c.Key,
Wins = (from r1 in c select r1.Wins).Sum()
}
orderby c.Wins descending, c.Country
select c).Take(5);
foreach (var country in countries)
{
Console.WriteLine(country.Country + " " + country.Wins);
}
}
/// <summary>
/// 分组联接
/// 将第一个表中的元素与第二个表中的一个或多个匹配元素相关联，如果没有匹配上，填充null
/// </summary>
public void IntoJoinDemo()
{
var racers = Formulal.GetChampionships()
.SelectMany(cs => new List <RacerInfo>()
{
new RacerInfo
{
Year = cs.Year,
Positon = 1,
FirstName = cs.First.Split(' ')[0],
LastName = cs.First.Split(' ')[1]
},
new RacerInfo
{
Year = cs.Year,
Positon = 2,
FirstName = cs.Second.Split(' ')[0],
LastName = cs.Second.Split(' ')[1]
},
new RacerInfo
{
Year = cs.Year,
Positon = 3,
FirstName = cs.Third.Split(' ')[0],
LastName = cs.Third.Split(' ')[1]
}
});
foreach (var r in racers)
{
Console.WriteLine(r.FirstName + " " + r.LastName);
}
var q = (from r in Formulal.GetChampions()
join r2 in racers on
new
{
FirstName = r.FirstName,
LastName = r.LastName
}
equals
new
{
r2.FirstName,
r2.LastName
}
into yearResults
select new
{
r.FirstName,
r.LastName,
r.Wins,
r.Starts,
Results = yearResults
});
foreach (var r in q)
{
Console.WriteLine(r.FirstName + " " + r.LastName);
foreach (var results in r.Results)
{
Console.WriteLine(results.Year + " " + results.Positon);
}
}
}
/// <summary>
/// 内联接
/// 精确返回同时匹配到左右两张表的数据
/// </summary>
public void InnerJoinDemo()
{
//分布查询
var racers = from r in Formulal.GetChampions()
from y in r.Years
select new
{
Year = y,
Name = r.FirstName + " " + r.LastName
};
var teams = from t in Formulal.GetContructorChampions()
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
{
r.Year,
Champion = r.Name,
Constructor = t.Name
}).Take(10);
Console.WriteLine("输出结果：");
foreach (var item in racersAndTeams)
{
Console.WriteLine($"{item.Year}:{item.Champion,-20} {item.Constructor}");
}
//一步查询
var racersAndTeams2 =
(from r in from r1 in Formulal.GetChampions()
from yr in r1.Years
select new
{
Year = yr,
Name = r1.FirstName + " " + r1.LastName
}
join t in
from t1 in Formulal.GetContructorChampions()
from yt in t1.Years
select new
{
Year = yt,
Name = t1.Name
}
on r.Year equals t.Year
orderby t.Year
select new
{
Year = r.Year,
Champion = r.Name,
Constructor = t.Name
}
).Take(10);
Console.WriteLine("输出结果：");
foreach (var item in racersAndTeams2)
{
Console.WriteLine($"{item.Year}:{item.Champion,-20} {item.Constructor}");
}
}