π Documentation: 20230822130112_AddAuditTable.cs
π Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Class Breakdown
- Schema Changes
- Using the Migration
- UML Diagram
- Sample Code Snippets
- Conclusion
1. Overview & Purpose
π What is this file?
- This C# class is an Entity Framework Core Migration named
AddAuditTable. - It adds a new table called AuditDetail to the database schema
irby.
π Why do we need it?
- Audit logging: Track user activitiesβlogin status, page accessed, IP, etc.
- Accountability: Store historical data for compliance, debugging, and analytics.
2. Dependencies & Technologies
| Technology / Package | Version | Purpose |
|---|---|---|
| Microsoft.EntityFrameworkCore | x.y.z |
ORM for migrations & data access |
| Microsoft.EntityFrameworkCore.Tools | x.y.z |
CLI tools for Add-Migration & Update-Database |
| .NET 6 / .NET 7 | >=6.0 |
Runtime for the EF Core migration project |
3. Migration Class Breakdown
public partial class AddAuditTable : Migration
{
protected override void Up(MigrationBuilder migrationBuilder) { /* β¦ */ }
protected override void Down(MigrationBuilder migrationBuilder) { /* β¦ */ }
}
Up: Defines operations to apply this migration (i.e., create the table).Down: Reverts those operations (i.e., drops the table) if you roll back.
4. Schema Changes
4.1 AuditDetail Table
- Schema:
irby - Table:
AuditDetail
4.2 Columns & Data Types
| Column Name | CLR Type | SQL Type | Nullable | Description |
|---|---|---|---|---|
| AuditDetailId | Guid |
uniqueidentifier |
No | PK, unique ID for each audit record |
| Area | string |
nvarchar(max) |
Yes | MVC Area name |
| ControllerName | string |
nvarchar(max) |
Yes | Name of the controller handling the request |
| ActionName | string |
nvarchar(max) |
Yes | Action method invoked |
| Namespace | string |
nvarchar(max) |
Yes | Fully qualified namespace |
| UserId | Guid |
uniqueidentifier |
No | ID of the authenticated user |
| RoleId | Guid? |
uniqueidentifier |
Yes | FK to Role |
| EmployeeId | Guid? |
uniqueidentifier |
Yes | FK to Employee |
| LangId | string |
nvarchar(max) |
Yes | Culture / Language ID |
| IpAddress | string |
nvarchar(max) |
Yes | Client IP address |
| IsFirstLogin | string |
nvarchar(max) |
Yes | Flag for first-time login |
| ActivityDate | DateTime |
datetime2 |
No | Timestamp of the activity |
| LoginStatus | string |
nvarchar(max) |
Yes | e.g., Success / Failure |
| PageAccessed | string |
nvarchar(max) |
Yes | URI or page name accessed |
| SessionId | string |
nvarchar(max) |
Yes | Session identifier |
| UrlReferrer | string |
nvarchar(max) |
Yes | Referring URL |
| DocumentImageId | Guid? |
uniqueidentifier |
Yes | FK or link to a document image |
4.3 Constraints & Indexes
- Primary Key
CONSTRAINT PK_AuditDetail PRIMARY KEY (AuditDetailId) - Foreign Keys
EmployeeIdβirby.Employee(EmployeeId)RoleIdβirby.Role(RoleId)
- Indexes
IX_AuditDetail_EmployeeIdIX_AuditDetail_RoleId
5. Using the Migration
5.1 Generating & Applying
- Create migration (if you were writing it manually):
dotnet ef migrations add AddAuditTable --schema irby - Apply to database:
dotnet ef database update
5.2 Querying Audit Data
using var context = new IrbyDbContext();
var recentLogins = context.AuditDetails
.Where(a => a.LoginStatus == "Success" && a.ActivityDate >= DateTime.UtcNow.AddDays(-7))
.OrderByDescending(a => a.ActivityDate)
.ToList();
6. UML Diagram
+---------------------+
| AuditDetail |
+---------------------+
| - AuditDetailId PK |
| - Area |
| - ControllerName |
| - ActionName |
| - Namespace |
| - UserId |
| - RoleId FK ββ> Role.RoleId
| - EmployeeId FK ββ> Employee.EmployeeId
| - LangId |
| - IpAddress |
| - IsFirstLogin |
| - ActivityDate |
| - LoginStatus |
| - PageAccessed |
| - SessionId |
| - UrlReferrer |
| - DocumentImageId |
+---------------------+
7. Sample Code Snippets
Adding an Audit Entry
var audit = new AuditDetail {
AuditDetailId = Guid.NewGuid(),
UserId = currentUserId,
RoleId = currentRoleId,
EmployeeId = employeeId,
ActionName = "Login",
ControllerName= "Account",
ActivityDate = DateTime.UtcNow,
IpAddress = HttpContext.Connection.RemoteIpAddress.ToString(),
LoginStatus = loginSuccess ? "Success" : "Failure"
};
context.AuditDetails.Add(audit);
context.SaveChanges();
Rolling Back the Migration
# Reverts one migration (i.e., drops AuditDetail)
dotnet ef database update LastGoodMigrationName
8. Conclusion
π‘ What did we achieve?
- Introduced a comprehensive AuditDetail table to track user activity.
- Provided schema definitions, constraints, and indexes.
- Ensured seamless integration with EF Coreβs migration pipeline.
π Next Steps
- Seed initial data if needed.
- Extend queries and reporting layers to consume audit logs.
- Secure sensitive data (e.g., IP addresses) per compliance policies.
Generated by the IrbyImaging.DbContext Team π