EF相关基础语法

实力决定地位 2018-01-31

展开全文

using Ruanmou.EF.Model;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace Ruanmou.Project
{
    public class EFAdvancedTest
    {
        public static void Show()
        {
            #region 本地增删改
            {
                User userNew = null;
                using (JDDbContext context = new JDDbContext())
                {
                    context.Database.Log += c => Console.WriteLine(c);
                    userNew = new User()
                    {
                        Account = "Admin",
                        State = 0,
                        CompanyId = 4,
                        CompanyName = "北京北京",
                        CreateTime = DateTime.Now,
                        CreatorId = 1,
                        Email = "1231232131@qq.com",
                        LastLoginTime = null,
                        LastModifierId = 0,
                        LastModifyTime = DateTime.Now,
                        Mobile = "123123123",
                        Name = "123131",
                        Password = "12356789",
                        UserType = 1
                    };
                    context.User.Add(userNew);
                    context.SaveChanges();//

//userNew.Name = "123123";
//context.SaveChanges();

                    //context.User.Remove(userNew);
                    //context.SaveChanges();
                }

                Console.WriteLine("***************************************");
                using (JDDbContext context = new JDDbContext())
                {
                    //context.User.Remove(userNew);//wrong
                    //context.SaveChanges();

//User user = context.User.Find(userNew.Id);
//context.User.Remove(user);

                    context.User.Attach(userNew);//without this error    or find
                    context.User.Remove(userNew);//必须事先在context有这个数据
                    context.SaveChanges();
                }
                Console.WriteLine("***************************************");
                using (JDDbContext context = new JDDbContext())
                {
                    context.Database.Log += c => Console.WriteLine(c);
                    userNew.Name = "12312321";

                    context.User.Attach(userNew);//without this nothing hanppened   or find
                    //userNew.Name = "131231";
                    context.Entry<User>(userNew).State = EntityState.Modified;//需要指定状态或者Attach后修改 clone
                    context.SaveChanges();
                }
                Console.WriteLine("***************************************");
                using (JDDbContext context = new JDDbContext())
                {
                    context.Database.Log += c => Console.WriteLine(c);

                    User user2 = context.User.Find(2);
                    user2.Name = user2.Name + "123";
                    User user7 = context.User.Find(7);
                    user7.Name = user7.Name + "123";
                    context.SaveChanges();//一次性保存全部的变化多个context呢
                }
                Console.WriteLine("***************************************");
                using (JDDbContext context = new JDDbContext())
                {   //本地缓存
                    context.Database.Log += c => Console.WriteLine(c);
                    var list = context.User.Where(u => u.Id < 5).ToList();
                    var user1 = context.User.Find(2);
                    Console.WriteLine("**********************************");
                    var user2 = context.User.Where(u => u.Id == 2).ToList();
                    Console.WriteLine("**********************************");
                    var user3 = context.User.Find(2);
                    Console.WriteLine("**********************************");
                    var user4 = context.User.Where(u => u.Id == 2).ToList();
                    Console.WriteLine("**********************************");
                }
                using (JDDbContext context = new JDDbContext())
                {
                    context.Database.Log += c => Console.WriteLine(c);
                    //本地缓存
                    var list1 = context.User.Where(u => u.Id > 5).AsNoTracking().ToList();
                    Console.WriteLine("*****************list1*****************");
                    var list2 = context.User.Where(u => u.Id > 5).ToList();
                    Console.WriteLine("******************list2****************");
                    var list3 = context.User.Where(u => u.Id > 5).ToList();
                    Console.WriteLine("******************list3****************");
                    var list4 = context.User.Where(u => u.Id > 5).ToList();
                    Console.WriteLine("*****************list4*****************");
                }
            }

#endregion

            #region 导航属性延迟加载/预先加载/显示加载
            //一对一一对多多对多 Code First配置见附件《EF导航属性和外键.docx》
            Console.WriteLine("******************************************");
            using (JDDbContext context = new JDDbContext())
            {
                context.Database.Log += c => Console.WriteLine(c);
                var companyList = context.Set<Company>().Where(c => c.Id > 0);
                //var companyList = context.Set<Company>().Where(c => c.Id > 0).ToList();//.ToList()直接Company都加载过来

                foreach (var company in companyList)
                {
                    Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
                }
            }

            Console.WriteLine("******************************************");
            using (JDDbContext context = new JDDbContext())
            {
                context.Database.Log += c => Console.WriteLine(c);
                //实体类型包含其它实体类型（POCO类）的属性（也可称为导航属性），且同时满足如下条件即可实列延迟加载，
                //1.该属性的类型必需为public且不能为Sealed；
                //2.属性标记为Virtual
                context.Configuration.LazyLoadingEnabled = true;//默认是true 针对导航属性的
                var companyList = context.Set<Company>().Where(c => c.Id > 0);
                foreach (var company in companyList)
                {
                    Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
                    foreach (var item in company.User)//这个时候才去数据库查询user
                    {
                        Console.WriteLine("User name={0}", item.Name);
                    }
                }
            }
            Console.WriteLine("******************************************");
            using (JDDbContext context = new JDDbContext())
            {
                context.Database.Log += c => Console.WriteLine(c);
                context.Configuration.LazyLoadingEnabled = false;//不延迟加载,不会再次查询了
                var companyList = context.Set<Company>().Where(c => c.Id > 0);
                foreach (var company in companyList)
                {
                    Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
                    foreach (var item in company.User)//这个时候才不去数据库查询了，所以用户全是空的了
                    {
                        Console.WriteLine("User name={0}", item.Name);
                    }
                }
            }
            Console.WriteLine("******************************************");
            using (JDDbContext context = new JDDbContext())
            {
                context.Database.Log += c => Console.WriteLine(c);
                //context.Configuration.LazyLoadingEnabled = false;//不延迟加载，指定Include，一次性加载出来
                var companyList = context.Set<Company>().Include("User").Where(c => c.Id > 0);
                foreach (var company in companyList)
                {
                    Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
                    foreach (var item in company.User)
                    {
                        Console.WriteLine("User name={0}", item.Name);
                    }
                }
            }

            using (JDDbContext context = new JDDbContext())//LoadProperty 手动加载
            {
                context.Database.Log += c => Console.WriteLine(c);
                context.Configuration.LazyLoadingEnabled = false;//不延迟加载，指定Include，一次性加载出来
                var companyList = context.Set<Company>().Where(c => c.Id > 0);
                foreach (var company in companyList)
                {
                    Console.WriteLine("Company id={0} name={1}", company.Id, company.Name);
                    context.Entry<Company>(company).Collection(c => c.User).Load();//集合显示加载
                    //context.Entry<Company>(company).Reference(c => c.User).Load();//单个属性用
                    foreach (var item in company.User)
                    {
                        Console.WriteLine("User name={0}", item.Name);
                    }
                }
            }
            #endregion 导航属性延迟加载

#region 插入数据自增id

            using (JDDbContext context = new JDDbContext())//保存 TransactionScope
            {
                context.Database.Log += c => Console.WriteLine(c);
                using (TransactionScope trans = new TransactionScope())
                {
                    Company company = new Company()
                    {
                        Name = "Test1",
                        CreateTime = DateTime.Now,
                        CreatorId = 1,
                        LastModifierId = 0,
                        LastModifyTime = DateTime.Now,
                    };
                    context.Company.Add(company);
                    context.SaveChanges();//company.id赋值了

                    User userNew = new User()
                    {
                        Account = "Admin",
                        State = 0,
                        CompanyId = company.Id,
                        CompanyName = "北京有限公司",
                        CreateTime = DateTime.Now,
                        CreatorId = 1,
                        Email = "1112323@qq.com",
                        LastLoginTime = null,
                        LastModifierId = 0,
                        LastModifyTime = DateTime.Now,
                        Mobile = "121231311113",
                        Name = "城市新增",
                        Password = "12356789",
                        UserType = 1
                    };
                    context.User.Add(userNew);
                    context.SaveChanges();//userNew.id赋值了
                    trans.Complete();//提交事务
                }
            }
            using (JDDbContext context = new JDDbContext())//保存 TransactionScope
            {
                context.Database.Log += c => Console.WriteLine(c);
                context.Configuration.LazyLoadingEnabled = false;
                Company company = new Company()
                {
                    Name = "Test1",
                    CreateTime = DateTime.Now,
                    CreatorId = 1,
                    LastModifierId = 0,
                    LastModifyTime = DateTime.Now,
                };

                User userNew = new User()
                {
                    Account = "Admin",
                    State = 0,
                    CompanyId = company.Id,
                    CompanyName = company.Name,
                    CreateTime = DateTime.Now,
                    CreatorId = 1,
                    Email = "123123@qq.com",
                    LastLoginTime = null,
                    LastModifierId = 0,
                    LastModifyTime = DateTime.Now,
                    Mobile = "123123",
                    Name = "开心就好",
                    Password = "12356789",
                    UserType = 1
                };
                company.User = new List<User>() { userNew };
                context.Company.Add(company);
                context.SaveChanges();
            }
            #endregion 插入数据自增id

            #region 主从增加删除
            {
                //级联删除就直接删主表
                //非级联删除，需要每个都remove，然后保存即可
            }
            #endregion
        }
    }
}