//输出所有的打赏者
private static void PrintAllDonators(DonatorsContext db)
{
var provinces = db.Provinces.ToList();
foreach (var province in provinces)
{
Console.WriteLine("{0}的打赏者如下：", province.ProvinceName);
foreach (var donator in province.Donators)
{
Console.WriteLine("{0,-10}\t{1,-10}\t{2,-10}\t{3,-10}", donator.Id, donator.Name, donator.Amount,
donator.DonateDate.ToShortDateString());
}
}
}
//输出所有的打赏者
private static void PrintAllDonators(DonatorsContext db)
{
// “懒加载”---> “预加载”
var provinces = db.Provinces.Include(d => d.Donators); // % 此处不能用默认“懒加载”，因为不加载数据，没法删除
// % 此处用Include“预加载”Donators才能进行删除操作
foreach (var province in provinces)
{
Console.WriteLine("{0}:{1}的打赏者如下：", province.Id, province.ProvinceName);
foreach (var don in province.Donators)
{
Console.WriteLine("{0,-10}\t{1,-10}\t{2,-10}\t{3,-10}", don.Id, don.Name, don.Amount,
don.DonateDate.ToShortDateString());
}
}
}
static void Main(string[] args)
{
// 查询数据
using (var db = new DonatorsContext())
{
//IEnumerable<Donator> donators;
IQueryable <Donator> donators;
//var donators;
IQueryable <Province> province;
#region 1.0 执行简单的查询
Console.WriteLine("1.0 执行简单的查询");
//1.查询语法
donators = from d in db.Donators
where d.Amount == 50
select d;
//2.方法语法
donators = db.Donators.Where(d => d.Amount == 50);
#endregion
#region 2.0 使用导航属性
Console.WriteLine();
Console.WriteLine("2.0 使用导航属性");
// 获取山东省打赏超过5元的所有人
//1 查询语法
donators = from pro in db.Provinces
where pro.ProvinceName == "山东省"
from d in pro.Donators
where d.Amount == 50
select d;
//2 方法语法
donators = db.Provinces.Where(pro => pro.ProvinceName == "山东省")
.SelectMany(p => p.Donators)
.Where(d => d.Amount > 5);
// 获取打赏者"雪茄"的省份
//1 查询语法
province = from don in db.Donators
where don.Name == "雪茄"
select don.Province;
//2 方法语法
province = db.Donators.Where(don => don.Name == "雪茄").Select(p => p.Province);
Console.WriteLine("Id\t姓名\t金额\t打赏日期");
foreach (var donator in donators)
{
Console.WriteLine("{0}\t{1,-10}\t{2}\t{3}", donator.Id, donator.Name, donator.Amount, donator.DonateDate.ToShortDateString());
}
Console.WriteLine("\nId\t省份名称");
foreach (var pro in province)
{
Console.WriteLine("{0}\t{1}", pro.Id, pro.ProvinceName);
}
#endregion
#region 4.0 LINQ投影:查出“所有省的所有打赏者”
Console.WriteLine();
Console.WriteLine("4.0 LINQ投影:查出“所有省的所有打赏者”");
//1 查询语法
var donatorsPro = from pro in db.Provinces
select new
{
ProV = pro.ProvinceName,
DonatorList = pro.Donators
};
//2 方法语法
donatorsPro = db.Provinces.Select(p => new
{
ProV = p.ProvinceName,
DonatorList = p.Donators
});
//3 返回一个对象的方法语法: 直接利用 ViewModel类
var donatorsPro1 = db.Provinces.Select(p => new DonatorsProvinceViewModel
{
ProV = p.ProvinceName,
DonatorList = p.Donators
});
//4 返回一个对象的查询语法: 直接利用 ViewModel类
donatorsPro1 = from pro in db.Provinces
select new DonatorsProvinceViewModel
{
ProV = pro.ProvinceName,
DonatorList = pro.Donators
};
Console.WriteLine("\n省份\t打赏者");
foreach (var donator in donatorsPro1)
{
foreach (var donator1 in donator.DonatorList)
{
Console.WriteLine("{0}\t{1}", donator.ProV, donator1.Name);
}
}
#endregion
#region 5.0 分组Group
Console.WriteLine();
Console.WriteLine("5.0 分组Group");
//1 查询语法
var donatorsWithProvince = from donator in db.Donators
group donator by donator.Province.ProvinceName
into donatorGroup
select new
{
ProvinceName = donatorGroup.Key,
Donators = donatorGroup
};
//2 方法语法
donatorsWithProvince = db.Donators.GroupBy(d => d.Province.ProvinceName)
.Select(donatorGroup => new
{
ProvinceName = donatorGroup.Key,
Donators = donatorGroup
});
foreach (var dwp in donatorsWithProvince)
{
Console.WriteLine("{0}的打赏者如下：", dwp.ProvinceName);
foreach (var d in dwp.Donators)
{
Console.WriteLine("{0,-10}\t\t{1}", d.Name, d.Amount);
}
}
#endregion
#region 6.0 排序Ordering
Console.WriteLine();
Console.WriteLine("6.0 排序Ordering");
//1 升序查询语法
donators = from donator in db.Donators
orderby donator.Amount ascending //ascending可省略
select donator;
//2 升序方法语法
donators = db.Donators.OrderBy(d => d.Amount);
//#endregion
//1 降序查询语法
donators = from donator in db.Donators
orderby donator.Amount descending
select donator;
//2 降序方法语法
donators = db.Donators.OrderByDescending(d => d.Amount);
Console.WriteLine("Id\t姓名\t\t金额\t打赏日期");
foreach (var donator in donators)
{
Console.WriteLine("{0}\t{1,-10}\t{2}\t{3}", donator.Id, donator.Name, donator.Amount, donator.DonateDate.ToShortDateString());
}
#endregion
#region 7.0 聚合操作
//Console.WriteLine();
//Console.WriteLine("7.0 聚合操作: Count, Sum, Min, Max, Average");
//// 查询山东省的捐献认识
//var count = (from donator in db.Donators
// where donator.Province.ProvinceName == "山东省"
// select donator).Count();
//var count2 = db.Donators.Count(d => d.Province.ProvinceName == "山东省");
//Console.WriteLine("查询语法Count={0}，方法语法Count={1}", count, count2);
//// 查询山东省的捐献总金额
//var sum1 = (from don in db.Donators
// where don.Province.ProvinceName == "山东省"
// select don.Amount).Sum();
//var sum2 = db.Donators.Where(don => don.Province.ProvinceName == "山东省").Sum(a => a.Amount);
//Console.WriteLine("查询语法：山东捐献总金额={0}\n方法语法：山东捐献总金额={1}", sum1, sum2);
#endregion
#region 8.0 分页Paging: Skip, Take
Console.WriteLine();
Console.WriteLine("8.0 分页Paging: Skip");
var donatorsBefore = db.Donators;
var donatorsAfter = db.Donators.OrderBy(d => d.Id).Skip(2);
var donatorsAfter2 = db.Donators.OrderBy(d => d.Id).Take(3);
Console.WriteLine("原始数据打印结果：");
PrintDonators(donatorsBefore);
Console.WriteLine("Skip(2)之后的结果：");
PrintDonators(donatorsAfter);
Console.WriteLine("Take(3)之后的结果：");
PrintDonators(donatorsAfter2);
////分页实现
//while (true)
//{
// Console.WriteLine("您要看第几页数据");
// string pageStr = Console.ReadLine() ?? "1";
// int page = int.Parse(pageStr);
// const int pageSize = 3;
// if (page > 0 && page < 4)
// {
// donators = db.Donators.OrderBy(d => d.Id).Skip((page - 1) * pageSize).Take(pageSize);
// PrintDonators(donators);
// }
// else
// {
// break;
// }
//}
#endregion
#region 9.0实现多表连接
// Console.WriteLine();
// Console.WriteLine("9.0实现多表连接");
// var join1 = from p in db.Provinces
// join donator in db.Donators on p.Id equals donator.Province.Id
// into donatorList//注意，这里的donatorList是属于某个省份的所有打赏者，很多人会误解为这是两张表join之后的结果集
// select new
// {
// ProvinceName = p.ProvinceName,
// DonatorList = donatorList
// };
// var join2 = db.Provinces.GroupJoin(db.Donators,//Provinces集合要连接的Donators实体集合
// p => p.Id,//左表要连接的键
// donator => donator.Province.Id,//右表要连接的键
// (p, donatorGroup) => new//返回的结果集
//{
// ProvinceName = p.ProvinceName,
// DonatorList = donatorGroup
// }
// );
// foreach (var dwp in join1)
// {
// Console.WriteLine("{0}的打赏者如下：", dwp.ProvinceName);
// foreach (var d in dwp.DonatorList)
// {
// Console.WriteLine("{0,-10}\t\t{1}", d.Name, d.Amount);
// }
// }
#endregion
}
// 插入数据
using (var db = new DonatorsContext())
{
#region 11.0 插入数据
//Console.WriteLine();
//Console.WriteLine("11.0 插入数据");
////方法一：用Add方法
//var province = new Province { ProvinceName = "浙江省" };
//province.Donators.Add(new Donator
//{
// Name = "星空夜焰",
// Amount = 50m,
// DonateDate = DateTime.Parse("2016-5-30")
//});
//province.Donators.Add(new Donator
//{
// Name = "伟涛",
// Amount = 25m,
// DonateDate = DateTime.Parse("2016-5-25")
//});
//db.Provinces.Add(province);
//db.SaveChanges();
////方法二：直接设置对象的状态
//var province2 = new Province { ProvinceName = "广东省" };
//province2.Donators.Add(new Donator
//{
// Name = "邱宇",
// Amount = 30,
// DonateDate = DateTime.Parse("2016-04-25")
//});
//db.Entry(province2).State = EntityState.Added;
//db.SaveChanges();
#endregion
}
// 更新数据
#region 12.0 更新数据
using (var db = new DonatorsContext())
{
// // 方法一、使用Find查询修改：在写桌面应用的话，可以使用Find方法先找到实体，再修改，最后提交，这是没问题的
// Console.WriteLine();
// Console.WriteLine("12.0 更新数据");
// var donator = db.Donators.Find(3);
// donator.Name = "醉千秋";//我想把“醉、千秋”中的顿号去掉
// db.SaveChanges();
// Console.WriteLine("Id\t姓名\t\t捐赠数\t捐赠日期");
// foreach(var d in db.Donators)
// {
// Console.WriteLine("{0}\t{1,-10}\t{2}\t{3}", d.Id, d.Name, d.Amount, d.DonateDate.ToShortDateString());
// }
}
//using (var db = new DonatorsContext())
//{
// // 方法二、直接修改状态：但是在Web应用中就不行了，因为不能在两个web服务器调用之间保留原始的上下文。
// // 我们也没必要寻找一个实体两次，第一次用于展示给用户，第二次用于更新。相反，我们可以直接修改实体的状态达到目的。
// var prov = new Province { Id = 1, ProvinceName = "山东省更新" };
// prov.Donators.Add(new Donator
// {
// Name = "醉、千秋",
// Id = 3,
// Amount = 12.00m,
// DonateDate = DateTime.Parse("2016/4/13"),
// });
// db.Entry(prov).State = EntityState.Modified;
// foreach (var donator in prov.Donators)
// {
// db.Entry(donator).State = EntityState.Modified;
// }
// db.SaveChanges();
//}
#endregion
#region 13.0 删除数据
// 方法一: 用Find查询，删除
using (var db = new DonatorsContext())
{
PrintAllDonators(db);
Console.WriteLine("删除后的数据如下：");
var toDelete = db.Provinces.Find(3);
if (toDelete != null)
{
toDelete.Donators.ToList().ForEach(
d => db.Donators.Remove(d));
db.Provinces.Remove(toDelete);
db.SaveChanges();
// 上面的代码会删除每个子实体，然后再删除根实体
}
PrintAllDonators(db);
}
// 方法二：设置状态方式，删除
//var toDeleteProvince = new Province { Id = 1 };
//toDeleteProvince.Donators.Add(new Donator
//{
// Id = 1
//});
//toDeleteProvince.Donators.Add(new Donator
//{
// Id = 2
//});
//toDeleteProvince.Donators.Add(new Donator
//{
// Id = 3
//});
//using (var db = new DonatorsContext())
//{
// PrintAllDonators(db);//删除前先输出现有的数据,不能写在下面的using语句中，否则Attach方法会报错，原因我相信你已经可以思考出来了
//}
//using (var db = new DonatorsContext())
//{
// db.Provinces.Attach(toDeleteProvince);
// foreach (var donator in toDeleteProvince.Donators.ToList())
// {
// db.Entry(donator).State = EntityState.Deleted;
// }
// db.Entry(toDeleteProvince).State = EntityState.Deleted;//删除完子实体再删除父实体
// db.SaveChanges();
// Console.WriteLine("删除之后的数据如下：\r\n");
// PrintAllDonators(db);//删除后输出现有的数据
//}
#endregion
Console.WriteLine("Operation completed!");
Console.Read();
}
static void Main(string[] args)
{
Database.SetInitializer(new DropCreateDatabaseIfModelChanges <DonatorsContext>());
using (var db = new DonatorsContext())
{
#region 1.0 简单的查询演示
//1 查询语法
//var donators = from donator in db.Donators
// where donator.Amount == 50m
// select donator;
//2 方法语法
//var donators = db.Donators.Where(d => d.Amount == 50m);
#endregion
#region 2.0 使用导航属性
//1 查询语法
//var donators = from province in db.Provinces
// where province.ProvinceName == "山东省"
// from donator in province.Donators
// select donator;
//2 方法语法
//var donators = db.Provinces.Where(p => p.ProvinceName == "山东省").SelectMany(p => p.Donators);
//Console.WriteLine(donators);
//1 查询语法
//var province = from donator in db.Donators
// where donator.Name == "雪茄"
// select donator.Province;
//2 方法语法
//var province = db.Donators.Where(d => d.Name == "雪茄").Select(d => d.Province);
//Console.WriteLine(province);
#endregion
#region 3.0 过滤数据
//1 查询语法
//var donators = from donator in db.Donators
// where donator.Amount > 10 && donator.Amount < 20
// select donator;
//2 方法语法
//var donators = db.Donators.Where(d => d.Amount > 10 && d.Amount < 20);
#endregion
#region 4.0 LINQ投影
//1 查询语法
//var donators = from province in db.Provinces
// select new
// {
// Province=province.ProvinceName,
// DonatorList=province.Donators
// };
//2 方法语法
//var donators =db.Provinces.Select(p=>new
//{
// Province = p.ProvinceName,
// DonatorList = p.Donators
//});
//3 返回一个对象的方法语法
//var donators = db.Provinces.Select(p => new DonatorsWithProvinceViewModel
//{
// Province = p.ProvinceName,
// DonatorList = p.Donators
//});
//Console.WriteLine("省份\t打赏者");
//foreach (var donator in donators)
//{
// foreach (var donator1 in donator.DonatorList)
// {
// Console.WriteLine("{0}\t{1}", donator.Province,donator1.Name);
// }
//}
#endregion
#region 5.0 分组Group
//1 查询语法
//var donatorsWithProvince = from donator in db.Donators
// group donator by donator.Province.ProvinceName
// into donatorGroup
// select new
// {
// ProvinceName=donatorGroup.Key,
// Donators=donatorGroup
// };
//2 方法语法
//var donatorsWithProvince = db.Donators.GroupBy(d => d.Province.ProvinceName)
// .Select(donatorGroup => new
// {
// ProvinceName = donatorGroup.Key,
// Donators = donatorGroup
// });
//foreach (var dwp in donatorsWithProvince)
//{
// Console.WriteLine("{0}的打赏者如下：",dwp.ProvinceName);
// foreach (var d in dwp.Donators)
// {
// Console.WriteLine("{0}\t\t{1}", d.Name, d.Amount);
// }
//}
#endregion
#region 6.0 排序Ordering
//1 升序查询语法
//var donators = from donator in db.Donators
// orderby donator.Amount ascending //ascending可省略
// select donator;
//2 升序方法语法
//var donators = db.Donators.OrderBy(d => d.Amount);
//#endregion
//1 降序查询语法
//var donators = from donator in db.Donators
// orderby donator.Amount descending
// select donator;
//2 降序方法语法
//var donators = db.Donators.OrderByDescending(d => d.Amount);
#endregion
#region 7.0 聚合操作
//var count = (from donator in db.Donators
// where donator.Province.ProvinceName == "山东省"
// select donator).Count();
//var count2 = db.Donators.Count(d => d.Province.ProvinceName == "山东省");
//Console.WriteLine("查询语法Count={0}，方法语法Count={1}",count,count2);
//var sum = db.Donators.Sum(d => d.Amount);//计算所有打赏者的金额总和
//var min = db.Donators.Min(d => d.Amount);//最少的打赏金额
//var max = db.Donators.Max(d => d.Amount);//最多的打赏金额
//var average = db.Donators.Average(d => d.Amount);//打赏金额的平均值
//Console.WriteLine("Sum={0},Min={1},Max={2},Average={3}",sum,min,max,average);
#endregion
#region 8.0 分页Paging
//var donatorsBefore = db.Donators;
//var donatorsAfter = db.Donators.OrderBy(d => d.Id).Skip(2);
//Console.WriteLine("原始数据打印结果：");
//PrintDonators(donatorsBefore);
//Console.WriteLine("Skip(2)之后的结果：");
//PrintDonators(donatorsAfter);
//var take = db.Donators.Take(3);
//Console.WriteLine("Take(3)之后的结果：");
//PrintDonators(take);
//分页实现
//while (true)
//{
// Console.WriteLine("您要看第几页数据");
// string pageStr = Console.ReadLine() ?? "1";
// int page = int.Parse(pageStr);
// const int pageSize = 2;
// if (page>0&&page<4)
// {
// var donators = db.Donators.OrderBy(d => d.Id).Skip((page - 1)*pageSize).Take(pageSize);
// PrintDonators(donators);
// }
// else
// {
// break;
// }
//}
#endregion
#region 9.0实现多表连接
//var join1 = from province in db.Provinces
// join donator in db.Donators on province.Id equals donator.Province.Id
// into donatorList//注意，这里的donatorList是属于某个省份的所有打赏者，很多人会误解为这是两张表join之后的结果集
// select new
// {
// ProvinceName = province.ProvinceName,
// DonatorList = donatorList
// };
//var join2 = db.Provinces.GroupJoin(db.Donators,//Provinces集合要连接的Donators实体集合
// province => province.Id,//左表要连接的键
// donator => donator.Province.Id,//右表要连接的键
// (province, donatorGroup) => new//返回的结果集
// {
// ProvinceName = province.ProvinceName,
// DonatorList = donatorGroup
// }
// );
#endregion
#region 10.0 懒加载和预加载
//var donators = db.Donators;//还没有查询数据库
//var donatorList = donators.ToList();//已经查询了数据库，但由于懒加载的存在，还没有加载Provinces表的数据
//var province = donatorList.ElementAt(0).Province;//因为用户访问了Province表的数据，因此这时才加载
//预加载
//var donators2 = db.Donators.Include(d => d.Province).ToList();
//var donators3 = db.Donators.Include("Provinces").ToList();
#endregion
//Console.WriteLine("Id\t\t姓名\t\t金额\t\t打赏日期");
//foreach (var donator in donators)
//{
// Console.WriteLine("{0,-10}\t{1,-10}\t{2,-10}\t{3,-10}", donator.Id, donator.Name, donator.Amount, donator.DonateDate.ToShortDateString());
//}
}
#region 11.0 插入数据
//方法一
//var province = new Province { ProvinceName = "浙江省" };
//province.Donators.Add(new Donator
//{
// Name = "星空夜焰",
// Amount = 50m,
// DonateDate = DateTime.Parse("2016-5-30")
//});
//province.Donators.Add(new Donator
//{
// Name = "伟涛",
// Amount = 25m,
// DonateDate = DateTime.Parse("2016-5-25")
//});
//using (var db=new DonatorsContext())
//{
// db.Provinces.Add(province);
// db.SaveChanges();
//}
//方法二：直接设置对象的状态
//var province2 = new Province{ ProvinceName = "广东省"};
//province2.Donators.Add(new Donator
//{
// Name = "邱宇",
// Amount = 30,
// DonateDate = DateTime.Parse("2016-04-25")
//});
//using (var db=new DonatorsContext())
//{
// db.Entry(province2).State=EntityState.Added;
// db.SaveChanges();
//}
#endregion
#region 12.0 更新数据
//using (var db = new DonatorsContext())
//{
// var donator = db.Donators.Find(3);
// donator.Name = "醉千秋";//我想把“醉、千秋”中的顿号去掉
// db.SaveChanges();
//}
//var province = new Province { Id = 1, ProvinceName = "山东省" };
//province.Donators.Add(new Donator
//{
// Name = "醉、千秋",//再改回来
// Id = 3,
// Amount = 12.00m,
// DonateDate = DateTime.Parse("2016/4/13 0:00:00"),
//});
//using (var db = new DonatorsContext())
//{
// db.Entry(province).State = EntityState.Modified;
// foreach (var donator in province.Donators)
// {
// db.Entry(donator).State = EntityState.Modified;
// }
// db.SaveChanges();
//}
//using (var db=new DonatorsContext())
//{
// var provinceNormal = db.Provinces.Include(p => p.Donators);
// foreach (var p in provinceNormal)
// {
// Console.WriteLine("省份的追踪状态：{0}", db.Entry(p).State);
// foreach (var donator in p.Donators)
// {
// Console.WriteLine("打赏者的追踪状态：{0}", db.Entry(donator).State);
// }
// Console.WriteLine("**************");
// }
// var province = db.Provinces.Include(p => p.Donators).AsNoTracking();//使用AsNoTracking()方法设置不再追踪该实体
// Console.WriteLine("使用了AsNoTracking()方法之后");
// foreach (var p in province)
// {
// Console.WriteLine("省份的追踪状态：{0}", db.Entry(p).State);
// foreach (var donator in p.Donators)
// {
// Console.WriteLine("打赏者的追踪状态：{0}",db.Entry(donator).State);
// }
// Console.WriteLine("**************");
// }
//}
//var donator = new Donator
//{
// Id = 4,
// Name = "雪茄",
// Amount = 18.80m,
// DonateDate = DateTime.Parse("2016/4/15 0:00:00")
//};
//using (var db = new DonatorsContext())
//{
// db.Donators.Attach(donator);
// //db.Entry(donator).State=EntityState.Modified;//这句可以作为第二种方法替换上面一句代码
// donator.Name = "秦皇岛-雪茄";
// db.SaveChanges();
//}
#endregion
#region 13.0 删除数据
//方法1：常规删除
//using (var db = new DonatorsContext())
//{
// PrintAllDonators(db);
// Console.WriteLine("删除后的数据如下：");
// var toDelete = db.Provinces.Find(2);
// toDelete.Donators.ToList().ForEach(
// d => db.Donators.Remove(d));
// db.Provinces.Remove(toDelete);
// db.SaveChanges();
// PrintAllDonators(db);
//}
//方法2：通过设置实体状态删除
//var toDeleteProvince = new Province { Id = 1 };//id=1的省份是山东省，对应三个打赏者
//toDeleteProvince.Donators.Add(new Donator
//{
// Id = 1
//});
//toDeleteProvince.Donators.Add(new Donator
//{
// Id = 2
//});
//toDeleteProvince.Donators.Add(new Donator
//{
// Id = 3
//});
//using (var db = new DonatorsContext())
//{
// PrintAllDonators(db);//删除前先输出现有的数据,不能写在下面的using语句中，否则Attach方法会报错，原因我相信你已经可以思考出来了
//}
//using (var db = new DonatorsContext())
//{
// db.Provinces.Attach(toDeleteProvince);
// foreach (var donator in toDeleteProvince.Donators.ToList())
// {
// db.Entry(donator).State=EntityState.Deleted;
// }
// db.Entry(toDeleteProvince).State=EntityState.Deleted;//删除完子实体再删除父实体
// db.SaveChanges();
// Console.WriteLine("删除之后的数据如下：\r\n");
// PrintAllDonators(db);//删除后输出现有的数据
//}
#endregion
#region 14.0 使用内存数据
using (var db = new DonatorsContext())
{
//14.1 证明Find方法先去内存中寻找数据
var provinces = db.Provinces.ToList();
//var query = db.Provinces.Find(3);//还剩Id=3和4的两条数据了
//14.2 ChangeTracker的使用
foreach (var dbEntityEntry in db.ChangeTracker.Entries <Province>())
{
Console.WriteLine(dbEntityEntry.State);
Console.WriteLine(dbEntityEntry.Entity.ProvinceName);
}
}
#endregion
Console.WriteLine("Operation completed!");
Console.Read();
}