c# ASP.net EF基本操作增、删、改、查、分页，join……等

最新推荐文章于 2024-02-15 13:00:00 发布

橙-极纪元JJY.Cheng

最新推荐文章于 2024-02-15 13:00:00 发布

阅读量886

点赞数

分类专栏： c# # asp.net mvc # c# WinForm

本文链接：https://blog.csdn.net/cplvfx/article/details/106733957

版权

c# 同时被 3 个专栏收录

120 篇文章 11 订阅

订阅专栏

asp.net mvc

90 篇文章 6 订阅

订阅专栏

c# WinForm

27 篇文章 2 订阅

订阅专栏

或许你还需要

ef 调用存储过程

https://blog.csdn.net/cplvfx/article/details/109490445

添加

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<Product>
            (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<Product> product2 = taobao.Product.OrderByDescending(p => p.ProductNo).ToList<Product>();
 
    }
}

批量修改产品

private static void ModifyProductList()
{
    using (TAOBAODBEntities taobao = new TAOBAODBEntities())
    {
        List<Product> productList = taobao.Product.Where(p => p.TypeId == 1).ToList<Product>();
        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<Product>();
        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<Product> 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<ProductType>();

        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<Product> productList = (from a in taobao.Product
    //                                 join b in taobao.ProductType
    //                                 on a.TypeId equals b.TypeId
    //                                 select a)
    //                                 .ToList<Product>();//比较 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<Product> productList = taobao.Database
            .SqlQuery<Product>(sql, paras).ToList();
        foreach (var product in productList)
        {
            Console.WriteLine(product.ProductName + "-" + product.ProductNo);
        }

        每页显示10条  pagesize *(pageindex-1)
        //List<Product> productList = taobao.Database
        //    .SqlQuery<Product>(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<int>(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<Product> productList = taobao.Database
            .SqlQuery<Product>
            ("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<User> 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<T>泛型方法的使用

static void Main(string[] args)
        {
            EFSet();
            Console.ReadKey();
        }

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

转载连接：https://www.cnblogs.com/M-LittleBird/p/5852395.html

橙-极纪元JJY.Cheng

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
c# ASP.net EF基本操作增、删、改、查、分页，join……等

添加private static void AddProduct(){ Product product = new Product() { ProductName = "测试产品1", Price = 2000, Description = "产品测试描述信息", TypeId = 2, ImageUrl = "images/xxx.jpg" }; //带事务处理机制添加对象关系映
复制链接

扫一扫