EntityFramework性能优化建议（Performance tips）

Change Tracking

EF Core查询数据库的时候，在内存中存储了结果数据集的闪照，任何对代码中实体的改变都首先与内存中的数据集对比，然后才会被写入数据库中。
在只读的场景中，不需要将数据写回数据库，因此不需要将数据集存到内存中，此时可以放弃使用该策略以节省系统资源。如何做呢？只需要在查询表后面添加AsNoTracking()方法即可。

Eager Loading

Eager Loading: Eager Loading helps you to load all your needed entities at once. i.e. related objects (child objects) are loaded automatically with its parent object.
When to use:

1. Use Eager Loading when the relations are not too much. Thus, Eager Loading is a good practice to reduce further queries on the Server.
2. Use Eager Loading when you are sure that you will be using related entities with the main entity everywhere.

How to use:
在查询表的后面使用include方法即可调用Eager Loading。

上面将Order表也列入查询结果了，可以自己试验一下，用MySQL的World表：

        static void Main(string[] args)
        {
            using var context = new ContosoPetsContext();

            var c = context.Country.Include(c => c.City).Where(c => c.Name == "China").FirstOrDefault();

            var capital = c.City.Where(city => city.Id == c.Capital).Select(city => city.Name).SingleOrDefault();

            Console.WriteLine($"Country Name:{c.Name}");
            Console.WriteLine($"Country Capital:{capital}");
        }

c中就包含了city表的查询结果，它会将city.countrycode与c.code比对，然后查询出与这些国家相对应的城市列表，上面的例子中就是查到了中国相关的城市，并且再进一步查询中国的首都。

Lazy Loading

**Lazy Loading: **Lazy loading is delaying the loading of related data, until you specifically request for it. It is the opposite of eager loading. For example, the Student entity contains the StudentAddress entity. In the lazy loading, the context first loads the Student entity data from the database, then it will load the StudentAddress entity when we access the StudentAddress property as shown below
When to use:

1. Use Lazy Loading when you are using one-to-many collections.
2. Use Lazy Loading when you are sure that you are not using related entities instantly.

How to use:
首先，添加Proxies包：

然后在构建数据库连接的地方对options使用UseLazyLoadingProxies()方法：

或者在ASP.NET Core中

第三步，确保与之相关联的表的实体属性可以被重写（virtual），如Countrys关联的Citys表：

最后，就可以愉快的进行查询了，如查询中国的所有城市，我可以先查询到中国，但是中国的城市我先不用，而是做一些其他的事情，当我需要的时候，我再打印出中国的所有城市：

        static void Main(string[] args)
        {
            using var context = new ContosoPetsContext();

            var myCountry = context.Country.Where(c => c.Name == "China").FirstOrDefault();

            Console.WriteLine("Do something...");

            Console.WriteLine("Now I want to use citys table.");

            var citys = myCountry.City;

            foreach (var city in citys)
            {
                Console.WriteLine(city.Name);
            }
        }

在代码中使用自己写的SQL语句

SQL raw

例如查询首都编码为1891的国家的相关信息：

        static void Main(string[] args)
        {
            using var context = new ContosoPetsContext();

            var myCountry = context.Country.FromSqlRaw("Select * From world.country Where Capital = 1891").ToList();
            Console.WriteLine(myCountry[0].Name);
        }

SQL interpolated

例如查询首都编码为1891的国家的相关信息：

        static void Main(string[] args)
        {
            using var context = new ContosoPetsContext();

            int capitalCode = 1891;
            var myCountry = context.Country.FromSqlInterpolated($"Select * From world.country Where Capital = {capitalCode}").ToList();
			Console.WriteLine(myCountry[0].Name);
        }

database context pooling

使用数据库池可以避免重复的创建和销毁context对象，而是可以重复使用它。
在ASP.NET Core中：

（具体使用可以参考文献3）

参考文献

1. https://stackoverflow.com/questions/31366236/lazy-loading-vs-eager-loading
2. Entity Framework Core 101 video series with .NET Core and ASP.NET Core（B站可看，搜索Entity Framework Core 101）
3. https://neelbhatt.com/2018/02/27/use-dbcontextpooling-to-improve-the-performance-net-core-2-1-feature/

及时获取我发的文章，学习更多编程知识欢迎关注我的个人微信公众号：