c# ASP.net EF基本操作增、删、改、查、分页，join……等ef 调用存储过程

c# ASP.net EF基本操作增、删、改、查、分页，join……等ef 调用存储过程

添加

private static void AddProduct(){ Product product = new Product() { ProductName = "测试产品1", Price = 2000, Description = "产品测试描述信息", TypeId = 2, ImageUrl = "images/xxx.jpg" }; //带事务处理机制 添加 对象关系映射 using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { taobao.Product.Add(product); if (taobao.SaveChanges() > 0) { Console.WriteLine("添加成功"); } }}

删除

private static void DeleteProduct(){ //传进来的编号,真实项目一般不会做物理删除 int productNo = 1005; using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { Product product = taobao.Product.Single (p => p.ProductNo == productNo); taobao.Product.Remove(product);//对象 if (taobao.SaveChanges() > 0)//提交持久化 commit { Console.WriteLine("删除成功"); } }}

修改

private static void ModifyProduct(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { Product product = taobao.Product.FirstOrDefault(p => p.ProductName == "P20pro"); product.Price = 2500; product.Description = "测试备注信息"; if (taobao.SaveChanges() > 0)//提交持久化 commit { Console.WriteLine("修改成功"); } }}

查询

//单条查询private static void SelectProduct(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { Product product = taobao.Product.FirstOrDefault(p => p.ProductName == "P20pro").Single(); }}//多条查询private static void SelectProductList(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { List product2 = taobao.Product.OrderByDescending(p => p.ProductNo).ToList(); }}

批量修改产品

private static void ModifyProductList(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { List productList = taobao.Product.Where(p => p.TypeId == 1).ToList(); for (int i = 0; i < productList.Count; i++) { productList[i].Price -= 5; } //foreach (var item in collection)//只读迭代只读循环,执行效率比较快 //{ //} if (taobao.SaveChanges() > 0)//提交持久化 commit { Console.WriteLine("批量修改成功"); } }}

跨表查询(表联接)  Include

private static void GetProductInclude(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { Product product = taobao.Product.Include("ProductType") .Where(p => p.ProductNo == 1002).Single(); Console.WriteLine($"{product.ProductName}-{product.ProductType.TypeName}"); }}

private static void GetProductListInclude(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { var list = taobao.Product.Include("ProductType") .Where(p => p.TypeId == 1); List productList = list.OrderBy(p => p.TypeId).ToList();//asc升序 //聚合函数：统计 求平均 求最大 求最小 求和 Console.WriteLine(list.ToList().Max(p => p.Price)); foreach (var product in productList) { Console.WriteLine($"{product.ProductName}-{product.ProductType.TypeName}"); } }}

private static void GetProductListInclude2(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { //集合刷1 对象刷2 ProductType productType = taobao.ProductType.Include("Product") .Where(t => t.TypeId == 1).Single(); Console.WriteLine($"类型：{productType.TypeName}");//一对多关系 foreach (var product in productType.Product) { Console.WriteLine($"{product.ProductName}-{product.ProductNo}"); } }}

跨表查询  Join 内联接

private static void GetProductJoin1(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { //select * from product a inner join producttype b on(a.typeid=b.typeid) var productList = taobao.Product.Join ( taobao.ProductType, a => a.TypeId, b => b.TypeId, (a, b) => new { a.ProductNo, a.ProductName, a.Price, b.TypeName }//投影 ).ToList(); Console.WriteLine($"类型:{productList.GetType()}"); //var dynamic foreach (var product in productList) { Console.WriteLine(product.ProductName + "-" + product.TypeName); } }}

private static void GetProductJoin2(){ //using (TAOBAODBEntities taobao = new TAOBAODBEntities()) //{ // //linq to sql 查询表达式 // List productList = (from a in taobao.Product // join b in taobao.ProductType // on a.TypeId equals b.TypeId // select a) // .ToList();//比较 equals // foreach (var product in productList) // { // Console.WriteLine(product.ProductName + "-" + product.ProductType.TypeName); // } //} using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { //linq to sql 查询表达式 var productList = (from a in taobao.Product join b in taobao.ProductType on a.TypeId equals b.TypeId select new { a.ProductNo, a.ProductName, b.TypeName })//投影信息 .OrderBy(a => a.ProductNo) .ToList();//比较 equals foreach (var product in productList) { Console.WriteLine(product.ProductName + "-" + product.TypeName);//DataTable } }}

EF 原生态SQL脚本执行

添加 删除 修改

添加

private static void Execute(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { string sql = @"insert into ProductType(typeid,typename) values(@id,@name)"; SqlParameter[] paras = { new SqlParameter("@id",5), new SqlParameter("@name","测试2") }; int result = taobao.Database.ExecuteSqlCommand(sql, paras); if (result > 0) { Console.WriteLine("执行成功!"); } else { Console.WriteLine("执行失败..."); } }}

查询返回第一行第一列

private static void ExecuteReader(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { string sql = "select * from product where typeid=@typeid"; SqlParameter[] paras = { new SqlParameter("@typeid",1) }; List productList = taobao.Database .SqlQuery(sql, paras).ToList(); foreach (var product in productList) { Console.WriteLine(product.ProductName + "-" + product.ProductNo); } 每页显示10条 pagesize *(pageindex-1) //List productList = taobao.Database // .SqlQuery(sql, paras) // .Skip(11) // .Take(10)//pagesize // .ToList(); }}

查询返回第一行第一列

private static void ExecuteScalar(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { string sql = "select count(1) from product where typeid=@typeid"; SqlParameter[] paras = { new SqlParameter("@typeid",1) }; int result = taobao.Database.SqlQuery(sql, paras).Single();//单个值.Single() Console.WriteLine(result); }}

查询表 + 视图 +存储过程

public static void GetProductProc(){ using (TAOBAODBEntities taobao = new TAOBAODBEntities()) { SqlParameter[] paras = { new SqlParameter("@typeid",1),//0 new SqlParameter("@count",SqlDbType.Int)//1 }; paras[1].Direction = ParameterDirection.Output;//默认执行参数类型 List productList = taobao.Database .SqlQuery ("PROC_GetProduct @typeid,@count output", paras).ToList(); int count = (int)paras[1].Value;//获取输出参数 Console.WriteLine($"共计:{count}"); foreach (var product in productList) { Console.WriteLine($"{product.ProductNo}- {product.ProductName}"); } }}

一、批量添加数据

static void Main(string[] args) { add(); add2(); Console.ReadKey(); } static void add() { DemoDbEntities db = new DemoDbEntities(); Stopwatch st = new Stopwatch(); st.Start(); for (int i = 0; i < 1000; i++) { db.User.Add(new User { NAME = "张三" +i, AGE = i }); db.SaveChanges(); } st.Stop(); Console.WriteLine("用时：{0}毫秒", st.ElapsedMilliseconds); } static void add2() { DemoDbEntities db = new DemoDbEntities(); Stopwatch st = new Stopwatch(); st.Start(); for (int i = 0; i < 1000; i++) { db.User.Add(new User { NAME = "张三" + i,AGE=i }); } db.SaveChanges(); st.Stop(); Console.WriteLine("用时：{0}毫秒", st.ElapsedMilliseconds); }

通过以上的对比发现，如果每次新增加一个条数据，就用EF SaveChanges 一次，这样会很消耗性能，可以全部标记完了之后，再去调用SaveChanges方法保存数据，这样性能会有所提高。

二、批量删除

static void Main(string[] args) { del(); Console.ReadKey(); } #region EF批量删除 static void del() { DemoDbEntities db = new DemoDbEntities(); List list = db.User.Where(u => u.NAME == "张三1").ToList(); //Any表示只要集合中有值就返回true 否者返回float if (list != null && list.Any()) { foreach (User item in list) { db.User.Remove(item); } db.SaveChanges(); } Console.WriteLine("删除成功"); } #endregion

先查询出结果，保证数据库中有值，再删除。

三、EF编辑数据

第一种方式是查询之后在编辑；

第二种方式直接传入一个需要修改的model实体，可以是部分字段

static void Main(string[] args) { edit2(); Console.ReadKey(); } #region EF编辑数据 static void edit() { DemoDbEntities db = new DemoDbEntities(); var model = db.User.FirstOrDefault(u => u.NAME == "张三3"); model.NAME = "李四"; db.SaveChanges(); Console.WriteLine("编辑成功"); } static void edit2() { DemoDbEntities db = new DemoDbEntities(); User model = new User() { ID=4, NAME="王五" }; //获取代理对象类的状态为Detaceh System.Data.Entity.Infrastructure.DbEntityEntry entry = db.Entry(model); //1、将代理类的状态修改成 Unchanged 2、将代理类中的需要更新的字段的IsModified修改成true entry.State = System.Data.Entity.EntityState.Unchanged; entry.Property("NAME").IsModified = true; //解决对一个或多个实体验证失败 的方法：关闭EF的实体合法性检查 db.Configuration.ValidateOnSaveEnabled = false; db.SaveChanges(); Console.WriteLine("编辑成功"); } #endregion

四、EF中join的使用方法

static void Main(string[] args) { efjoin2(); Console.ReadKey(); } #region EF连表查询的2种方式 static void efjoin() { DemoDbEntities db = new DemoDbEntities(); var sql = db.User.Join(db.GroupInfo, u => u.GroupinfoID, g => g.ID, (c, g) => new {uername=c.NAME,g.NAME }); var list = sql.ToList(); list.ForEach(c => Console.WriteLine(c.uername+""+c.NAME)); } static void efjoin2() { DemoDbEntities db = new DemoDbEntities(); db.User.Include("GroupInfo").Where(c => true).ToList().ForEach(c => Console.WriteLine(c.NAME + "" + c.GroupInfo.NAME)); } #endregion

五、 EF分页查询

static void Main(string[] args) { fenye(); Console.ReadKey(); } #region EF分页 static void fenye() { DemoDbEntities db = new DemoDbEntities(); //在分页前先要是用OrderBy或者OrderByDescending对数据进行正序或者倒序然后在skip（）跳过多少条，take（）查询多少条。 db.User.OrderBy(u => u.ID).Skip(0).Take(5).ToList().ForEach(c=>Console.WriteLine(c.ID)); } #endregion

六、EF存储过程的调用

static void Main(string[] args) { cunchu(); Console.ReadKey(); } #region EF存储过程的使用 static void cunchu() { DemoDbEntities db = new DemoDbEntities(); //调用存储过程USP_GetPagedArticleList int count = 0; //由于totalItems是一个输出参数，所以由程序员自己定义 ObjectParameter ps = new ObjectParameter("totalItems", count); db.USP_GetPagedArticleList(1, 2, ps).ToList().ForEach(u=>Console.WriteLine(u.ID)); Console.WriteLine("总行数=" + ps.Value); } #endregion

七、EF中执行SQL

static void Main(string[] args) { EFtoSql(); Console.ReadKey(); } #region EF中执行SQL语句 static void EFtoSql() { DemoDbEntities db = new DemoDbEntities(); string sql = "update [DemoDb].[dbo].[User] set NAME=@name where ID>@id"; SqlParameter[] p = new SqlParameter[] { new SqlParameter("@id",5), new SqlParameter("@name","王五") }; db.Database.ExecuteSqlCommand(sql,p); Console.WriteLine("修改成功"); } #endregion

八 EF提高查询的方法AsNoTracking

static void Main(string[] args) { EFAsNoTracking(); Console.ReadKey(); } #region EF不跟踪查询AsNoTracking() static void EFAsNoTracking() { DemoDbEntities db = new DemoDbEntities(); //使用AsNoTracking()可以提高查询效率，不用在DbContext中进行缓存 db.User.AsNoTracking().Where(u => u.ID > 5).ToList().ForEach(c => Console.WriteLine(c.ID)); } #endregion

九、EF上下文容器中Set泛型方法的使用

static void Main(string[] args) { EFSet(); Console.ReadKey(); } #region EF上下文容器中的Set泛型方法的作用 static void EFSet() { DemoDbEntities db = new DemoDbEntities(); //db.Set 相当于db.User db.Set().Where(u => u.ID > 5).ToList().ForEach(c => Console.WriteLine(c.ID)); } #endregion

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。