Primary Keys, Composite Keys, & Guids

Every entity in a relational database must have a unique identifier. While an auto-incrementing integer (Identity Column) is the industry standard, modern distributed systems often require complex key structures, such as GUIDs or Multi-Column Composite Keys.

1. Standard Identifiers (Auto-Increment)

By convention, if a property is named Id or ClassNameId, EF Core automatically designates it as the Primary Key.

public class Order
{
    // EF Core recognizes this automatically.
    // In SQL Server, this becomes an IDENTITY(1,1) column.
    public int Id { get; set; } 
}

2. Using GUIDs (Globally Unique Identifiers)

Integers are sequential (1, 2, 3), making them highly predictable. A hacker who sees /api/orders/5 can immediately guess /api/orders/6. GUIDs (e.g., a3f8b...) are impossible to guess, making them ideal for secure API identifiers.

public class User
{
    public Guid Id { get; set; } 
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .Property(u => u.Id)
        // Generates an ordered GUID inside SQL Server, eliminating Page Splits!
        .HasDefaultValueSql("NEWSEQUENTIALID()"); 
}

3. Shadow Properties as Foreign Keys

A "Shadow Property" is a column that exists in SQL Server, but does NOT exist in your C# class. This is incredibly useful for hiding internal database IDs from your domain logic.

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    // Note: There is NO 'BlogId' foreign key defined here in C#!
}

// In Fluent API:
modelBuilder.Entity<Post>()
            // We tell EF Core to create a 'BlogId' column in SQL Server anyway
            .Property<int>("BlogId"); 

4. Interview Mastery

Q: "We have an 'Enrollments' table linking 'Students' and 'Courses'. The DBA requires a Composite Primary Key using both the StudentId and CourseId. Why can't we just use two '[Key]' Data Annotations on the C# properties?"

Architect Answer: "Entity Framework Core explicitly prohibits using multiple `[Key]` annotations on a single entity class to define a composite key; the framework will throw a runtime exception during model building because it doesn't know the intended hierarchical order of the composite keys (e.g., should the SQL Index be StudentId, CourseId or CourseId, StudentId?). Composite keys MUST be configured using the Fluent API via `builder.HasKey(e => new { e.StudentId, e.CourseId });`. Note: As of EF Core 7+, Microsoft finally introduced the `[PrimaryKey(nameof(StudentId), nameof(CourseId))]` class-level attribute to circumvent this, but the Fluent API remains the safest and most expressive standard for complex schema generation."