EFCore实践教程三

延迟加载

nuget 

Microsoft.EntityFrameworkCore.Proxies

 

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;

namespace EFStudy
{
    public class BloggingContext : DbContext
    {
        //提升性能建议  延迟加载，用不好有一定风险
        /*1.禁止客户端评估，提高性能
         * 
         * 2.只读方案中.AsNoTracking() 不跟踪
         * 
         * 
         * 3.
         * 
         * 
         */

        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }
        public DbSet<Person> Persons { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["BloggingDatabase"].ConnectionString);
            optionsBuilder.UseSqlServer("Data Source = .;Initial Catalog = Test;User Id = sa;Password = 123456;");

            //.ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));//禁止客户端评估，提高性能
        }

        public class Blog
        {
            public int BlogId { get; set; }
            public string Url { get; set; }
            public int? Rating { get; set; }

            public Person Person { get; set; }
            public List<Post> Posts { get; set; }
        }
        public class Post
        {
            public int PostId { get; set; }
            public string Title { get; set; }
            public string Content { get; set; }
            public int Rating { get; set; }

            //public int BlogId { get; set; }
            public Blog Blog { get; set; }
            public Person Author { get; set; }

        }


        #region 深入1
        public class Person
        {
            public int PersonId { get; set; }
            public string Name { get; set; }

            public List<Post> AuthoredPosts { get; set; }
            public List<Blog> OwnedBlogs { get; set; }

        }
        #endregion

        #region 深入2

        public DbSet<People> Peoples { get; set; }
        public DbSet<School> Schools { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
           // modelBuilder.Entity<School>().HasMany(s => s.Students).WithOne(s => s.School); 
        }
        public class People
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
        public class Student : People
        {
            public School School { get; set; }
        }
        public class School
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public List<Student> Students { get; set; }
        }
        #endregion


    }
}

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using static EFStudy.BloggingContext;

namespace EFStudy
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            //test();
            //addpots();
            //addperson();
            //test1();
            //test2();
            //addData3();
            //test3();
            //test4();
            //test5();
            //test9();
            //test11();
            //test12();
            //test13();
            test16();
            Console.ReadLine();
        }
        #region 添加基础数据


        static void test()
        {
            using (var db = new BloggingContext())
            {
                Console.WriteLine("----------------test-----------------");
                db.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
                db.Blogs.Add(new Blog { Url = "http://blogs.cddn.com" });
                db.Blogs.Add(new Blog { Url = "http://blogs.xiaonei.com" });
                var count = db.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
                Console.WriteLine(); Console.WriteLine("All blogs in database:");
                foreach (var blog in db.Blogs) { Console.WriteLine(" - {0}", blog.Url); }
            }

        }

        static void addpots()
        {
            Console.WriteLine("----------------addpots-----------------");
            using (var db = new BloggingContext())
            {
                var blog = db.Blogs.FirstOrDefault();
                for (int i = 0; i < 10; i++)
                {
                    db.Posts.Add(new Post { Content = "Posts" + i, Title = "nihao" + i, Blog = blog });
                }
                var count = db.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
            }

        }

        static void addperson()
        {
            Console.WriteLine("----------------addperson-----------------");
            using (var db = new BloggingContext())
            {
                var pot = db.Posts.ToList();
                var blog = db.Blogs.FirstOrDefault();
                var listBlog = new List<Blog>();
                listBlog.Add(blog);
                for (int i = 0; i < 10; i++)
                {
                    db.Persons.Add(new Person { AuthoredPosts = pot, Name = "wang", OwnedBlogs = listBlog });
                }
                var count = db.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
            }

        }
        #endregion

        #region Include查询
        static void test1()
        {
            Console.WriteLine("----------------test1 TEST Include-----------------");
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs.Include(blog => blog.Posts).ToList();
                foreach (var blog in blogs)
                {
                    Console.WriteLine($"博客id{blog.BlogId}");
                    foreach (var item in blog.Posts)
                    {
                        Console.WriteLine($"          该所包含文章{item.Title}");
                    }
                    Console.WriteLine("----------------");
                }
            }

        }
        #endregion

        #region 深入1
        static void test2()
        {
            //方式1
            Console.WriteLine("----------------test1 TEST Include-----------------");
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs.Include(blog => blog.Posts).Include(blog => blog.Person).ToList();
                foreach (var blog in blogs)
                {
                    Console.WriteLine($"博客id{blog.BlogId}");
                    foreach (var item in blog.Posts)
                    {
                        Console.WriteLine($"          该所包含文章{item.Title}-作者{item.Author.Name}");
                    }
                    Console.WriteLine("----------------");
                }


                //方式2  虽然也是一样结果不过意义不一样，更深入的一层
                var blogs2 = context.Blogs.
                  Include(blog => blog.Posts).
                  ThenInclude(post => post.Author).ToList();
            }

        }

        #endregion

        #region 深入2
        //====这种方式插入式可以的，ID能对应上,但是多线程时候是否会出错？没有测试,尽量避免，虽然方便了些
        static void addData3()
        {
            School school = new School() { Name = "苏州中学" };
            List<Student> listStu = new List<Student>();
            for (int i = 0; i < 10; i++)
            {
                listStu.Add(new Student()
                {
                    Name = $"yuan{ i}",
                    School = school
                });
            }
            using (var context = new BloggingContext())
            {
                var entity = context.Schools.Add(school);
                context.Peoples.AddRange(listStu);
                var count = context.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
            }
        }
        static void test3()
        {
            //方式1
            Console.WriteLine("----------------test3 -----------------");
            using (var context = new BloggingContext())
            {
                //所有人员（可以使用许多模式预先加载的学生）的 School 导航的内容，断点是可以看到的，但是点不出来school
                dynamic list = context.Peoples.Include(person => ((Student)person).School).ToList();
                //context.People.Include(person => (person as Student).School).ToList()
                foreach (var item in list)
                {
                    Console.WriteLine($"{item.Name}所在学校{item.School.Name}");
                    Console.WriteLine("----------------");
                }
            }

        }

        static void test4()
        {
            Console.WriteLine("----------------test4 -----------------");

            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs.
                    Include(blog => blog.Posts).
                    Select(blog => new
                    {
                        Id = blog.BlogId,
                        Url = blog.Url
                    }).ToList();
                foreach (var item in blogs)
                {
                    Console.WriteLine($"{item.Url}");
                    Console.WriteLine("----------------");
                }
            }

        }
        //显示加载 可以逐步填充内置的属性
        static void test5()
        {
            Console.WriteLine("----------------test5 -----------------");

            using (var context = new BloggingContext())
            {
                var blog = context.Blogs.Single(b => b.BlogId == 2);
                context.Entry(blog).Collection(b => b.Posts).Load();//会填充Posts
                context.Entry(blog).Reference(b => b.Person).Load();//会填充Person
            }

            using (var context = new BloggingContext())
            {
                var blog = context.Blogs.Single(b => b.BlogId == 1);
                var goodPosts = context.Entry(blog)
                    .Collection(b => b.Posts)
                    .Query()
                    .Where(p => p.Rating > 3)
                    .ToList();
            }

        }
        //延迟加载
        //使用延迟加载的最简单方式是通过安装 Microsoft.EntityFrameworkCore.Proxies 包，并通过调用 UseLazyLoadingProxies 来启用该包
        //MVC中protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseLazyLoadingProxies() .UseSqlServer(myConnectionString);



        static void test6()
        {
            using (var context = new BloggingContext())
            {
                var blogs = context.Blogs
                    .OrderByDescending(blog => blog.Rating)
                    .Select(blog => new
                    {
                        Id = blog.BlogId,
                        Url = StandardizeUrl(blog.Url)
                    }).ToList();
            }
            //不跟踪
            //如果结果集不包含任何实体类型，则不会执行跟踪。 在以下返回匿名类型（具有实体中的某些值，但没有实际实体类 型的实例）的查询中，不会执行跟踪。
            //using (var context = new BloggingContext()) 
            //{ 
            //    context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
            //    var blogs = context.Blogs.ToList(); 
            //}
        }
        public static string StandardizeUrl(string url) { url = url.ToLower(); if (!url.StartsWith("http://")) { url = string.Concat("http://", url); } return url; }

        //原生sql支持
        static void test7()
        {
            using (var context = new BloggingContext())
            {
                var user = "johndoe";
                var blogs = context.Blogs.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user).ToList();

                var searchTerm = ".NET";
                blogs = context.Blogs
                    .FromSqlRaw($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
                    .Where(b => b.Rating > 3)
                    .OrderByDescending(b => b.Rating).ToList();

                blogs = context.Blogs
                    .FromSqlRaw($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
                    .Include(b => b.Posts).ToList();
            }

        }
        //异步查询，忽略，基本一样




        /*
         * protected override void OnModelCreating(ModelBuilder modelBuilder)
         * { 
         * modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("_tenantId");
         * // Configure entity filters 
         * modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId); 
         * modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
         * }
         * 传递给 HasQueryFilter 调用的谓词表达式将立即自动应用于这些类型的任何 LINQ 查询。
         * 禁用筛选器IgnoreQueryFilters()
         * blogs = db.Blogs .Include(b => b.Posts) .IgnoreQueryFilters() .ToList()
         */



        //保存数据
        public static void test8()
        {
            using (var context = new BloggingContext())
            {
                var blog = new Blog { Url = "http://sample.com" };
                context.Blogs.Add(blog);
                context.SaveChanges();
                Console.WriteLine(blog.BlogId + ": " + blog.Url);
            }
        }
        // context.Entry(blog).State = EntityState.Modified  应该也有些用途
        //插入
        public static void test9()
        {
            using (var context = new BloggingContext())
            {
                var blog = new Blog
                {
                    Url = "http://blogs.msdn.com/dotnet",
                    Posts = new List<Post>
                    {
                        new Post { Title = "Intro to C#" },
                        new Post { Title = "Intro to VB.NET" },
                        new Post { Title = "Intro to F#" }
                    }
                };
                context.Blogs.Add(blog);
                var count = context.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
            }
        }
        public static void test10()
        {

            using (var context = new BloggingContext())
            {
                var blog = new Blog
                {
                    Url = "http://blogs.msdn.com/dotnet",
                    Posts = new List<Post>
                    {
                        new Post { Title = "Intro to C#" },
                        new Post { Title = "Intro to VB.NET" },
                        new Post { Title = "Intro to F#" }
                    }
                };
                context.Blogs.Add(blog);
                var count = context.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
            }

            //using (var context = new BloggingContext())
            //{
            //    var blog = context.Blogs.Include(b => b.Posts).First();
            //    var post = new Post { Title = "Intro to EF Core" };
            //    blog.Posts.Add(post);
            //    var count = context.SaveChanges();
            //    Console.WriteLine("{0} records saved to database", count);
            //}

        }

        public static void test11()
        {
            //更改关系
            using (var context = new BloggingContext())
            {
                var blog = new Blog { Url = "http://blogs.msdn.com/visualstudio" };
                var post = context.Posts.First();
                post.Blog = blog;
                var count = context.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
            }
            ////删除关系
            //using (var context = new BloggingContext())
            //{ 
            //    var blog = context.Blogs.Include(b => b.Posts).First();
            //    var post = blog.Posts.First(); 
            //    blog.Posts.Remove(post); 
            //    context.SaveChanges(); 
            //}
        }
        //级联删除
        public static void test12()
        {
            //我测试结果为只是删除了关系,默认应该是这样子吧
            using (var context = new BloggingContext())
            {
                var blog = context.Blogs.Include(b => b.Posts).First();
                //var posts = blog.Posts.ToList();//无关紧要
                /*
                 * 
                 *  protected override void OnModelCreating(DbModelBuilder modelBuilder)
                    {
                            //移除外键级联删除
                            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
                            modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
                   }
                    Cascade（默认）    删除实体    删除实体
                    ClientSetNull    SaveChanges 引发异常    无
                    SetNull    引发 SaveChanges    引发 SaveChanges
                    Restrict    无    无
                 * 
                 * 
                 */
                context.Remove(blog);

                var count = context.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
            }

        }

        //删除pots与blog的关系
        public static void test13()
        {
            using (var context = new BloggingContext())
            {
                var blog = context.Blogs.Include(b => b.Posts).First();
                var posts = blog.Posts.ToList();
                blog.Posts.Clear();
                var count = context.SaveChanges();
                Console.WriteLine("{0} records saved to database", count);
            }

        }
        /*
         * 冲突
         * 有有三三组组值值可可用用于于帮帮助助解解决决并并发冲冲突突：
         * ： “当前值”是应用程序尝试写入数据库的值。
         * “原始值”是在进行任何编辑之前最初从数据库中检索的值。
         * “数据库值”是当前存储在数据库中的值。
         * 处理并发冲突的常规方法是：
         * 
         * 1. 在 SaveChanges 期间捕获 DbUpdateConcurrencyException 。
         * 2. 使用 DbUpdateConcurrencyException.Entries 为受影响的实体准备一组新更改。
         * 3. 刷新并发令牌的原始值以反映数据库中的当前值。
         * 4. 重试该过程，直到不发生任何冲突
         */
        #endregion
        //使用事务

        public static void test14()
        {
            using (var context = new BloggingContext())
            {
                using (var transaction = context.Database.BeginTransaction())
                {
                    try
                    {
                        context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });
                        context.SaveChanges();
                        context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/visualstudio" });
                        context.SaveChanges();
                        var blogs = context.Blogs.OrderBy(b => b.Url).ToList();
                        // Commit transaction if all commands succeed, transaction will auto-rollback
                        // when disposed if either commands fails
                        transaction.Commit();
                    }
                    catch (Exception)
                    { // TODO: Handle failure } } }

                    }
                }
            }
        }


        //TransactionScope一般用不到
        //其他一些方式
        public static void InsertOrUpdate(BloggingContext context, Blog blog) 
        { 
            var existingBlog = context.Blogs.Find(blog.BlogId); 
            if (existingBlog == null) 
            { 
                context.Add(blog);
            } 
            else
            {
                context.Entry(existingBlog).CurrentValues.SetValues(blog);
            } 
            context.SaveChanges();
        }

        public static void InsertUpdateOrDeleteGraph(BloggingContext context, Blog blog)
        { 
            var existingBlog = context.Blogs.Include(b => b.Posts).FirstOrDefault(b => b.BlogId == blog.BlogId); 
            if (existingBlog == null) 
            {
                context.Add(blog); 
            }
            else
            { 
                context.Entry(existingBlog).CurrentValues.SetValues(blog); 
                foreach (var post in blog.Posts) 
                { 
                    var existingPost = existingBlog.Posts.FirstOrDefault(p => p.PostId == post.PostId); 
                    if (existingPost == null)
                    { 
                        existingBlog.Posts.Add(post);
                    } 
                    else
                    { 
                        context.Entry(existingPost).CurrentValues.SetValues(post); 
                    } 
                } 
                foreach (var post in existingBlog.Posts) 
                { 
                    if (!blog.Posts.Any(p => p.PostId == post.PostId))
                    { 
                        context.Remove(post);
                    } 
                }
            } 
            context.SaveChanges();
        }

        static void test15()
        {
            //using (var context = new EmployeeContext()) 
            //{ 
            //    context.Employees.Add(new Employee { EmployeeId = 100, Name = "John Doe" });
            //    context.Employees.Add(new Employee { EmployeeId = 101, Name = "Jane Doe" });
            //    context.Database.OpenConnection(); 
            //    try 
            //    { 
            //        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Employees ON");
            //        context.SaveChanges(); 
            //        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Employees OFF"); 
            //    } finally { context.Database.CloseConnection(); } 
            //    foreach (var employee in context.Employees)
            //    { 
            //        Console.WriteLine(employee.EmployeeId + ": " + employee.Name);
            //    } 
            //}
        }
        //EFCORE的可视化工具一些等 ,感觉没什么意义
        //https://channel9.msdn.com/Shows/Visual-Studio-Toolbox/Entity-Framework-Power-Tools

        //EFCORE的弹性连接 需要配置点，具体见PDF文档

        public static void test16()
        {
            using (var context = new BloggingContext())
            {
                #region GroupJoin  的带两个相乘返回13*4   返回类型是list<Blog,Post>类型
                var query = from b in context.Set<Blog>()
                            from p in context.Set<Post>()
                            select new { b, p };
                var list = query.ToList();
                #endregion
            }

        }
    }
}