EF框架实现数据添加、删除、查询（包含查询部分列）、修改、分页、排序

---

（EF实体数据模型生成方法已经在上一篇文章。）

要操作的数据数据库表结构：

 

1.添加数据

        private void add_Click(object sender, EventArgs e)
        {
            //添加一条数据  以添加部门为例
            department dept = new department();//构建一个对象
            dept.deptId = 10;
            dept.deptName = "测试部门";
            arsEntities addDept = new arsEntities();//此类在*.Context.cs文件中查看  继承Context类 具体作用百度可知
            addDept.department.Add(dept);//将要操作的数据添加到EF上，并且打上“添加”标记
            addDept.SaveChanges();//与数据库打交道
        }

 

2.查询数据（linq查询和 lambda查询）

• linq表达式查询

        private void linqSearch_Click(object sender, EventArgs e)
        {
            //linq查询一条数据
            arsEntities linqDept = new arsEntities();
            var deptList = from u in linqDept.department//此处deptList为IQueryable<T>类型
                           where u.deptId == 1
                           select u;
            //以上linq表达式类似于select * from department where deptId==1

            var dept = deptList.FirstOrDefault();//防止集合为空报错
            foreach (var dempt in deptList)
            {
                //此处dempt为department类型
            }

        }

• linq查询部分列

       private void linqSeatoPart_Click(object sender, EventArgs e)
        {
            //查询部分列数据
            arsEntities linqDept = new arsEntities();
            var deptList = from u in linqDept.department
                           select new { dptName = u.deptName };//匿名函数         
            var dept = deptList.FirstOrDefault();
            foreach (var dempt in deptList)
            {
                //此处dempt为department类型
            }
        }

• lambda表达式查询（包含查询部分列方法）

       private void lambdaSearch_Click(object sender, EventArgs e)
        {
            //lambda查询
            arsEntities lambdaDept = new arsEntities();
            var dept = lambdaDept.department.Where<department>(d => d.deptId == 10).FirstOrDefault();//此处where不是关键字 是一个有过滤作用的扩展方法 传一个委托类型的参数

            //lambda查询部分列
            var depts = lambdaDept.department.Where<department>(d => d.deptId == 10).Select(p=>new {p.deptId,p.deptName}).FirstOrDefault();
        }

 

3.删除数据

        private void del_Click(object sender, EventArgs e)
        {
            //删除一条数据
            arsEntities delDept = new arsEntities();
            var deptList = from u in delDept.department
                           where u.deptId == 10
                           select u;
            var dept = deptList.FirstOrDefault();//FristOrDefault返回集合中第一个元素  如果没有默认值为NULL  这里要删除的元素集合可能只有一条数据或没有数据
                                                 //如果不用这条语句当集合为空时会报错
            if (dept != null)
            {
                //删除的第一种标记方式
                //delDept.department.Remove(dept);//在EF上打上“删除”标记
                //第二种标记方式
                delDept.Entry<department>(dept).State = System.Data.Entity.EntityState.Deleted;
                delDept.SaveChanges();
            }
            //可以以直接构建对象的方式删除数据  但要用第二种标记方式
            //department department = new department() { deptId = 10 };
            //delDept.Entry<department>(department).State = System.Data.Entity.EntityState.Deleted;
        }

 

4.修改数据

        private void upde_Click(object sender, EventArgs e)
        {
            //修改一条数据
            arsEntities updDept = new arsEntities();
            var deptList = from u in updDept.department
                           where u.deptId == 10
                           select u;
            var dept = deptList.FirstOrDefault();
            dept.deptName = "测试修改";
            updDept.Entry<department>(dept).State = System.Data.Entity.EntityState.Modified;//以Modified标记更新相当于update
            updDept.SaveChanges();

        }

 

5.数据分页

       private void paging_Click(object sender, EventArgs e)
        {
            //分页操作
            arsEntities deptLists = new arsEntities();
            //Skip表示取的时候跳过多少条数据   Take表示取出多少条数据
            //var deptList = deptLists.department.Where<department>(dept => true).OrderBy<department, int>(c => c.deptId).Skip<department>(2).Take<department>(2);
            //上一行代码表示跳过两条数据 去两条  及从3开始取数据3、4

            int pageIndex = 2;//页码
            int pageSize = 2;//每页展示对少数据
            var deptList = deptLists.department.Where<department>(dept => true).OrderBy<department, int>(c => c.deptId).Skip<department>((pageIndex-1)*pageSize).Take<department>(pageSize);
            foreach (var dempt in deptList)
            {

            }

            以上分页代码等价于
            //IQueryable<department> depList=(from d in deptLists.department
            //                                orderby d.deptId descending
            //                                where d.deptId>0
            //                                select d).Skip<department>((pageIndex - 1) * pageSize).Take<department>(pageSize);
        }

 

6.数据排序

        private void sort_Click(object sender, EventArgs e)
        {
            //对数据进行排序

            //OrderBy为升序排序
            arsEntities deptLists = new arsEntities();
            //var deptList = deptLists.department.Where<department>(dept => true).OrderBy<department, int>(c => c.deptId); // 按ID排序
            //OrderByDescending为降序排序
            var deptList = deptLists.department.Where<department>(dept => true).OrderByDescending<department, int>(c => c.deptId);
            //按多字段排序
            //var deptList = deptLists.department.Where<department>(dept => true).OrderBy<department, int>(c => c.deptId).OrderByDescending<department, string>(c => c.deptName);

            this.dataGridView1.Rows.Clear();
            foreach (var dempt in deptList)
            {
                //此处dempt为department类型
            }
        }