Entity Framework Code First：如何关联只读CODE_YESNO表？

Got it, let's walk through this step by step using Entity Framework Code First. The goal is to create the CODE_YESNO lookup table, link the userIsActive (from USERS) and productIsOnDiscount (from PRODUCTS) fields to it as foreign keys, and ensure we never modify the lookup table once it's set up.

1. Define the CODE_YESNO Entity #

First, create the entity class for your lookup table. We'll use data annotations to enforce field lengths and mark the primary key:

public class CODE_YESNO
{
    // Primary key: 1-character string
    [Key]
    [StringLength(1)]
    public string C_CODE { get; set; }

    // Description: max 5 characters
    [StringLength(5)]
    public string C_DESC { get; set; }
}

2. Update USERS and PRODUCTS Entities #

Your existing USERS and PRODUCTS tables need their status fields converted to string types (to match C_CODE in CODE_YESNO). Add optional (but recommended) navigation properties for relational operations:

USERS Entity #

public class USERS
{
    // Your existing fields (e.g., userId, userName)
    public int userId { get; set; }
    public string userName { get; set; }

    // Convert userIsActive to string (matches C_CODE length)
    [StringLength(1)]
    public string userIsActive { get; set; }

    // Navigation property to CODE_YESNO
    public virtual CODE_YESNO ActiveStatus { get; set; }
}

PRODUCTS Entity #

public class PRODUCTS
{
    // Your existing fields (e.g., productId, productName)
    public int productId { get; set; }
    public string productName { get; set; }

    // Convert productIsOnDiscount to string
    [StringLength(1)]
    public string productIsOnDiscount { get; set; }

    // Navigation property to CODE_YESNO
    public virtual CODE_YESNO DiscountStatus { get; set; }
}

3. Configure Relationships and Lock Down CODE_YESNO in DbContext #

In your DbContext class, use Fluent API to define foreign key relationships and ensure CODE_YESNO isn't modified by future migrations:

public class AppDbContext : DbContext
{
    public DbSet<CODE_YESNO> CODE_YESNO { get; set; }
    public DbSet<USERS> USERS { get; set; }
    public DbSet<PRODUCTS> PRODUCTS { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure CODE_YESNO constraints and exclude from migrations
        modelBuilder.Entity<CODE_YESNO>()
            .HasKey(y => y.C_CODE);
        
        modelBuilder.Entity<CODE_YESNO>()
            .Property(y => y.C_CODE)
            .HasMaxLength(1)
            .IsRequired();
        
        modelBuilder.Entity<CODE_YESNO>()
            .Property(y => y.C_DESC)
            .HasMaxLength(5);

        // Ensure CODE_YESNO is untouched by future migrations (EF Core only)
        modelBuilder.Entity<CODE_YESNO>()
            .ToTable(t => t.ExcludeFromMigrations());

        // Seed initial Yes/No data for the lookup table
        modelBuilder.Entity<CODE_YESNO>()
            .HasData(
                new CODE_YESNO { C_CODE = "Y", C_DESC = "Yes" },
                new CODE_YESNO { C_CODE = "N", C_DESC = "No" }
            );

        // Link USERS.userIsActive to CODE_YESNO.C_CODE
        modelBuilder.Entity<USERS>()
            .HasOne(u => u.ActiveStatus)
            .WithMany() // One status applies to multiple users
            .HasForeignKey(u => u.userIsActive)
            .OnDelete(DeleteBehavior.Restrict); // Block deletion of in-use lookup records

        // Link PRODUCTS.productIsOnDiscount to CODE_YESNO.C_CODE
        modelBuilder.Entity<PRODUCTS>()
            .HasOne(p => p.DiscountStatus)
            .WithMany() // One status applies to multiple products
            .HasForeignKey(p => p.productIsOnDiscount)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

4. Handle Existing Data (If Applicable) #

If your USERS/PRODUCTS tables already have data, convert boolean values to match C_CODE (e.g., 1 → "Y", 0 → "N"). When generating migrations, you may need to manually adjust the script to include this conversion first:

-- Example for USERS table
UPDATE USERS SET userIsActive = CASE WHEN userIsActive = 1 THEN 'Y' ELSE 'N' END;
ALTER TABLE USERS ALTER COLUMN userIsActive VARCHAR(1) NOT NULL;
ALTER TABLE USERS ADD CONSTRAINT FK_USERS_CODE_YESNO FOREIGN KEY (userIsActive) REFERENCES CODE_YESNO(C_CODE);

5. Generate and Apply Migrations #

Run these commands in the Package Manager Console to create and apply the migration:

Add-Migration InitialLookupTableSetup
Update-Database

After this, CODE_YESNO will be created with seed data, and foreign keys will link the status fields. The ExcludeFromMigrations() setting ensures future changes to the CODE_YESNO entity won't affect the database table.

内容的提问来源于stack exchange，提问作者Asım Gündüz