data-access-ef-core
How the repository reads and writes the SQLite database with EF Core - scoped DbContext, no tracking by default, SQL projections for lists, set-based updates and deletes, and safe LIKE searches. Use when adding or changing a repository method or a query.
LeanMeanCleanApps · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add LeanMeanCleanApps/CleanMinimalApi --skill data-access-ef-core技能说明
name: data-access-ef-core description: How the repository reads and writes the SQLite database with EF Core - scoped DbContext, no tracking by default, SQL projections for lists, set-based updates and deletes, and safe LIKE searches. Use when adding or changing a repository method or a query. metadata: scope: shared
Data access with EF Core
The rule
One repository, EntityFrameworkMovieReviewsRepository in src/Infrastructure/Databases/MovieReviews/, implements every repository interface from Application. It and MovieReviewsDbContext are scoped: one per request, never shared between threads. The context doesn't track queries (QueryTrackingBehavior.NoTracking in AddInfrastructure), because nothing loads an entity to change it. Lists are projected in SQL. Updates and deletes are set-based and are their own existence check. The repository returns Application entities, never database models.
Steps
- Add the method to the Application interface first (
IMoviesRepositoryand friends), taking aCancellationTokenlast. - Implement it in the matching
#regionof the repository, aspublic async Task<...>like every other method. - Reads: start from
this.context.<Set>, filter, then either- a list: take a
PageRequest page, count, order (ending with the id),Skip(page.Offset),Take(page.Limit),Selectthe summary fields so SQL does the counting and averaging, and returnnew Page<T>(items, page.Offset, page.Limit, totalCount)(paging), or - one entity:
Includewhat the entity needs,FirstOrDefaultAsync, then map withthis.mapper.ToDomain(...)(mapping-with-mapperly).
- a list: take a
- Writes:
- create: build the model, set
DateCreatedandDateModifiedfromthis.timeProvider.GetUtcNow().UtcDateTime,Add,SaveChangesAsync, then read it back with its includes. - update:
Where(...).ExecuteUpdateAsync(setters => ...), always settingDateModified. No rows changed means not found, so returnnull; otherwise read the entity back and return it. - delete:
Where(...).ExecuteDeleteAsync(...), returning whether a row was deleted. - Don't check first. The handler turns
nullorfalseintoNotFoundException(errors-and-exceptions). - New rows get a version 7 GUID from
Version7GuidValueGenerator, set up for every model inEntityConfiguration<T>. Each model needs its own configuration class, even an empty one, or it gets random GUIDs.
- create: build the model, set
- Text search uses
EF.Functions.Likewith theContainsPatternhelper, which escapes%,_and\. - Add repository tests (
integration-tests).
Example
// src/Infrastructure/Databases/MovieReviews/EntityFrameworkMovieReviewsRepository.cs
public async Task<ApplicationReview?> UpdateReview(Guid id, int stars, CancellationToken cancellationToken)
{
var now = this.timeProvider.GetUtcNow().UtcDateTime;
// The update is the existence check: no rows changed means there's no such review.
var updated = await this.context.Reviews
.Where(r => r.Id == id)
.ExecuteUpdateAsync(
setters => setters
.SetProperty(r => r.Stars, stars)
.SetProperty(r => r.DateModified, now),
cancellationToken);
return updated == 0 ? null : await this.GetReviewById(id, cancellationToken);
}
if (!string.IsNullOrWhiteSpace(query))
{
var pattern = ContainsPattern(query);
movies = movies.Where(m => EF.Functions.Like(m.Title, pattern, EscapeCharacter));
}
Don't
- Register the
DbContextor repository as a singleton. A shared context fails under concurrent requests. - Use
string.Contains(value, StringComparison.OrdinalIgnoreCase)in a query. EF Core can't translate it; use theLIKEhelper. - Use
DateTime.UtcNow. Use the injectedTimeProviderso tests can fix the clock. Includechildren on a list query to count them.- Load an entity, change it and
SaveChangesto update one column. UseExecuteUpdateAsync. - Add
AsNoTracking()to a query. It's already the default. - Call an
Existsmethod before an update or delete. - Return a database model or
IQueryablefrom the repository.
Check
dotnet test tests/Infrastructure.Tests.Integration