Several EF core performance optimizations will make your program move quickly

1. Use EF.Functions.xxx to make inquiries

1.1 Using EF.Functions.Like for fuzzy queries provides better performance than SQL statements generated by the StartsWith, Contains, and EndsWith methods.

1. Contains statement, and the generated sql is:

var data3 = dbContext.T_UserInfor.Where(u => u.userName.Contains("p")).ToList();

Use charindex

2. EF.Functions.Like statement generates sql as: (Like used with wildcard of SQL query)

var data1 = dbContext.T_UserInfor.Where(u => EF.Functions.Like(u.userName, "%p%")).ToList();
//或者
var data2 = (from p in dbContext.T_UserInfor
              where EF.Functions.Like(p.userName, "%p%")
              select p).ToList();

It was Like.

PS：在传统的.Net 中，还有种用法 SqlMethods，详见：https://www.cnblogs.com/yaopengfei/p/11805980.html

1.2. Also EF.Functions.DateDiffDay (DateDiffHour, DateDiffMonth), find the number of days, hours, and months

PS: In EF Core, StartsWith, Contains, and EndsWith fuzzy queries are actually parsed as Left, CharIndex, and Right, respectively, rather than Like, while EF.Functions.Like is parsed as a Like statement.

详见：https://www.cnblogs.com/tdfblog/p/entity-framework-core-like-query.html

2. Add Z.EntityFramework.Plus.EFCore dependencies to use some special syntax

This is free, but some packages for bulk data manipulation in Z.EntityFramework.Plus charge for a fee

1. EFCore deletion must be queried first and then deleted. After optimization, it can be deleted directly:

context.User.Where(t => t.Id == 100).Delete();

2. Optimize update statements:

context.User.Where(t => t.Id == 4).Update(t =>new User() { NickName = "2224114" ,Phone = "1234"} );

3. Properly use Find(id=10) instead of FirstOrDefault(t=> t.id =10)

Find will give priority to querying the cache and use it when this data has been queried before, while FirstOrDefault will query the database every time; when the data with id=10 is modified, the data found by find is new data.

4. Disable physical tracking

When we query data from the database, the context creates a snapshot of the entity to track the entity. When SaveChanges is called, any changes to the entity are saved to the database.

But when we only need to query entities without modification (read-only), entity tracking has no use. At this time, we can call AsNoTracking to obtain non-tracked data, which can improve query performance. The specific code is as follows:

var users = db.Users.AsNoTracking().ToList();

** Note: If it is a multi-table query, you can search it before query **

db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

This sets all table queries to non-tracking state

5. When determining whether the queried list has a value, use.Any(), and try not to use.Count(); .FirstOrDefault()