.Net 性能优化--EFCore(EntityFrameworkCore)

新建.NET CORE CONSOLE控制台项目NetCoreConsole，管理NuGet程序包种添加一下引用

//Microsoft.EntityFrameworkCore //EFCore 
//Microsoft.Extensions.Logging  // 日志
//Microsoft.Extensions.Logging.Console // 日志输出到Console控制台
//Microsoft.Extensions.Logging.Debug  // 日志输出到调试Debug
//Microsoft.EntityFrameworkCore.Proxies// 懒加载，延迟加载，EFCore没有懒加载，使用该库，可以实现懒加载
//Microsoft.EntityFrameworkCore.SqlServer//EFCore使用的SqlServer数据库
//Pomelo.EntityFrameworkCore.MySql//EFCore使用的MySql数据库

项目代码如下：

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using Microsoft.Extensions.Logging;
using System.Linq;
using System.Diagnostics;
using Microsoft.EntityFrameworkCore.Storage;

//Microsoft.EntityFrameworkCore //EFCore 
//Microsoft.Extensions.Logging  // 日志
//Microsoft.Extensions.Logging.Console // 日志输出到Console控制台
//Microsoft.Extensions.Logging.Debug  // 日志输出到调试Debug
//Microsoft.EntityFrameworkCore.Proxies// 懒加载，延迟加载，EFCore没有懒加载，使用该库，可以实现懒加载
//Microsoft.EntityFrameworkCore.SqlServer//EFCore使用的SqlServer数据库
//Pomelo.EntityFrameworkCore.MySql//EFCore使用的MySql数据库
namespace NetCoreConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("测试  EF core ");
            BlogContext blogContext = new BlogContext();

            #region ef 数据库的创建和删除
            //Console.WriteLine("删除数据库");
            //blogContext.Database.EnsureDeleted();
            //Console.WriteLine("添加数据库");            
            blogContext.Database.EnsureCreated();
            //Console.WriteLine("添加数据库成功"); 
            #endregion

            #region 数据迁移相关
            //IEnumerable<string> vs = blogContext.Database.GetMigrations();
            //blogContext.Database.GetPendingMigrations();
            //blogContext.Database.GetAppliedMigrations();
            //blogContext.Database.Migrate(); 
            //Console.WriteLine("添加数据库成功");
            #endregion

            #region 预先加载，懒加载

            //预先加载(贪婪加载，饥饿加载，一次行加载所有)
            //var list = blogContext.Blog.Include(op => op.Posts).ThenInclude(op => op.Author).ToList();
            //var list = blogContext.Blog.Include(op => op.Posts).ThenInclude(op => op.Author).ToList();
            //var list = blogContext.Blog.Include(op => op.Posts).ThenInclude(op => op.Author).ToList().AsQueryable();
            //foreach (var item in list)
            //{
            //    Console.WriteLine($"blog:{item}");
            //    foreach (var it in item.Posts)
            //    {
            //        Console.WriteLine($"Posts:{it.Title}");
            //        Console.WriteLine($"person:{it.Author.Name}");
            //    }
            //}
            //懒加载(延迟加载)，又称按需加载，net ef core 默认时候没有懒加载的，
            //ef是有的，ef core 要使用懒加载，需要使用proxy代理类UseLazyLoadingProxies，并且导航属性要添加Virtual标记
            //optionsBuilder.UseLazyLoadingProxies() 
            #endregion

            #region IQueryable和IEnumerable不同
            //IQueryable和IEnumerable不同
            //IQueryable和IEnumerable都是linq操作
            //IQueryable和IEnumerable都是延迟执行，tolist的时候在执行
            //只不过操作的位置不同，IQueryable是在数据库一侧操作，而IEnumerable实在应用程序的内存一侧操作，
            //IQueryable继承IEnumerable，IQueryable与IEnumerable 可以相互转换，IQueryable.AsIEnumerable,IEnumerable.AsIQueryable
            //var item = blogContext.Post.OrderBy(op => EF.Property<object>(op, "Title")).AsQueryable().Skip(1).Take(2);
            //IQueryable 在数据库中的 执行脚本，IQueryable和IEnumerable都是延迟执行，tolist的时候在执行
            //exec sp_executesql N'SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[PersonId], [p].[Title]
            //FROM[Post] AS[p]
            //ORDER BY[p].[Title]
            //OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=1,@__p_1=2

            //foreach (var it in item)
            //{
            //    Console.WriteLine($"it:{it.Title}");
            //}

            //var item = blogContext.Post.OrderBy(op => op.Title).AsEnumerable().Skip(1).Take(2);
            //IEnumerable在内存中的 执行脚本，IQueryable和IEnumerable都是延迟执行，tolist的时候在执行
            //SELECT[p].[PostId], [p].[BlogId], [p].[Content], [p].[PersonId], [p].[Title]
            //FROM[Post] AS[p]
            //ORDER BY[p].[Title]

            //foreach (var it in item)
            //{
            //    Console.WriteLine($"it:{it.Title}");
            //} 
            #endregion

            #region 实体跟踪Tracking
            //一种是全局修改之后在修改回来
            //另外一种是，是使用AsNoTracking()直接修改
            //blogContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
            ////using (IDbContextTransaction transaction = blogContext.Database.BeginTransaction())
            ////{
            //var item = blogContext.Post.Where(op => op.Title.Contains("10"));
            ////var item = blogContext.Post.AsNoTracking().Where(op => op.Title.Contains("10"));
            //foreach (var it in item)
            //    {
            //        Console.WriteLine($"it:{it.Title}");
            //        it.Title = "111";
            //    }
            //    blogContext.SaveChanges();
            ////    transaction.Commit();
            ////}
            //blogContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.TrackAll;
            #endregion

            #region 模糊查询StartsWith,,EndsWith,Contains,其中Contains和 EF.Functions.Like功能一样，但是sql语句翻译的确实不一样的
            ////但凡使用的计算函数，索引是一定不会被用上的
            //var item = blogContext.Post.Where(op => op.Title.Contains("10")).ToList();
            ////            SELECT[p].[PostId], [p].[BlogId], [p].[Content], [p].[PersonId], [p].[Title]
            ////            FROM[Post] AS[p]
            ////            WHERE CHARINDEX(N'10', [p].[Title]) > 0
            //foreach (var it in item)
            //{
            //    Console.WriteLine($"it:{it.Title}");
            //}
            //item = blogContext.Post.Where(op => EF.Functions.Like(op.Title, "%10%")).ToList();
            ////            SELECT[p].[PostId], [p].[BlogId], [p].[Content], [p].[PersonId], [p].[Title]
            ////            FROM[Post] AS[p]
            ////            WHERE[p].[Title]
            ////            LIKE N'%10%'
            //foreach (var it in item)
            //{
            //    Console.WriteLine($"it:{it.Title}");
            //}
            #endregion

            #region 自定义标量函数，linq一样可以翻译成对应的sql脚本
            ////            SELECT[p].[PostId] AS[id], [dbo].[MyFunction] ([p].[PostId]) AS[name]
            ////FROM[Post] AS[p]
            //var item = blogContext.Post.Select(op => new
            //{
            //    id = op.PostId,
            //    name = BlogContext.MyFunction(op.PostId)//使用标量函数
            //}).ToList();
            //foreach (var it in item)
            //{
            //    Console.WriteLine($"it:{it.name}");
            //}
            #endregion

            #region 编译查询

            #region 普通查询
            //int id = 1;
            //Stopwatch stopwatch = new Stopwatch();
            //stopwatch.Start();
            //var item = blogContext.Post.Where(op => op.PostId == id).FirstOrDefault();
            //stopwatch.Stop();
            //Console.WriteLine($"普通查询：item:{item.Title},用时：{stopwatch.ElapsedMilliseconds}");
            //            exec sp_executesql N'SELECT TOP(1) [p].[PostId], [p].[BlogId], [p].[Content], [p].[PersonId], [p].[Title]
            //FROM[Post] AS[p]
            //WHERE[p].[PostId] = @__id_0',N'@__id_0 int',@__id_0=1
            #endregion

            #region 编译查询
            //Stopwatch stopwatch = new Stopwatch();
            //stopwatch.Start();
            ////var compileQuery = EF.CompileQuery((BlogContext context, int id) => context.Post.Where(c => c.PostId == id).FirstOrDefault());
            ////var item = compileQuery(blogContext, 1);
            //stopwatch.Stop();
            //Console.WriteLine($"普通查询：item:{item.Title},用时：{stopwatch.ElapsedMilliseconds}");
            #endregion

            //普通查询

            Func<BlogContext, Blog> unCompileQuery = context => context.Blog.Include(c => c.Posts).Where(a => a.BlogId == 1).FirstOrDefault();
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            Console.WriteLine("普通查询开始");
            for (int i = 0; i < 100000; i++)
            {
                var a = unCompileQuery(blogContext);
            }
            stopwatch.Stop();
            Console.WriteLine($"编译查询 用时：{stopwatch.ElapsedMilliseconds} 毫秒");

            //编译查询 一般是大量数据查询，并且参数不变的情况下比较好，但是不能返回集合
            //Stopwatch stopwatch = new Stopwatch();
            //stopwatch.Start();
            //Console.WriteLine("编译查询开始");
            //var compileQuery = EF.CompileQuery((BlogContext context, int id) => context.Blog.Include(c => c.Posts).Where(a => a.BlogId == 1).FirstOrDefault());
            //for (int i = 0; i < 100000; i++)
            //{
            //    var item = compileQuery(blogContext, 1);
            //}
            //stopwatch.Stop();
            //Console.WriteLine($"编译查询 用时：{stopwatch.ElapsedMilliseconds} 毫秒");
            #endregion

            //var item = blogContext.Post.Where(op => op.Title.Contains("10"));
            //foreach (var it in item)
            //{
            //    Console.WriteLine($"it:{it.Title}");
            //}
            //var item = blogContext.Post.Where(op => EF.Functions.Like(op.Title,"10"));
            //foreach (var it in item)
            //{
            //    Console.WriteLine($"it:{it.Title}");
            //}

            Console.Read();

        }
    }

    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blog { get; set; }
        public DbSet<Post> Post { get; set; }

        public static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder => builder.AddConsole().AddDebug());
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //optionsBuilder.UseLoggerFactory(loggerFactory);//添加把数据库操作的信息输出到指定的控制台
            //optionsBuilder.UseLazyLoadingProxies().UseSqlServer(@"server=127.0.0.1;database=mynetcore;user=sa;password=sa123;");
            //Microsoft.EntityFrameworkCore.SqlServer
            optionsBuilder.UseSqlServer(@"server=127.0.0.1;database=mynetcore;user=sa;password=sa123;");
            //Pomelo.EntityFrameworkCore.MySql
            //optionsBuilder.UseMySql(@"server=127.0.0.1;database=mynetcore;user=root;password=123456;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            List<Post> posts = new List<Post>();
            for (int i = 0; i < 20; i++)
            {
                posts.Add(new Post { PostId = i + 1, PersonId = i >= 10 ? 2 : 1, Title = $"文章标题:100{i}", Content = $"文章内容:{i}", BlogId = i >= 10 ? 2 : 1 });
            }
            Blog blogs = new Blog { BlogId = 1, Url = "http://www.baidu.com" };
            Blog blogs1 = new Blog { BlogId = 2, Url = "http://www.tianmao.com" };
            Person person = new Person { PersonId = 1, Name = "123" };
            Person person1 = new Person { PersonId = 2, Name = "456" };
            modelBuilder.Entity<Post>().HasData(posts);
            modelBuilder.Entity<Blog>().HasData(blogs, blogs1);
            modelBuilder.Entity<Person>().HasData(person, person1);
        }

        #region 自定义标量函数
        [DbFunction]
        public static string MyFunction(int id)
        {
            throw new NotImplementedException();
        }
        #endregion
    }
    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
        public virtual 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 BlogId { get; set; }
        public virtual Blog Blog { get; set; }
        public int PersonId { get; set; }
        public virtual Person Author { get; set; }
    }
    public class Person
    {
        public int PersonId { get; set; }
        public string Name { get; set; }
        public virtual List<Post> Posts { get; set; }
    }
}