English
Checkpoint
What must work
powershell
dotnet buildpowershell
./Run.ps1 dbpowershell
dotnet ef migrations has-pending-model-changes --project Source/Chatex.DataCompare with the reference. The configurations, the context, the factory and the interceptor must be identical:
powershell
git diff --no-index Source/Chatex.Data/Configurations D:\Work\Chatex-Reference\Source\Chatex.Data\ConfigurationsFor the migration, compare the body of Up in your *_InitialCreate.cs with the reference file; only the timestamp in the file and class names may differ. DependencyInjection.cs is still shorter than the reference: the rest comes in topic 5.
Commit:
powershell
git add .powershell
git commit -m "Map the model to PostgreSQL with EF Core and add the initial migration"Common mistakes
Table 4.2. Common mistakes with EF Core and migrations
| Symptom | Cause | Fix |
|---|---|---|
Could not execute because the specified command or file was not found for dotnet ef | the local tool is not restored | dotnet tool restore |
Unable to create a 'DbContext' of type 'ChatexDbContext' | the design-time factory is missing or does not implement IDesignTimeDbContextFactory<ChatexDbContext> | add ChatexDbContextFactory |
The entity type 'ChatMember' requires a primary key to be defined | the composite key is not configured | builder.HasKey(m => new { m.ChatId, m.UserId }) in ChatMemberConfiguration |
| a configuration seems to be ignored | the class does not implement IEntityTypeConfiguration<T>, or ApplyConfigurationsFromAssembly is missing | check both |
tables are named AspNetUsers, asp_net_users | the ToTable calls are missing or come before base.OnModelCreating | rename after the base call, principal tables first |
columns are named LastActivityAt | UseSnakeCaseNamingConvention() is missing | add it in DependencyInjection.Configure; remove the migration and create it again |
Failed to connect to 127.0.0.1:5432 | PostgreSQL is not running | docker compose up --detach --wait |
has-pending-model-changes reports changes right after creating the migration | the project was changed after the migration, or the migration was edited by hand | remove the last migration (dotnet ef migrations remove) and add it again |
Self-check questions
- What does an ORM do? Which parts of EF Core are general, and which come from the provider?
- Why does
ChatexDbContextderive fromIdentityDbContext<User, IdentityRole<Guid>, Guid>? What do the three type arguments mean? - Why does a
DbContextreceive its options through the constructor instead of configuring itself? - In which three ways does EF Core learn the model, and which one wins?
- Why should every string column have a maximum length?
- How does the check constraint
ck_chats_direct_keywork? Why is it written withtypeanddirect_key, notTypeandDirectKey? - Why can many private chats have
public_handle = NULLalthough the column has a unique index? - Which query does the index
(chat_id, id DESC)serve, and why is it fast for any page of the history? - Explain the delete rules of
Message.SenderId,Message.ReplyToMessageIdandChatMember.UserId. - Why are the timestamps set by an interceptor? Why does it take a
TimeProvider? - Why do the application and the design-time factory share
DependencyInjection.Configure? - What is stored in the migration, the designer file and the snapshot? Why must a generated migration be read before it is committed?
- What does
has-pending-model-changesprotect against?
Exercises
Look at the SQL. Run
dotnet ef migrations script --project Source/Chatex.Dataand read the SQL thatdatabase updateexecuted. Find the check constraint and the indexix_messages_chat_id_id.A second migration. Add the property
string? WebsitetoUserProfileand give it a maximum length of 200 inUserProfileConfiguration. Create a migrationAddProfileWebsite, read it, apply it, check the column with\d user_profiles. Then roll back withdotnet ef database update InitialCreate --project Source/Chatex.Data, remove the migration withdotnet ef migrations remove --project Source/Chatex.Dataand the property. Why must the database be rolled back before the migration is removed?The query plan. In
psql, ask PostgreSQL how it would run a history query:sqlexplain select * from messages where chat_id = '01a1e0ce-526e-742b-8216-1fab814b067a' and id < '01a1e0ce-526e-742b-8216-1fab814b0670' order by id desc limit 50;On an empty table it reads the table directly (
Seq Scan), because that is cheaper than an index. Runset enable_seqscan = off;first, explain again and find the index in the plan (Index Scan using ix_messages_chat_id_id). Why does the plan need noSortstep?