EF Core Practical Guide: DbContext Configuration, Threading, Connection Pooling, Logging, and CRUD Operations

Introduction

.NET has steadily evolved, and interacting with databases is essential in development. Early developers used raw DbHelper, while modern projects favor various ORM frameworks. This guide shares practical tips and best‑practice snippets for working with EF Core.

1. DbContext Configuration

In ASP.NET, EF services are typically added in Startup.ConfigureServices :

services.AddDbContext<DemoDbContext>(opt => opt.UseMySql("server=.;Database=demo;Uid=root;Pwd=123;Port=3306;"));

Replace UseMySql with UseSqlServer or other providers as needed, after installing the corresponding EF Core provider package. The UseMySql overload also accepts an optional MySqlDbContextOptionsBuilder for custom settings such as retry policies:

services.AddDbContext<DemoDbContext>(opt => opt.UseMySql("server=.;Database=demo;Uid=root;Pwd=123456;Port=3306;", provideropt => provideropt.EnableRetryOnFailure(3, TimeSpan.FromSeconds(10), new List<int>(){0})));

This retry mechanism helps mitigate transient network issues or spikes in load.

2. DbContext Threading Issues

EF Core does not support concurrent operations on the same DbContext instance. Attempting parallel async queries or using the context from multiple threads throws an InvalidOperationException with a message like:

A second operation started on this context before a previous operation completed. Any instance members are not guaranteed to be thread safe.

Always await async calls, or create a separate DbContext per thread.

3. Using DbContext Pooling

Registering the context with services.AddDbContextPool<DemoDbContext>() can improve throughput by 10‑20% compared with AddDbContext . The pool size should be tuned; larger is not always better.

4. Logging SQL Statements

EF Core 2.x injects a logger by default, allowing you to see the generated SQL. Starting with EF Core 3.x the default logger is removed; you must configure it manually:

public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); });
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    base.OnConfiguring(optionsBuilder);
    optionsBuilder.UseLoggerFactory(MyLoggerFactory);
}

Running the code prints the SQL to the console.

5. Insertion Techniques

Common insertion scenarios include single‑row, cascade (one‑to‑many), and bulk inserts.

Single insert :

var student = new Student { CreateTime = DateTime.Now, Name = "zjjjjjj" };
await _context.Students.AddAsync(student);
await _context.SaveChangesAsync();

Cascade insert (Blog → Posts):

[Table("blog")]
public class Blog
{
    [Column("id")]
    public long Id { get; set; }
    [Column("title")]
    public string Title { get; set; }
    public List
Posts { get; set; }
    [Column("create_date")]
    public DateTime CreateDate { get; set; }
}

var blog = new Blog
{
    Title = "测试标题",
    Posts = new List
{
        new Post { Content = "评论1" },
        new Post { Content = "评论2" },
        new Post { Content = "评论3" }
    }
};
await _context.Blog.AddAsync(blog);
await _context.SaveChangesAsync();

Bulk insert – EF default (suitable for small batches) or a custom bulk service (e.g., ISqlBulk ) for >1000 rows. Example using EF default:

var list = new List
();
for (int i = 0; i < num; i++)
{
    list.Add(new Student { CreateTime = DateTime.Now, Name = "zjjjjjj" });
}
await _context.Students.AddRangeAsync(list);
await _context.SaveChangesAsync();

Using a bulk library:

var list = new List
();
for (int i = 0; i < 100000; i++)
{
    list.Add(new Student { CreateTime = DateTime.Now, Name = "zjjjjjj" });
}
await _bulk.InsertAsync(list);

6. Primary Key Choice

For small datasets, an auto‑increment int key is efficient (small storage, fast joins). For large or distributed data, an ordered GUID is preferable; unordered GUIDs degrade insert performance because they break clustering.

7. Querying

EF translates LINQ to SQL. Example conditional query:

await _context.Blog.Where(x => x.Id > 0).ToListAsync();

Generated SQL:

SELECT `x`.`id`, `x`.`create_date`, `x`.`title`
FROM `blog` AS `x`
WHERE `x`.`id` > 0

Single entity retrieval can use First/FirstOrDefault (LIMIT 1) or Single/SingleOrDefault (LIMIT 2) – the latter throws if more than one row matches.

Existence checks: AnyAsync is more efficient than CountAsync on large tables.

8. Joins

Inner join example:

var query = from post in _context.Post
            join blog in _context.Blog on post.BlogId equals blog.Id
            where blog.Id > 0
            select new { blog, post };

Left join (using DefaultIfEmpty ) example is also provided.

9. Include (Eager Loading)

Load related collections with Include and optionally filter them:

var result = await _context.Blog
    .Include(b => b.Posts)
    .SingleOrDefaultAsync(x => x.Id == 157);

var filteredBlogs = await _context.Blogs
    .Include(blog => blog.Posts.Where(post => post.BlogId == 1))
    .OrderByDescending(blog => blog.Title)
    .Take(5)
    .ToListAsync();

10. Updating Entities

If the entity was retrieved via a tracking query, simply modify its properties and call SaveChangesAsync . EF will generate an UPDATE that touches only changed columns. Calling Update explicitly forces all columns to be written, which can hurt performance.

var school = await _context.Schools.FirstAsync(x => x.Id > 0);
school.Name = "6666";
await _context.SaveChangesAsync();

When the entity is not tracked, use Update before SaveChangesAsync .

— 福禄ICH.架构出品 Author: 福尔斯 August 2020