PostgreSQL Character类型与.NET Core 2.0映射及RFC主键存储咨询

Hey there! Let's tackle your two main questions—mapping PostgreSQL character types to .NET Core 2.0 with Npgsql, and properly setting up that RFC column as your primary key.

Npgsql has straightforward, intuitive mappings for PostgreSQL's character types when working with .NET Core 2.0:

• character(n) (fixed-length character string): Maps directly to .NET's string type by default. This is exactly what you need for your RFC column, since RFC follows a strict fixed 13-character format. Npgsql handles the fixed-length behavior automatically—just ensure your input strings are exactly 13 characters long (PostgreSQL will pad shorter values with spaces, but you’ll want to avoid that with pre-validation).
• For single fixed-length characters, you could use NpgsqlTypes.NpgsqlChar, but that’s irrelevant here given your 13-length RFC requirement.

Your choice of character(13) is perfect for storing RFC codes, as they adhere to the AAAXXXXXXAXX structure (A = letter, X = digit). Here’s a cleaned-up, production-ready SQL snippet based on pgAdmin4’s output:

CREATE TABLE your_table_name (
    rfc character(13) COLLATE pg_catalog."default" NOT NULL PRIMARY KEY,
    -- Add your other table columns here
);

To make this setup more robust, add validation at both the database and application levels:

• Database-side validation: Add a CHECK constraint to enforce the RFC format, so invalid values can’t even be inserted into the table:

  ALTER TABLE your_table_name
  ADD CONSTRAINT chk_rfc_format 
  CHECK (rfc ~ '^[A-Z]{3}\d{6}[A-Z0-9]{3}$');
  

  This regex ensures the first 3 characters are letters, the next 6 are digits, and the final 3 are either letters or digits—perfectly matching the RFC structure.
• Application-side validation: Use a .NET regex (like @"^[A-Z]{3}\d{6}[A-Z0-9]{3}$") to validate input before sending it to the database. This gives users immediate feedback and cuts down on unnecessary database calls.

If you’re using Entity Framework Core with Npgsql, you can explicitly configure the RFC property to map correctly to character(13):

Using Fluent API: #

public class YourEntity
{
    public string Rfc { get; set; }
    // Add other entity properties here
}

public class YourDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure RFC as fixed-length char(13) primary key
        modelBuilder.Entity<YourEntity>()
            .Property(e => e.Rfc)
            .HasColumnType("character(13)")
            .IsRequired();
        
        modelBuilder.Entity<YourEntity>()
            .HasKey(e => e.Rfc);
    }
}

Using Data Annotations: #

public class YourEntity
{
    [Key]
    [Column(TypeName = "character(13)")]
    [Required]
    public string Rfc { get; set; }
    // Add other entity properties here
}

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