//EntityState 四种状态
//1.Detached 实体跟上下文没有关系
//2.Detached 监听中 但是无修改
//3.Added 插入数据
//4.Modified 更新数据
//5.MDeleted 删除数据
public class EFStateTest
{
public static void Show()
{
try
{
Llogin llogin = new Llogin()
{
id1 = 8,
name1 = "zhangsan",
nameCH = "张三",
psw = "/7k8sVej5hE=",
qx = "系统管理员",
department = "01",
TPFW = "0",
DepartNumber = "01"
};
{
using (CodeFirst context = new CodeFirst())
{
llogin.nameCH = "李四";
Console.WriteLine(context.Entry<Llogin>(llogin).State); //实体跟context没关系 Detached
context.SaveChanges(); //Detached啥事儿不发生
context.Set<Llogin>().Add(llogin);
Console.WriteLine(context.Entry<Llogin>(llogin).State);//状态变为 Added
context.SaveChanges();//插入数据(自增主键在插入成功后，会自动赋值过去)
Console.WriteLine(context.Entry<Llogin>(llogin).State);// Unchanged（监听，但是没变化）
llogin.nameCH = "李四123";
Console.WriteLine(context.Entry<Llogin>(llogin).State);//状态变为 Modified
context.SaveChanges(); //更新数据库，因为状态是Modified
Console.WriteLine(context.Entry<Llogin>(llogin).State);// Unchanged（监听，但是没变化）
context.Set<Llogin>().Remove(llogin);
Console.WriteLine(context.Entry<Llogin>(llogin).State); //状态变为 MDeleted
context.SaveChanges();//删除数据，因为状态是Deleted
Console.WriteLine(context.Entry<Llogin>(llogin).State);//Detached已经从内存移除了
}
}
{
using (CodeFirst context = new CodeFirst())
{
llogin.nameCH = "李四897";
Console.WriteLine(context.Entry<Llogin>(llogin).State); //实体跟context没关系 Detached
context.Entry<Llogin>(llogin).State = EntityState.Modified;//全字段更新
Console.WriteLine(context.Entry<Llogin>(llogin).State); //状态变为 Modified
context.SaveChanges();//更新数据库，因为状态是Modified
}
}
{
using (CodeFirst context = new CodeFirst())
{
Console.WriteLine(context.Entry<Llogin>(llogin).State); //实体跟context没关系 Detached
context.Set<Llogin>().Attach(llogin); //使用Attach增加监听
Console.WriteLine(context.Entry<Llogin>(llogin).State);// Unchanged（正在监听，但是没变化）
llogin.nameCH = "李四897000"; //只更新这个字段
Console.WriteLine(context.Entry<Llogin>(llogin).State);//状态变为 Modified
context.SaveChanges();//更新数据库
}
}
{
//Find可以使用缓存，优先从内存查找(限于context)
//AsNoTracking() 返回数据不会缓存，加一个可以提升性能
using (CodeFirst context = new CodeFirst())
{
var login = context.Set<Llogin>().Where(l => l.id1 < 7).ToList();
//var login = context.Set<Llogin>().Where(l => l.id1 < 7).AsNoTracking().ToList();
var login1 = context.Set<Llogin>().Find(2); //从缓存中查找
var login2 = context.Set<Llogin>().Find(8); //缓存中没有再从数据库中查询
var login3 = context.Set<Llogin>().FirstOrDefault(l => l.id1 == 2);//从数据库查询
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
public class EFQueryTest
{
public static void Show()
{
using (CodeFirst dbContext = new CodeFirst())
{
//in查询
{
var list = dbContext.L_login.Where(u => new int[] { 1, 2, 3 }.Contains(u.id1));//in查询
foreach (var user in list)
{
Console.WriteLine(user.nameCH);
}
}
{
//没有任何差别，只有写法上的熟悉
var list = from u in dbContext.L_login
where new int[] { 1, 2, 3 }.Contains(u.id1)
select u;
foreach (var user in list)
{
Console.WriteLine(user.nameCH);
}
}
//分页
{
var list = dbContext.L_login.Where(u => new int[] { 1, 2, 3, 4, 5 }.Contains(u.id1))
.OrderBy(u => u.id1)
.Select(u => new
{
name = u.nameCH,
Pwd = u.psw
}).Skip(3).Take(5);
foreach (var user in list)
{
Console.WriteLine(user.name);
}
}
{
var list = (from u in dbContext.L_login
where new int[] { 1, 2, 3, 4, 5 }.Contains(u.id1)
orderby u.id1
select new
{
name = u.nameCH,
Pwd = u.psw
}).Skip(2).Take(3);
foreach (var user in list)
{
Console.WriteLine(user.name);
}
}
//模糊查询
{
var list = dbContext.L_login.Where(u => u.nameCH.StartsWith("孔") && u.nameCH.EndsWith("颖"))
.Where(u => u.nameCH.EndsWith("颖"))
.Where(u => u.nameCH.Contains("孔新颖"))
.Where(u => u.nameCH.Length < 5)
.OrderBy(u => u.id1);
foreach (var user in list)
{
Console.WriteLine(user.nameCH);
}
}
//join
{
var list = (from a in dbContext.L_login
join b in dbContext.L_DepartMent on a.DepartNumber equals b.DepartNumber
where new int[] { 1, 2, 3, 4, 6, 7, 8 }.Contains(a.id1)
select new
{
name = a.nameCH,
department = b.DepartMent,
id = a.id1
}).OrderBy(u => u.id);
foreach (var item in list)
{
Console.WriteLine("{0} {1}", item.name, item.department);
}
}
{
var list = from a in dbContext.L_login
join b in dbContext.L_DepartMent on a.DepartNumber equals b.DepartNumber
into abList
from ab in abList.DefaultIfEmpty()
where new int[] { 1, 2, 3, 4, 6, 7, 8 }.Contains(a.id1)
select new
{
name = a.nameCH,
department = ab.DepartMent,
pws = a.psw
};
foreach (var item in list)
{
Console.WriteLine("{0} {1} {2}", item.name, item.department, item.pws);
}
}
}
//sql语句查询
using (CodeFirst dbContext = new CodeFirst())
{
{
DbContextTransaction trans = null;
try
{
trans = dbContext.Database.BeginTransaction();
string sql = "Update [L_login] Set Name='张三' WHERE Id=@Id";
SqlParameter parameter = new SqlParameter("@Id", 3);
dbContext.Database.ExecuteSqlCommand(sql, parameter);
trans.Commit();
}
catch (Exception ex)
{
if (trans != null)
trans.Rollback();
throw ex;
}
finally
{
trans.Dispose();
}
}
{
DbContextTransaction trans = null;
try
{
trans = dbContext.Database.BeginTransaction();
string sql = "SELECT * FROM [L_login] WHERE Id=@Id";
SqlParameter parameter = new SqlParameter("@Id", 3);
List<Llogin> userList = dbContext.Database.SqlQuery<Llogin>(sql, parameter).ToList<Llogin>();
trans.Commit();
}
catch (Exception ex)
{
if (trans != null)
trans.Rollback();
throw ex;
}
finally
{
trans.Dispose();
}
}
}
}
}
