正文
EF 保证线程内唯一 上下文的创建
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
1、ef添加完这个对象，就会自动返回这个对象数据库的内容，比如下面这个表是自增ID
最后打印出来的ID 就是自增的结果
2、lambda 中怎么select *
var userInfoList = from u in db.UserInfo
where u.ID ==
select u;
3、返回类型 IQueryable<T>
它继承 IEnumerable<T> 相当于集合
延迟加载机制 用到的时候才去数据库查
4、两种删除标记
db.UserInfo.Remove(userInfo);
db.Entry<UserInfo>(userInfo).State = System.Data.EntityState.Deleted;
5、删除的时候可以不用先查数据库
UserInfo userInfo = new UserInfo() {ID=};
//db.UserInfo.Remove(userInfo);//这样不能用remove 只能用State,Remove必须先查出来
db.Entry<UserInfo>(userInfo).State = System.Data.EntityState.Deleted;
db.SaveChanges();
6、modelfirst 生成ddl 了解dml ddl dcl区别
在生成的时候（vs2012 sqlserver2008很可能会出错）http://www.bubuko.com/infodetail-1266884.html
7、导航属性
订单属于哪个人 外键会自动添加这个人的ID
savechange 设计模式：工作单元模式 ：一个业务对多张表的操作，只连一次数据库，完成条记录的更新
Model2Container db = new Model2Container();
//人
Customer customer = new Customer() {CustomerName="zhangsan",CustomerPwd="", SubTime=DateTime.Now };
//订单
OrderInfo orderInfo1 = new OrderInfo() { ID = Guid.NewGuid(), OrderNum = "", CreateDateTime = DateTime.Now,Customer=customer };
OrderInfo orderInfo2 = new OrderInfo() { ID = Guid.NewGuid(), OrderNum = "", CreateDateTime = DateTime.Now, Customer = customer };
db.Customer.Add(customer);
db.OrderInfo.Add(orderInfo1);
db.OrderInfo.Add(orderInfo2);
db.SaveChanges();//默认的已经开启了事务。 工作单元模式。（UnitOfwork）
Orderinfor 是Customer的导航属性
Model2Container db = new Model2Container();
var customerList = from c in db.Customer
select c;
foreach (var customer in customerList)
{
Response.Write(customer.CustomerName+":"); foreach (var orderInfo in customer.OrderInfo)//延迟加载。
{
Response.Write(orderInfo.OrderNum);
}
}
不过 导航属性效率比较低，检测sqlserver发现是 查询两次
下面的写法好些
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;namespace WebApplication1
{
public partial class WebForm2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ }
//创建一个人 添加俩订单
protected void Button1_Click(object sender, EventArgs e)
{
Model2Container db = new Model2Container();
//人
Customer customer = new Customer() {CustomerName="zhangsan",CustomerPwd="", SubTime=DateTime.Now };
//订单
OrderInfo orderInfo1 = new OrderInfo() { ID = Guid.NewGuid(), OrderNum = "", CreateDateTime = DateTime.Now,Customer=customer };
OrderInfo orderInfo2 = new OrderInfo() { ID = Guid.NewGuid(), OrderNum = "", CreateDateTime = DateTime.Now, Customer = customer };
db.Customer.Add(customer);
db.OrderInfo.Add(orderInfo1);
db.OrderInfo.Add(orderInfo2);
db.SaveChanges();//默认的已经开启了事务。 工作单元模式。（UnitOfwork）
}
//打印出所有人的订单号
protected void Button2_Click(object sender, EventArgs e)
{
Model2Container db = new Model2Container();
var customerList = from c in db.Customer
select c;
foreach (var customer in customerList)
{
Response.Write(customer.CustomerName+":"); foreach (var orderInfo in customer.OrderInfo)//延迟加载。
{
Response.Write(orderInfo.OrderNum);
}
}
}
//打印出这个人的订单号
protected void Button3_Click(object sender, EventArgs e)
{
Model2Container db = new Model2Container();
//var customerInfoList = from c in db.Customer
// where c.ID == 1
// select c;
//var customerInfo = customerInfoList.FirstOrDefault();
//foreach (var orderInfo in customerInfo.OrderInfo)
//{
// Response.Write(orderInfo.OrderNum);
//} var orderInfoList = from o in db.OrderInfo
where o.CustomerID ==
select o;
foreach (var orderInfo in orderInfoList)
{
Response.Write(orderInfo.OrderNum);
} } //打印出这个订单的客户名称
protected void Button4_Click(object sender, EventArgs e)
{
Model2Container db = new Model2Container();
var orderInfoList = from o in db.OrderInfo
where o.OrderNum == ""
select o;
var orderInfo = orderInfoList.FirstOrDefault();
Customer customer = orderInfo.Customer;
Response.Write(customer.CustomerName);
} //删除某个客户下的 所有的订单
protected void Button5_Click(object sender, EventArgs e)
{
Model2Container db = new Model2Container();
//var customer = (from c in db.Customer
// where c.ID == 1
// select c).FirstOrDefault();
//var orderInfoList = customer.OrderInfo;
//while (orderInfoList.Count > 0)
//{
// var orderInfo = orderInfoList.FirstOrDefault();
// db.Entry<OrderInfo>(orderInfo).State = System.Data.EntityState.Deleted;
//}
//db.SaveChanges(); var orderList = from o in db.OrderInfo
where o.CustomerID ==
select o; } }
}
简单查询：var result = from c in Entities.Customer select c;
条件查询：
普通linq写法： var result = from c in Entities.Customer where c.Gender ==‘w’ select c;
Lambda表达式写法： var result = from c in Entities.Customer.Where<Customer>(c =>c.Gender==‘w’);
排序分页写法：
IQueryable<Customers> cust10 = (from c in customers
orderby c.CustomerID
select c).Skip().Take();
左外连接：可以的连接有Join 和 GroupJoin 方法。GroupJoin组联接等效于左外部联接，它返回第一个（左侧）数据源的每个元素（即使其他数据源中没有关联元素）。
var query = from d in edm.Order_Details
join order in edm.Orders
on d.OrderID equals order.OrderID
select new
{
OrderId = order.OrderID,
ProductId = d.ProductID,
UnitPrice = d.UnitPrice
};
8、操作方式
dbfirst 连接数据库 映射对象（edmx）
modelfirt 创建空数据模型（edmx） 添加模型 右键根据模型生成数据库
9、codefirt 根据连接字符串 自动创建数据库
vs 2012 可以直接添加一个空的模型自动添加引用 其他的版本 不可以
学生表
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace CodeFirstDemo
{
public class StudentInfo
{
[Key] //主键
public int Id { get; set; }
[StringLength()] //指定长度
[Required] //不可为空
public string StuName { get; set; }
[Required] //不可为空
public DateTime SubTime { get; set; }
//关系 一个班级可以有多个学生
public virtual ClassInfo ClassInfo { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace CodeFirstDemo
{
public class ClassInfo
{
[Key]
public int Id { get; set; }
[StringLength()]
[Required]
public string ClassName { get; set; }
[Required]
public DateTime CreateTime { get; set; }
//导航属性 一个班级有多个学生
public virtual ICollection<StudentInfo> StudentInfo { get; set; }
}
}
数据库上线文 daset 表示对这个类有curd权限
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace CodeFirstDemo
{
public class CodeFirstDbContext:DbContext
{
//connStr 连接字符串
public CodeFirstDbContext()
: base("name=connStr")
{ }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//不加这句话，自动创建的表后边都有s 如 classinfo->classsinfos
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
} public DbSet<ClassInfo> ClassInfo { get; set; }
public DbSet<StudentInfo> StudentInfo { get; set; }
}
}
主程序 有则不创建数据库 没有则创建
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace CodeFirstDemo
{
class Program
{
static void Main(string[] args)
{
CodeFirstDbContext db = new CodeFirstDbContext();
db.Database.CreateIfNotExists();//没有数据库自动创建，有则不
ClassInfo classInfo = new ClassInfo();
classInfo.ClassName = "0413班";
classInfo.CreateTime = DateTime.Now;
db.ClassInfo.Add(classInfo);
db.SaveChanges(); }
}
}
保证线程内唯一
为了保证有两个EF上下文 F1 F2都改了集合 而F1先savechange，F2再保存就会发生冲突
解决方案：单列模式 只new一次
以上解决方案错误，所有人拿到一个实例，所有人都往上下文类添加数据，越来越大，内存会爆，服务器会瘫，还不能释放，一个人释放了，其他人就废了
所以
解决方案：线程内唯一
httpContext 是通过 callContext保证线程内唯一的
其实写httpContext也是可以的
using CZBK.HeiMaOA.Model;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Runtime.Remoting.Messaging;
using System.Text;
using System.Threading.Tasks;namespace CZBK.HeiMaOA.DAL
{
public class DBContextFactory
{
/// <summary>
/// 保证在一次请求过程中只创建一次EF上下文实例.
/// </summary>
/// <returns></returns>
public static DbContext CreateDbContext()
{
DbContext dbContext = (DbContext)CallContext.GetData("dbContext");
if (dbContext == null)
{
dbContext = new OAEntities();
CallContext.SetData("dbContext", dbContext);
}
return dbContext;
}
}
}
using CZBK.HeiMaOA.Model;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace CZBK.HeiMaOA.DAL
{
public class BaseDal<T>where T:class,new()
{
// OAEntities Db = new OAEntities();
DbContext Db = DBContextFactory.CreateDbContext();//完成EF上下文创建.
/// <summary>
/// 基本查询方法
/// </summary>
/// <param name="whereLambda"></param>
/// <returns></returns>
public IQueryable<T> LoadEntities(System.Linq.Expressions.Expression<Func<T, bool>> whereLambda)
{
return Db.Set<T>().Where<T>(whereLambda);
}
/// <summary>
/// 分页方法
/// </summary>
/// <typeparam name="s">排序的约束</typeparam>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示记录数</param>
/// <param name="totalCount">总条数</param>
/// <param name="whereLambda">过滤条件</param>
/// <param name="orderbyLambda">排序条件</param>
/// <param name="isAsc">排序方式</param>
/// <returns></returns>
public IQueryable<T> LoadPageEntities<s>(int pageIndex, int pageSize, out int totalCount, System.Linq.Expressions.Expression<Func<T, bool>> whereLambda, System.Linq.Expressions.Expression<Func<T, s>> orderbyLambda, bool isAsc)
{
var temp = Db.Set<T>().Where<T>(whereLambda);
totalCount = temp.Count();
if (isAsc)//如果成立表示升序
{
temp = temp.OrderBy<T, s>(orderbyLambda).Skip<T>((pageIndex - ) * pageSize).Take<T>(pageSize);
}
else
{
temp = temp.OrderByDescending<T, s>(orderbyLambda).Skip<T>((pageIndex - ) * pageSize).Take<T>(pageSize);
}
return temp; }
/// <summary>
/// 删除数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool DeleteEntity(T entity)
{
Db.Entry<T>(entity).State = System.Data.EntityState.Deleted;
// return Db.SaveChanges() > 0;
return true;
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool UpdateEntity(T entity)
{
Db.Entry<T>(entity).State = System.Data.EntityState.Modified;
// return Db.SaveChanges() > 0;
return true;
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public T AddEntity(T entity)
{
Db.Set <T>().Add(entity);
//Db.SaveChanges();
return entity;
}
}
}
//httpContext是线程内唯一
//这样保证EF上下文线程内唯一
EFFristModelEntities db = null;
if (HttpContext.Current.Items["db"] == null)
{
db = new EFFristModelEntities();
HttpContext.Current.Items["db"] = db;
}
else
{
db = HttpContext.Current.Items["db"] as EFFristModelEntities;
}
EF 执行SQL
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace CZBK.ItcastOA.BLL
{
public partial class KeyWordsRankService:BaseService<Model.KeyWordsRank>,IBLL.IKeyWordsRankService
{
/// <summary>
/// 将统计的明细表的数据插入。
/// </summary>
/// <returns></returns>
public bool InsertKeyWordsRank()
{
string sql = "insert into KeyWordsRank(Id,KeyWords,SearchCount) select newid(),KeyWords,count(*)
from SearchDetails where DateDiff(day,SearchDetails.SearchDateTime,getdate())<=
group by SearchDetails.KeyWords";
return this.CurrentDBSession.ExecuteSql(sql)>;
}
/// <summary>
/// 删除汇总中的数据。
/// </summary>
/// <returns></returns>
public bool DeleteAllKeyWordsRank()
{
//用这句删除表中的数据是非常快的
string sql = "truncate table KeyWordsRank";
return this.CurrentDBSession.ExecuteSql(sql)>;
}
public List<string> GetSearchMsg(string term)
{
//KeyWords like term%
string sql = "select KeyWords from KeyWordsRank where KeyWords like @term";
return this.CurrentDBSession.ExecuteQuery<string>(sql, new SqlParameter("@term",term+"%" ));
}
}
}
EF 返回datatable
/// <summary>
/// EF SQL 语句返回 dataTable
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable SqlQueryForDataTatable(this Database db,
string sql,
SqlParameter[] parameters)
{ SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = db.Connection.ConnectionString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql; if (parameters.Length>)
{
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
} SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}
调用如下
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridView1.DataSource = GetDataTable();
GridView1.DataBind();
}
} public DataTable GetDataTable()
{
GardenHotelContext context = new GardenHotelContext();
int LanType = ;
int state = ;
SqlParameter[] sqlparams=new SqlParameter[];
sqlparams[]=new SqlParameter("LanType",LanType);
sqlparams[]=new SqlParameter("state",state);
DataTable DataTable = context.Database.SqlQueryForDataTatable("select LeaveName,LeaveEmail from LeaveInfo where LanType=@LanType and State=@State", sqlparams);
return DataTable; }
另一种方法
public DataTable GetDataTable2()
{
GardenHotelContext context = new GardenHotelContext(); var list = (from l in context.LeaveInfoes
group l by l.LanType into g
select new
{
g.Key,
num = g.Count()
}).ToList(); return PubClass.ListToDataTable(list); }
#region 反射List To DataTable /// <summary>
/// 将集合类转换成DataTable
/// </summary>
/// <param name="list">集合</param>
/// <returns></returns>
public static DataTable ListToDataTable(IList list)
{
DataTable result = new DataTable();
if (list.Count > )
{
PropertyInfo[] propertys = list[].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
result.Columns.Add(pi.Name, pi.PropertyType);
} for (int i = ; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
} #endregion
http://www.cnblogs.com/wlflovenet/archive/2011/12/30/EF11.html
linq 包括方法语法（lambda格式） 查询语法
帮助文档推荐尽可能使用查询语法也就是正经linq格式
from n in list
where n.StartWith("s")
orderby n //查询方法排序 按照N排序
select n;
找到集合中以S开头的
方法语法排序
var result=list.OrderBy(n=>n).where(...)
倒序：orderByDescending()
