1 事务：一 次性保存全部的变化
using (JDDbContext context = new JDDbContext()) { context.Database.Log += c => Console.WriteLine(c); User user2 = context.Users.Find(2); user2.Name = user2.Name + "123"; User user7 = context.Users.Find(7); user7.Name = user7.Name + "123123123123"; context.SaveChanges(); }
2.1 context更新时保存全部变化，不能全局都是一个context
2.2 不同的context 也不能join的，除非都load到内存之后再去操作
2.3 context是一个数据库连接+内存对象，资源开销 一次请求，就是一个context，也不排除可能多个；
2.4更多的时候，是一个service一个context实例，多个请求 /多线程 最好是不同的context
using (JDDbContext context1 = new JDDbContext())
using (JDDbContext context2 = new JDDbContext())
{
context1.Database.Log += c => Console.WriteLine(c);
context2.Database.Log += c => Console.WriteLine(c);
var list = from u in context1.Users
join c in context2.Categories on u.CompanyId equals c.Id
into ucList
from uc in ucList.DefaultIfEmpty()
where new int[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
select new
{
Account = u.Account,
Pwd = u.Password
};
foreach (var user in list)
{
Console.WriteLine("{0} {1}", user.Account, user.Pwd);
}
}
3 本地缓存 Where查询不缓存；Find先在本地缓存查找(可能脏读，性能好处)
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine(c);
var list = context.Users.Where(u => u.Id < 5).ToList();
Console.WriteLine("*****************0*****************");
var user1 = context.Users.Find(2);
Console.WriteLine("*****************1*****************");
var user2 = context.Users.Where(u => u.Id == 2).ToList();
Console.WriteLine("*****************2*****************");
var user3 = context.Users.Find(2);
Console.WriteLine("*****************3*****************");
var user4 = context.Users.Where(u => u.Id == 2).ToList();
Console.WriteLine("*****************4*****************");
}
4 本地缓存 AsNoTracking() 表示不跟踪，不在内存做clone(增删改不行，有性能提升)
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine(c);
var list1 = context.Users.Where(u => u.Id > 5).AsNoTracking().ToList();
Console.WriteLine("*****************list1*****************");
var user1 = context.Users.Find(16);
Console.WriteLine("*****************1*****************");
var list2 = context.Users.Where(u => u.Id > 5).ToList();
Console.WriteLine("******************list2****************");
var userx = context.Users.Find(16);
Console.WriteLine("*****************1*****************");
var list3 = context.Users.Where(u => u.Id > 5).ToList();
Console.WriteLine("******************list3****************");
var list4 = context.Users.Where(u => u.Id > 5).ToList();
Console.WriteLine("*****************list4*****************");
}
5 导航属性延迟加载/预先加载/显示加载
Console.WriteLine("******************************************");
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine(c);
var companyList = context.Set<Company>().Where(c => c.Id > 0);
//var companyList = context.Set<Company>().Where(c => c.Id > 0).ToList();//.ToList()直接Company都加载过来
foreach (var company in companyList)
{
Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
}
}
//1 延迟加载 每次使用子表时再查询
//2 放弃子表查询
//3 Include一次性主从表
//4 放弃子表查询，又去显示获取某个数据
//主流选择会去导航，内置的方便；innor join也能完成
Console.WriteLine("******************************************");
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine(c);
//实体类型包含其它实体类型（POCO类）的属性（也可称为导航属性），且同时满足如下条件即可实列延迟加载，
//1.该属性的类型必需为public且不能为Sealed；
//2.属性标记为Virtual
context.Configuration.LazyLoadingEnabled = true;//默认是true 针对导航属性的
var companyList = context.Set<Company>().Where(c => c.Id > 0);
foreach (var company in companyList)
{
Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
foreach (var item in company.Users)//这个时候才去数据库查询user
{
Console.WriteLine("User name={0}", item.Name);
}
}
}
//不延迟加载,不会再次查询了
Console.WriteLine("******************************************");
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine(c);
context.Configuration.LazyLoadingEnabled = false
var companyList = context.Set<Company>().Where(c => c.Id > 0);
foreach (var company in companyList)
{
Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
foreach (var item in company.Users)//这个时候才不去数据库查询了，所以用户全是空的了
{
Console.WriteLine("User name={0}", item.Name);
}
}
}
Console.WriteLine("******************************************");
//不延迟加载，指定Include，一次性加载出来
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine(c);
//context.Configuration.LazyLoadingEnabled = false;
var companyList = context.Set<Company>().Include("Users").Where(c => c.Id > 0);
foreach (var company in companyList)
{
Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
foreach (var item in company.Users)
{
Console.WriteLine("User name={0}", item.Name);
}
}
}
//LoadProperty 手动加载 放弃子表查询，又去显示获取某个数据
using (JDDbContext context = new JDDbContext())
{
ontext.Database.Log += c => Console.WriteLine(c);
context.Configuration.LazyLoadingEnabled = false;//不延迟加载，指定Include，一次性加载出来
var companyList = context.Set<Company>().Where(c => c.Id > 0);
foreach (var company in companyList)
{
Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
context.Entry<Company>(company).Collection(c => c.Users).Load();//集合显示加载
//context.Entry<Company>(company).Reference(c => c.User).Load();//单个属性用
foreach (var item in company.Users)
{
Console.WriteLine("User name={0}", item.Name);
}
}
}
6 Linq to Sql
//In 写法
var list = from u in dbContext.Users
where new int[] { 1, 2, 3, 5, 7, 8, 9, 10, 11, 12, 14 }.Contains(u.Id)
select u;
var list = dbContext.Users.Where(u => new int[] { 1, 2, 3, 5, 7, 8, 9, 10, 11, 12, 14 }.Contains(u.Id)).OrderBy(u => u.Id)
.Select(u => new
{
Account = u.Account,
Pwd = u.Password
}).Skip(3).Take(5);
//like 写法
var list = dbContext.Users.Where(u => u.Name.StartsWith("小") && u.Name.EndsWith("新"))
.Where(u => u.Name.EndsWith("新"))
.Where(u => u.Name.Contains("小新"))
.Where(u => u.Name.Length < 5)
.OrderBy(u => u.Id);
//inner join 写法
var list = from u in dbContext.Users
join c in dbContext.Companies on u.CompanyId equals c.Id
where new int[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
select new
{
Account = u.Account,
Pwd = u.Password,
CompanyName = c.Name
};
//left join 写法
var list = from u in dbContext.Users
oin c in dbContext.Categories on u.CompanyId equals c.Id
into ucList
from uc in ucList.DefaultIfEmpty()
where new int[] { 1, 2, 3, 4, 6, 7, 10 }.Contains(u.Id)
select new
{
Account = u.Account,
Pwd = u.Password
};
//事务 写法
using (JDDbContext dbContext = new JDDbContext())
{
DbContextTransaction trans = null;
try
{
trans = dbContext.Database.BeginTransaction();
string sql = "Update [User] Set Name='小新' WHERE Id=@Id";
SqlParameter parameter = new SqlParameter("@Id", 1);
dbContext.Database.ExecuteSqlCommand(sql, parameter);
trans.Commit();
}
catch (Exception ex)
{
if (trans != null)
trans.Rollback();
throw ex;
}
finally
{
trans.Dispose();
}
}
//SQL 写法
DbContextTransaction trans = null;
try
{
trans = dbContext.Database.BeginTransaction();
string sql = "SELECT * FROM [User] WHERE Id=@Id";
SqlParameter parameter = new SqlParameter("@Id", 1);
List<User> userList = dbContext.Database.SqlQuery<User>(sql, parameter).ToList<User>();
trans.Commit();
}
catch (Exception ex)
{
if (trans != null)
trans.Rollback();
throw ex;
}
finally
{
trans.Dispose();
}
}
7 其他
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine($"sql：{c}");
context.Users.Add(userNew);
context.SaveChanges();//id自动赋值
userNew.Name += "1";
context.SaveChanges();//更新 删除都没问题
}
//context实例会跟踪数据(插入/查询) 修改过就会有个状态变化
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine($"sql：{c}");
context.Users.Attach(userNew);//直接保存无效 Attach一下,附加之后就可以监控
userNew.Name += "2";
context.SaveChanges();
}
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine($"sql：{c}");
userNew = context.Users.Find(userNew.Id);
userNew.Name += "3";
context.SaveChanges();
}
using (JDDbContext context = new JDDbContext())
{
context.Database.Log += c => Console.WriteLine($"sql：{c}");
userNew.Name += "2";
context.Users.Attach(userNew);//直接保存无效 Attach一下,附加之后就可以监控
//context.Entry<User>(userNew).State = EntityState.Modified;
//告诉context数据改了
context.Entry<User>(userNew).Property<string>("Name").IsModified = true;
//告诉context说 name属性更改了
context.SaveChanges();
}
//按需更新:DTO对象--Find---赋值--save；传递json--解析json
{
//Update A Set Name=1 Where Name like ''
//EF不支持， 要么直接sql；要么表达式目录树解析sql 然后执行sql；
}