EF 保证线程内唯一 上下文的创建

时间：2023-03-08 16:51:57

1、ef添加完这个对象，就会自动返回这个对象数据库的内容，比如下面这个表是自增ID

EF 保证线程内唯一 上下文的创建

最后打印出来的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()

相关文章