//Insert Province
public static void InsertProvince()
{
var province = new Province {
ProvinceName = "浙江省"
};
province.Donators.Add(new Donator
{
Name = "星空夜焰",
Amount = 50m,
DonateDate = DateTime.Parse("2016-7-12")
});
province.Donators.Add(new Donator
{
Name = "伟涛",
Amount = 30m,
DonateDate = DateTime.Parse("2016-7-12")
});
using (var db = new DonatorsContext())
{
db.Provinces.Add(province);
db.SaveChanges();
}
Console.WriteLine("新建对象，然后添加到数据库上下文，这个表明了EF会追踪当时上下文中为attached或者added状态的实体");
}
//查询方法， 多表连接
public static void 查询语法多表连接()
{
using (var db = new DonatorsContext())
{
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
};
FluentConsole.Green.Line("省份\t打赏者");
foreach (var donator in join1)
{
foreach (var subdonator in donator.DonatorList)
{
Console.WriteLine("{0}\t{1}", donator.ProvinceName, subdonator.Name);
}
}
}
Console.WriteLine("查询语法多表连接！");
}
public static void 消除EF追踪()
{
using (var db = new DonatorsContext())
{
Console.WriteLine("默认情况下，实体的状态");
var provinceNormal = db.Provinces.Include(p => p.Donators);
foreach (var province in provinceNormal)
{
FluentConsole.Green.Line("省份追踪状态：{0}", db.Entry(province).State);
foreach (var donator in province.Donators)
{
Console.WriteLine("打赏者的追踪状态：{0}", db.Entry(donator).State);
}
}
Console.WriteLine("使用AsNoTracking()方法后，实体的状态");
var provinceNoTracking = db.Provinces.Include(p => p.Donators).AsNoTracking();
//使用AsNoTracking()方法设置不再追踪实体
foreach (var province in provinceNoTracking)
{
FluentConsole.Green.Line("省份追踪状态：{0}", db.Entry(province).State);
foreach (var donator in province.Donators)
{
Console.WriteLine("打赏者的追踪状态：{0}", db.Entry(donator).State);
}
}
}
Console.WriteLine("EF中实体的状态比较。");
}
//查询语法，分组Group
public static void 查询语法分组Group()
{
using (var db = new DonatorsContext())
{
//代码会根据省份名称进行分组，最终以匿名对象的投影返回，结果中的ProvinceName就是分组时用到的字段，Donators属性包含了通过ProvinceName找到的Donators集合
var donatorsWithProvince = from donator in db.Donators
group donator by donator.Province.ProvinceName
into donatorGroup
select new
{
ProvinceName = donatorGroup.Key,
Donators = donatorGroup
};
foreach (var dwp in donatorsWithProvince)
{
FluentConsole.Green.Line("{0}的打赏者如下：", dwp.ProvinceName);
foreach (var d in dwp.Donators)
{
Console.WriteLine("{0}\t{1}", d.Name, d.Amount);
}
}
}
Console.WriteLine("查询语法：分组Group！");
}
//桌面应用,更新Dontor
public static void 桌面应用UpdateDonator()
{
using (var db = new DonatorsContext())
{
var donator = db.Donators.Find(4);
donator.Name = "醉千秋"; //我想把“醉、千秋”中的顿号去掉
db.SaveChanges();
}
Console.WriteLine("桌面应用更新Donators， ");
}
//其他聚合操作
public static void 其他聚合操作()
{
using (var db = new DonatorsContext())
{
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); //打赏金额的平均值
FluentConsole.Green.Line("Sum={0},Min={1},Average={2},Max={3}", sum, min, average, max);
}
}
//方法语法， 分页PagingSkip
public static void 分页Paging之Skip()
{
using (var db = new DonatorsContext())
{
var donatorsBefore = db.Donators;
var donatorsAfter = db.Donators.OrderBy(d => d.Id).Skip(2);
Console.WriteLine("原始数据打印结果：");
PrintDonators(donatorsBefore);
Console.WriteLine("Skip（2）之后的结果");
PrintDonators(donatorsAfter);
}
Console.WriteLine("分页PagingSkip，操作完毕！");
}
//方法语法， 利用导航属性，得到匹配某个Donator的所有Province
public static void 方法语法导航属性根据某个Donator得到Province()
{
using (var db = new DonatorsContext())
{
var provinces = db.Donators.Where(d => d.Name == "雪茄").Select(d => d.Province);
FluentConsole.Green.Line("Id\t省份");
foreach (var province in provinces)
{
Console.WriteLine("{0}\t{1}", province.Id, province.ProvinceName);
}
}
Console.WriteLine("方法语法导航属性,利用Donator中的导航属性，找到符合某个Donator的省份！");
}
//方法语法，分页PagingTake
public static void 分页Paging之Take()
{
using (var db = new DonatorsContext())
{
var donatorsBefore = db.Donators;
var donatorsAfter = db.Donators.OrderBy(d => d.Amount).Take(2);
Console.WriteLine("原始数据打印结果：");
PrintDonators(donatorsBefore);
Console.WriteLine("Take(3)之后的结果：");
PrintDonators(donatorsAfter);
}
Console.WriteLine("方法语法，分页PagingTake！");
}
//聚合操作，求和
public static void 聚合操作求和()
{
using (var db = new DonatorsContext())
{
//方法语法更简洁，而且查询语法还要讲前面的LINQ SQL用括号括起来才能进行聚合（其实这是混合语法），没有方法语法简洁
var count = (from donator in db.Donators
where donator.Province.ProvinceName == "山东省"
select donator).Count();
var count1 = db.Donators.Count(d => d.Province.ProvinceName == "山东省");
FluentConsole.Green.Line("查询语法Count={0},方法语法Count={1}", count, count1);
}
}
//常规删除数据
public static void 常规删除数据()
{
using (var db = new DonatorsContext())
{
PrintAllDonators(db);
FluentConsole.Green.Line("删除后的数据如下：");
var toDelete = db.Provinces.Find(2);
toDelete.Donators.ToList().ForEach(d => db.Donators.Remove(d));
db.Provinces.Remove(toDelete);
db.SaveChanges();
PrintAllDonators(db);
}
}
public static void PrintAllDonators(DonatorsContext db)
{
var provinces = db.Provinces.ToList();
foreach (var province in provinces)
{
FluentConsole.Green.Line("{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());
}
}
}
//方法语法，利用导航属性，得到匹配某个Province的所有Donators
public static void 方法语法导航属性根据某个province得到所有的Donator()
{
using (var db = new DonatorsContext())
{
var donators = db.Provinces.Where(p => p.ProvinceName == "山东省").SelectMany(p => p.Donators);
FluentConsole.Green.Line("Id\t姓名\t金额\t打赏时间");
foreach (var donator in donators)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", donator.Id, donator.Name, donator.Amount,
donator.DonateDate.ToShortDateString());
}
}
Console.WriteLine("方法语法导航属性,利用Province中的导航属性，找到符合某个Province的所有打赏者！");
}
//方法语法检索捐赠者
public static void 方法语法Retrieve()
{
using (var db = new DonatorsContext())
{
var donators = db.Donators.Where(d => d.Amount == 50m);
FluentConsole.Green.Line("Id\t姓名\t金额\t打赏时间");
foreach (var donator in donators)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", donator.Id, donator.Name, donator.Amount,
donator.DonateDate.ToShortDateString());
}
}
Console.WriteLine("查询语法检索完成！");
}
public static void 使用内存数据()
{
using (var db = new DonatorsContext())
{
var provinces = db.Provinces.ToList();
//var query = db.Provinces.Find(3);//Find方法首先会去查询内存中的数据。
//ChangeTracker的使用
foreach (var dbEntityEntry in db.ChangeTracker.Entries <Province>())
{
Console.WriteLine(dbEntityEntry.State);
Console.WriteLine(dbEntityEntry.Entity.ProvinceName);
}
}
}
//方法语法，降序排序
public static void 方法语法降序排序()
{
using (var db = new DonatorsContext())
{
var donators = db.Donators.OrderByDescending(d => d.Amount);
FluentConsole.Green.Line("Id\t姓名\t金额\t打赏时间");
foreach (var donator in donators)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", donator.Id, donator.Name, donator.Amount,
donator.DonateDate.ToShortDateString());
}
}
Console.WriteLine("方法语法降序排序！");
}
//查询语法， 利用导航属性，得到匹配某个Donator的所有Province
public static void 查询语法导航属性根据某个Donator得到Province()
{
using (var db = new DonatorsContext())
{
var provinces = from donator in db.Donators
where donator.Name == "雪茄"
select donator.Province;
FluentConsole.Green.Line("Id\t省份");
foreach (var province in provinces)
{
Console.WriteLine("{0}\t{1}", province.Id, province.ProvinceName);
}
}
Console.WriteLine("查询语法导航属性,利用Donator中的导航属性，找到符合某个Donator的省份！");
}
//查询语法，过滤数据
public static void 查询语法过滤数据()
{
using (var db = new DonatorsContext())
{
var donators = from donator in db.Donators
where donator.Amount > 10 && donator.Amount < 20
select donator;
FluentConsole.Green.Line("Id\t姓名\t金额\t打赏时间");
foreach (var donator in donators)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", donator.Id, donator.Name, donator.Amount,
donator.DonateDate.ToShortDateString());
}
}
Console.WriteLine("查询语法过滤数据！");
}
public static void EF追踪与不追踪性能比较()
{
using (var db = new DonatorsContext())
{
Console.WriteLine("循环10000次，正常查询开始");
Stopwatch sw = Stopwatch.StartNew();
for (int i = 0; i < 100000; i++)
{
var provinceNormal = db.Provinces.Include(p => p.Donators);
//foreach (var province in provinceNormal)
//{
// FluentConsole.Green.Line("省份追踪状态：{0}", db.Entry(province).State);
// foreach (var donator in province.Donators)
// {
// Console.WriteLine("打赏者的追踪状态：{0}", db.Entry(donator).State);
// }
//}
}
FluentConsole.Green.Line("循环10000次，正常查询结束，消耗时间:{0}", sw.ElapsedMilliseconds);
Console.WriteLine("循环10000次，NoTracking查询开始：");
sw.Restart();
for (int i = 0; i < 100000; i++)
{
var provinceNoTracking = db.Provinces.Include(p => p.Donators).AsNoTracking();
//使用AsNoTracking()方法设置不再追踪实体
//foreach (var province in provinceNoTracking)
//{
// FluentConsole.Green.Line("省份追踪状态：{0}", db.Entry(province).State);
// foreach (var donator in province.Donators)
// {
// Console.WriteLine("打赏者的追踪状态：{0}", db.Entry(donator).State);
// }
//}
}
sw.Stop();
FluentConsole.Green.Line("循环10000次，NoTracking查询结束，消耗时间:{0}", sw.ElapsedMilliseconds);
}
}
//添加捐赠者
public static void AddDonator()
{
using (var context = new DonatorsContext())
{
var donator = new Donator
{
Name = "小黄",
Amount = 12m,
DonateDate = DateTime.MaxValue,
Province = new Province
{
ProvinceName = "新疆"
}
};
context.Donators.Add(donator);
context.SaveChanges();
}
}
//查询语法降序排序
public static void 查询语法降序排序()
{
using (var db = new DonatorsContext())
{
var donators = from donator in db.Donators
orderby donator.Amount descending
select donator;
FluentConsole.Green.Line("Id\t姓名\t金额\t打赏时间");
foreach (var donator in donators)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", donator.Id, donator.Name, donator.Amount,
donator.DonateDate.ToShortDateString());
}
}
Console.WriteLine("查询语法降序排序！");
}
//查询语法， 利用导航属性，得到匹配某个Province的所有Donators
public static void 查询语法导航属性根据某个Province得到所有的Donator()
{
using (var db = new DonatorsContext())
{
var donators = from province in db.Provinces
where province.ProvinceName == "山东省"
from donator in province.Donators
select donator;
FluentConsole.Green.Line("Id\t姓名\t金额\t打赏时间");
foreach (var donator in donators)
{
Console.WriteLine("{0}\t{1}\t{2}\t{3}", donator.Id, donator.Name, donator.Amount,
donator.DonateDate.ToShortDateString());
}
}
Console.WriteLine("查询语法导航属性,利用Province中的导航属性，找到符合某个Province的所有打赏者！");
}
public static void Web应用另一种更新操作()
{
var donator = new Donator
{
Id = 5,
Name = "雪茄",
Amount = 12m,
DonateDate = DateTime.Parse("2016-7-13")
};
using (var db = new DonatorsContext())
{
db.Donators.Attach(donator);
//db.Entry(donator).State=EntityState.Modified;//这句可以作为第二种方法替换上面一句代码
donator.Name = "秦皇岛-雪茄";
db.SaveChanges();
}
Console.WriteLine("Web应用另一种更新操作完成！");
}
//方法语法，LINQ投影
public static void 方法语法LINQ投影()
{
using (var db = new DonatorsContext())
{
var donators = db.Provinces.Select(p => new DonatorsWithProvinceViewModel
{
Province = p.ProvinceName,
DonatorList = p.Donators
});
FluentConsole.Green.Line("省份\t打赏者");
foreach (var donator in donators)
{
foreach (var subdonator in donator.DonatorList)
{
Console.WriteLine("{0}\t{1}", donator.Province, subdonator.Name);
}
}
}
Console.WriteLine("方法语法：LINQ投影！");
}
//Insert Province ,修改实体的状态为added
public static void InsertProvince2()
{
var province = new Province {
ProvinceName = "广东省"
};
province.Donators.Add(new Donator
{
Name = "求余",
Amount = 30,
DonateDate = DateTime.Parse("2016-7-12")
});
using (var db = new DonatorsContext())
{
db.Entry(province).State = EntityState.Added;
db.SaveChanges();
}
Console.WriteLine("InsertProvince,修改实体的状态为added");
}
//查询语法,LINQ投影
public static void 查询语法LINQ投影()
{
using (var db = new DonatorsContext())
{
var donators = from province in db.Provinces
select new
{
Province = province.ProvinceName,
DonatorList = province.Donators
};
FluentConsole.Green.Line("省份\t打赏者");
foreach (var donator in donators)
{
foreach (var subdonator in donator.DonatorList)
{
Console.WriteLine("{0}\t{1}", donator.Province, subdonator.Name);
}
}
}
Console.WriteLine("查询语法：LINQ投影");
}
//方法语法， 分组Group
public static void 方法语法分组Group()
{
using (var db = new DonatorsContext())
{
//代码会根据省份名称进行分组，最终以匿名对象的投影返回，结果中的ProvinceName就是分组时用到的字段，Donators属性包含了通过ProvinceName找到的Donators集合
var donatorsWithProvince = db.Donators.GroupBy(d => d.Province.ProvinceName)
.Select(donatorGroup => new
{
ProvinceName = donatorGroup.Key,
Donators = donatorGroup
});
foreach (var dwp in donatorsWithProvince)
{
FluentConsole.Green.Line("{0}的打赏者如下：", dwp.ProvinceName);
foreach (var d in dwp.Donators)
{
Console.WriteLine("{0}\t{1}", d.Name, d.Amount);
}
}
}
Console.WriteLine("方法语法：分组Group！");
}
//设置实体状态删除
public static void 设置实体状态删除()
{
//通过设置实体状态删除
var toDeleteProvince = new Province {
Id = 1
};
toDeleteProvince.Donators.Add(new Donator
{
Id = 2
});
toDeleteProvince.Donators.Add(new Donator
{
Id = 3
});
toDeleteProvince.Donators.Add(new Donator
{
Id = 4
});
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);
}
}
//Web应用，更新Province
public static void Web应用UpdateProvince()
{
var province = new Province {
Id = 2, ProvinceName = "山东省更新"
};
province.Donators.Add(new Donator
{
Name = "醉、千秋", //再改回来
Id = 4,
Amount = 12m,
DonateDate = DateTime.Parse("2015-7-12")
});
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();
}
Console.WriteLine("Web应用更新Province");
}
//方法语法，多表连接
public static void 方法语法多表连接()
{
using (var db = new DonatorsContext())
{
var join = db.Provinces.GroupJoin(db.Donators, //Provinces集合要连接的Donator是集合
province => province.Id, //左表要连接的键
donator => donator.Province.Id, //右表要连接的键
(province, donatorGroup) => new //返回的结果集
{
ProvinceName = province.ProvinceName,
DonatorList = donatorGroup
});
FluentConsole.Green.Line("省份\t打赏者");
foreach (var donator in join)
{
foreach (var subdonator in donator.DonatorList)
{
Console.WriteLine("{0}\t{1}", donator.ProvinceName, subdonator.Name);
}
}
}
Console.WriteLine("方法语法多表连接！");
}