Several EF Core Performance Optimizations to Make Your Program Run Swiftly

1. Using EF.Functions.xxx for Queries

1.1 Using EF.Functions.Like for fuzzy queries generates more performant SQL than StartsWith, Contains, and EndsWith methods.

1. Contains statement generates SQL using charindex:

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

Uses charindex

2. EF.Functions.Like statement generates SQL using Like (Like combined with SQL wildcards):

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

Uses Like

PS: In traditional .NET, there is also the SqlMethods usage. See: https://www.cnblogs.com/yaopengfei/p/11805980.html

1.2. Also EF.Functions.DateDiffDay (DateDiffHour, DateDiffMonth) to get the count of days, hours, or months.

PS: In EF Core, StartsWith, Contains, and EndsWith fuzzy queries resolve to Left, CharIndex, and Right respectively, not Like. However, EF.Functions.Like resolves to a Like statement.

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

2. Adding Z.EntityFramework.Plus.EFCore Dependency for Special Syntax

This is free, but some bulk data operation packages of Z.EntityFramework.Plus are paid.

1. EF Core requires querying before deletion; optimized direct deletion:

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

2. Optimized update statement:

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

3. Use Find(id=10) Instead of FirstOrDefault(t=>t.id=10) Correctly

Find prioritizes querying the cache. Use it when this data has been queried before. FirstOrDefault queries the database every time. When data with id=10 is modified, Find retrieves the new data.

4. Disable Entity Tracking

When querying data from the database, the context creates entity snapshots and tracks entities. When SaveChanges is called, any changes to entities are saved to the database.

But when we only need to query entities without modification (read-only), entity tracking is not useful. In this case, we can call AsNoTracking to get non-tracking data, which improves query performance. The code is as follows:

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

Note: For multi-table queries, you can set before querying:

db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

This sets all table queries to non-tracking mode.

5. When Checking If a Query Result List Has Values, Use .Any() Instead of .Count() or .FirstOrDefault()