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有這個(gè)數(shù)據(jù)
                    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;//需要指定狀態(tài)  或者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();//一次性保存全部的變化  多個(gè)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 導(dǎo)航屬性延遲加載/預(yù)先加載/顯示加載
            //一對(duì)一  一對(duì)多  多對(duì)多 Code First配置見附件《EF導(dǎo)航屬性和外鍵.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);
                //實(shí)體類型包含其它實(shí)體類型（POCO類）的屬性（也可稱為導(dǎo)航屬性），且同時(shí)滿足如下條件即可實(shí)列延遲加載，
                //1.該屬性的類型必需為public且不能為Sealed；
                //2.屬性標(biāo)記為Virtual
                context.Configuration.LazyLoadingEnabled = true;//默認(rèn)是true  針對(duì)導(dǎo)航屬性的
                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)//這個(gè)時(shí)候才去數(shù)據(jù)庫(kù)查詢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;//不延遲加載,不會(huì)再次查詢了
                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)//這個(gè)時(shí)候才不去數(shù)據(jù)庫(kù)查詢了，所以用戶全是空的了
                    {
                        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 手動(dòng)加載
            {
                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();//單個(gè)屬性用
                    foreach (var item in company.User)
                    {
                        Console.WriteLine("User name={0}", item.Name);
                    }
                }
            }
            #endregion 導(dǎo)航屬性 延遲加載

            #region 插入數(shù)據(jù)自增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();//提交事務(wù)
                }
            }
            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 插入數(shù)據(jù)自增id

            #region 主從增加刪除
            {
                //級(jí)聯(lián)刪除  就直接刪主表
                //非級(jí)聯(lián)刪除，需要每個(gè)都remove，然后保存即可
            }
            #endregion
        }
    }
}