//延时加载
public class EFQueryAdvancedTest
{
public static void Show()
{
IQueryable<Llogin> sources = null;
using (CodeFirst dbContext = new CodeFirst())
{
sources = dbContext.Set<Llogin>().Where(u => u.id1 > 3);
var List = dbContext.Set<Llogin>().Where(u => u.id1 > 3);//1 这句话执行完，没有数据库查询
foreach (var user in List)// 2 迭代遍历数据才去数据库查询--在真实需要使用数据时，才去数据库查询的
{
Console.WriteLine(user.nameCH);
}
//这就是延迟查询,可以叠加多次查询条件，一次提交给数据库；可以按需获取数据；
List = List.Where(u => u.id1 < 100);
List = List.Where(u => u.DepartNumber == "01");
List = List.OrderBy(u => u.id1);
var list = List.ToList<Llogin>();//ToList() 迭代器 Count() FitstOrDefalut()
//延迟查询也要注意：a 迭代使用时，用完了关闭连接 b 脱离context作用域
}
//foreach (var item in sources) //这个时候查询，已经超出作用域。会异常
//{
// Console.WriteLine(item.nameCH);
//}
{
{
List<int> intList = new List<int>() { 123, 4354, 3, 23, 3, 4, 4, 34, 34, 3, 43, 43, 4, 34, 3 };
var list = intList.Where(i =>
{
Thread.Sleep(1000);
return i > 10;
});//没有过滤
foreach (var i in list)//才去过滤
{
Console.WriteLine(i);
}
Console.WriteLine("*********************");
//这里是延迟的，利用的是迭代器的方式，每次去迭代访问时，才去筛选一次，委托+迭代器
}
//楼上是IEnumerable类型，数据其实已经在内存里，有个迭代器的实现，用的是委托
//楼下的IQueryable类型，数据在数据库里面，这个list里面有表达式目录树---返回值类型--IQueryProvider(查询的支持工具，sqlserver语句的生成)
//其实userList只是一个包装对象，里面有表达式目录树，有结果类型，有解析工具，还有上下文，真需要数据的时候才去解析sql，执行sql，拿到数据的---因为表达式目录树可以拼装；
{
using (CodeFirst dbContext = new CodeFirst())
{
var list = dbContext.Set<Llogin>().Where(u => u.id1 > 10);
foreach (var item in list)
{
Console.WriteLine(item.nameCH);
}
}
}
}
}
}
/// 事务：1 SaveChange
/// 2 TransactionScope完成一个context的多次SaveChange
/// 3 TransactionScope完成不同context实例的事务
/// 4 通过context.Datebase.BeginTranscation
public class ContextLifetimeTest
{
public static void Show()
{
#region 多个数据修改，一次SaveChange,开始事务保存
using (CodeFirst dbContext = new CodeFirst())
{
Llogin llogin = new Llogin()
{
id1 = 8,
name1 = "zhangsan",
nameCH = "张三",
psw = "/7k8sVej5hE=",
qx = "系统管理员",
department = "01",
TPFW = "0",
DepartNumber = "01"
};
Llogin Llogin3 = dbContext.L_login.FirstOrDefault(u => u.id1 == 3);
Llogin3.nameCH += "aaa";
Llogin Llogin4 = dbContext.L_login.FirstOrDefault(u => u.id1 == 4);
Llogin4.nameCH += "bbb";
//Llogin Llogin8 = dbContext.Set<Llogin>().Find(8);
//dbContext.L_login.Remove(Llogin8);
dbContext.SaveChanges();
}
#endregion
#region 多个数据操作一次savechange，任何一个失败直接全部失败
using (CodeFirst dbContext = new CodeFirst())
{
Llogin llogin = new Llogin()
{
id1 = 8,
name1 = "zhangsan",
nameCH = "张三",
psw = "/7k8sVej5hE=",
qx = "系统管理员",
department = "01",
TPFW = "0",
DepartNumber = "01"
};
Llogin Llogin3 = dbContext.L_login.FirstOrDefault(u => u.id1 == 3);
Llogin3.nameCH += "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"; //超过数据库字段长度
Llogin Llogin4 = dbContext.L_login.FirstOrDefault(u => u.id1 == 4);
Llogin4.nameCH += "bbb";
dbContext.SaveChanges();
}
#endregion
#region TransactionScope开启事务
//using (CodeFirst dbContext = new CodeFirst())
//{
// using (TransactionScope trans = new TransactionScope())
// {
// Llogin llogin = new Llogin()
// {
// id1 = 8,
// name1 = "zhangsan",
// nameCH = "张三",
// psw = "/7k8sVej5hE=",
// qx = "系统管理员",
// department = "01",
// TPFW = "0",
// DepartNumber = "01"
// };
// dbContext.L_login.Add(llogin);
// dbContext.SaveChanges();
// Llogin Llogin3 = dbContext.L_login.FirstOrDefault(u => u.id1 == 3);
// Llogin3.nameCH += "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"; //超过数据库字段长度
// dbContext.SaveChanges();
// trans.Complete();//能执行这个，就表示成功了；
// }
//}
#endregion
#region 不同context实例 TransactionScope开启事务
//using (CodeFirst dbContext = new CodeFirst())
//using (CodeFirst dbContext1 = new CodeFirst())
//{
// using (TransactionScope trans = new TransactionScope())
// {
// Llogin llogin = new Llogin()
// {
// id1 = 8,
// name1 = "zhangsan",
// nameCH = "张三",
// psw = "/7k8sVej5hE=",
// qx = "系统管理员",
// department = "01",
// TPFW = "0",
// DepartNumber = "01"
// };
// dbContext.L_login.Add(llogin);
// dbContext.SaveChanges();
// Llogin Llogin3 = dbContext1.L_login.FirstOrDefault(u => u.id1 == 3);
// Llogin3.nameCH += "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"; //超过数据库字段长度
// dbContext1.SaveChanges();
// trans.Complete();//能执行这个，就表示成功了；
// }
//}
#endregion
}
}
//导航属性加载&增加&删除
public class NavigationTest
{
public static void ShowQuery()
{
//1 默认情况下，导航属性是延迟查询；
//条件是virtaul属性+默认配置
using (CodeFirst dbContext = new CodeFirst())
{
var departList = dbContext.Set<L_DepartMent>().Where(d => d.autoID == 1);
foreach (var item in departList)//只查Depart
{
Console.WriteLine(item.DepartMent);
foreach (var user in item.L_login)//使用再去查用户
{
Console.WriteLine(user.nameCH);
}
}
}
//2 关闭延迟加载，子表数据就没了
using (CodeFirst dbContext = new CodeFirst())
{
dbContext.Configuration.LazyLoadingEnabled = false;//关闭延迟查询
var departList = dbContext.Set<L_DepartMent>().Where(d => d.autoID == 1);
foreach (var item in departList)//只查Depart
{
Console.WriteLine(item.DepartMent);
foreach (var user in item.L_login)//这里不会再去查询
{
Console.WriteLine(user.nameCH);
}
}
}
//3 预先加载 Include 查询主表时就把子表数据一次性查出来
//其实自己join也可以的
using (CodeFirst dbContext = new CodeFirst())
{
dbContext.Configuration.LazyLoadingEnabled = false;//是否关闭无所谓
var departList = dbContext.Set<L_DepartMent>().Include("L_login").Where(d => d.autoID == 1);
foreach (var item in departList)//只查Depart
{
Console.WriteLine(item.DepartMent);
foreach (var user in item.L_login)//这里不会再去查询
{
Console.WriteLine(user.nameCH);
}
}
}
//4 关闭延迟查询后,如果需要子表数据，可以显示加载
using (CodeFirst dbContext = new CodeFirst())
{
dbContext.Configuration.LazyLoadingEnabled = false;//是否关闭无所谓
var departList = dbContext.Set<L_DepartMent>().Where(d => d.autoID == 1);
foreach (var item in departList)//只查Depart
{
Console.WriteLine(item.DepartMent);
dbContext.Entry<L_DepartMent>(item).Collection(c => c.L_login).Load();
foreach (var user in item.L_login)//这里不会再去查询
{
Console.WriteLine(user.nameCH);
}
}
}
}
public static void ShowInsert()
{
//数据插入：A表--B表(包含A的ID)--ID是自增的
#region 一次savechange，如果是主外键，可以自动使用自增id；如果不是，就用不到
{
using (CodeFirst dbContext = new CodeFirst())
{
L_AppMenu menu = new L_AppMenu()
{
MenuName = "测试",
ParentId = 0,
Icon = "icon-sys",
SquentIndex = 2,
NodeLeavel = 1,
IsShow = true,
TreeCode = "64"
};
dbContext.Set<L_AppMenu>().Add(menu);
L_User_Qx user_Qx = new L_User_Qx()
{
UserId = 1,
MenuId = menu.MenuId,
IsSearch = true,
IsSet = true
};
dbContext.Set<L_User_Qx>().Add(user_Qx);
dbContext.SaveChanges();
}
}
#endregion
}
//1 数据库设置级联删除 只需要删除主表
//2 如果没有级联删除--数据库外键设置强制外键约束为否--就可以只删除主表
public static void ShowDelete()
{
string DepartNumber = "";
{
using (CodeFirst dbContext = new CodeFirst())
{
L_DepartMent depart = new L_DepartMent()
{
DepartNumber = "05",
DepartMent = "测试123",
ParentId = "0"
};
dbContext.Set<L_DepartMent>().Add(depart);
dbContext.SaveChanges();
Llogin llogin = new Llogin()
{
name1 = "wangwu",
nameCH = "王五",
psw = "/7k8sVej5hE=",
qx = "系统管理员",
department = "01",
TPFW = "0",
DepartNumber = depart.DepartNumber
};
dbContext.Set<Llogin>().Add(llogin);
dbContext.SaveChanges();
DepartNumber = depart.DepartNumber;
}
using (CodeFirst dbContext = new CodeFirst())
{
L_DepartMent departMent = dbContext.L_DepartMent.FirstOrDefault(l => l.DepartNumber == DepartNumber);
dbContext.Set<L_DepartMent>().Remove(departMent);
dbContext.SaveChanges();
}
}
}
}
