定制ASP.NET Dynamic Data：300张表CRUD、审计与EDMX免重建需求咨询

Hey there! Let's break down your scenario and tackle each of your questions and requirements—managing 300 frequently changing tables is no small feat, so we need solutions that balance flexibility and maintainability.

1. Is this the best way to build an ASP site for CRUD on 300 frequently changing tables? #

Your current Dynamic Data + EF setup is a solid starting point, but its biggest pain point (needing to rebuild the EDMX on schema changes) makes it less ideal for a dynamic table set. Here's how to frame the options:

• Dynamic Data is still viable if you replace the static EDMX with dynamic model building (more on that later). It’s great for auto-generating CRUD UI without writing 300 controllers/views.
• MVC with handwritten controllers/views is not practical (as you found) because maintaining 300+ controllers for changing tables is unsustainable.
• EF Core with dynamic model building is a better modern alternative than EF6 for this scenario—it has more flexible model-building APIs that let you generate models at runtime from database schema.
• Low-code/no-code platforms could work, but if you need to stick with ASP.NET, dynamic model + Dynamic Data (or a lightweight dynamic UI library) is the sweet spot.

2. Best approach to meet your new requirements #

Let’s tackle each requirement one by one, with practical, maintainable solutions:

a. Audit Windows user CRUD operations #

Use EF’s interception capabilities to track changes without modifying every table’s code:

• For EF6: Implement IDbCommandInterceptor or override DbContext.SaveChanges() to capture the ChangeTracker state.
• For EF Core: Use SaveChangesInterceptor (cleaner and more powerful).
• What to log:
  • Windows user: Grab it via System.Security.Principal.WindowsIdentity.GetCurrent().Name (ensure your site uses Windows Authentication).
  • Table name: Pull it from the entity’s metadata (e.g., entityEntry.Metadata.GetTableName() in EF Core).
  • Change details: Compare OriginalValues and CurrentValues in the ChangeTracker to capture which fields changed, old vs new values.
  • Operation type: Detect if it’s Create (entity is Added), Update (Modified), or Delete (Deleted).
• Store logs: Create a dedicated EDT.AuditLogs table to store all audit records—make sure this table is read-only to users except admins.

b. Make foreign key tables read-only #

First, define which tables are "foreign key tables" (tables referenced by other tables’ foreign keys). Then:

• In Dynamic Data templates: Modify the List/Edit/Details templates to hide edit/delete/create buttons if the current table is marked as a foreign key table.
• Check via database metadata: Query INFORMATION_SCHEMA.KEY_COLUMN_USAGE to find tables that are referenced by foreign keys in other tables. Cache this list on app startup to avoid repeated database hits.
• Optional: Add a configuration flag: If some tables should be read-only regardless of foreign keys, add a config file or a small settings table to mark tables as read-only.

c. Foreign key dropdowns: Read-only if target table is in dbo schema #

Modify the Dynamic Data foreign key field template (e.g., ForeignKey_Edit.ascx for Web Forms, or the corresponding Razor component for MVC):

• Get the target table’s schema: Use EF metadata to find the foreign key’s referenced table (e.g., in EF Core, foreignKey.ReferencedEntityType.GetSchema()).
• Switch control type: If the schema is dbo, render a read-only label showing the current value instead of a dropdown. If it’s EDT, keep the dropdown for selection.

d. Avoid frequent model rebuilds #

The key here is to ditch static EDMX files and build your EF model dynamically at runtime:

• EF6: Use DbModelBuilder to iterate over all tables in the EDT schema (query INFORMATION_SCHEMA.TABLES), create entity types and DbSet definitions dynamically, then build a DbCompiledModel to initialize your DbContext.
• EF Core: Even easier—use modelBuilder.Entity() with reflection or metadata to generate entities on the fly. You can also use the reverse-engineer approach with an automated script (e.g., a PowerShell script that runs Scaffold-DbContext with -Force to regenerate entity classes when schema changes), but dynamic runtime building avoids code generation entirely.
• Cache the dynamic model: To avoid performance hits on every request, cache the compiled model after the first build (in memory or a distributed cache if scaling).

3. Is there a way to avoid rebuilding the EDMX file? #

Absolutely! Here are your two main options:

• Dynamic model building (as mentioned above): For both EF6 and EF Core, you can construct the model in code at runtime by reading the database schema directly. This means schema changes are picked up automatically when the app restarts (or you can add a "refresh model" endpoint for admins to trigger without restarting).
• Automated EDMX regeneration: If you prefer to stick with EDMX, write a script (PowerShell or CLI) that runs the Update Model from Database command programmatically. You can trigger this script manually when schema changes, or set up a CI/CD pipeline to run it on database schema updates. However, dynamic model building is more scalable for 300 tables.

Quick Pro Tips #

• Performance: Caching the dynamic model and audit log batch inserts will help keep your app responsive with 300 tables.
• Testing: Set up a staging environment with a copy of your production schema to test dynamic model builds and audit logic before deploying.
• Maintenance: Document your dynamic model building logic and audit configuration—this will make it easier to tweak as your schema evolves.

内容的提问来源于stack exchange，提问作者DIG