ef core entity framework sql server postgresql

Generate EF Core Entities from an Existing SQL Table (Without Scaffolding the Whole Database)

Turn a CREATE TABLE script from SQL Server, PostgreSQL, MySQL or SQLite into EF Core entity classes — keys, nullability, identity columns, foreign keys and the type-mapping traps.

· Mohammed Aquib Ansari

dotnet ef dbcontext scaffold is great when you own the database and want all of it. Most of the time I don’t. I’m adding one table from a legacy schema to a new service, or a DBA has sent me a script in a pull request, or the database lives in a network I can’t reach from my laptop. In those cases, I have DDL, not a connection string, and I want one or two entity classes that I can trust.

That’s the gap the SQL to C# entity generator fills. You paste CREATE TABLE statements, and it writes the classes. This guide walks through what it produces and — more usefully — the places where naive table-to-class conversion goes wrong.

A typical table

Here’s a SQL Server table the way I usually receive it:

CREATE TABLE dbo.Invoices (
    InvoiceId int IDENTITY(1,1) NOT NULL,
    customer_id int NOT NULL,
    InvoiceNumber varchar(20) NOT NULL,
    Total decimal(12, 2) NOT NULL,
    IssuedOn date NOT NULL,
    PaidAt datetimeoffset NULL,
    Notes nvarchar(max) NULL,
    CONSTRAINT PK_Invoices PRIMARY KEY (InvoiceId)
);

With the default options — data annotations, nullable reference types on, PascalCase names — the output is:

[Table("Invoices", Schema = "dbo")]
public class Invoices
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int InvoiceId { get; set; }

    [Column("customer_id")]
    public int CustomerId { get; set; }

    [MaxLength(20)]
    [Unicode(false)]
    public string InvoiceNumber { get; set; } = null!;

    [Column(TypeName = "decimal(12,2)")]
    public decimal Total { get; set; }

    public DateOnly IssuedOn { get; set; }

    public DateTimeOffset? PaidAt { get; set; }

    public string? Notes { get; set; }
}

Every attribute in there is doing a job, and it’s worth knowing which.

The details that matter

[Column("customer_id")]. The property is CustomerId because that’s what C# code should look like, but EF Core maps properties to columns by name. Without the attribute, EF would query a CustomerId column that doesn’t exist. The generator only adds [Column] when the names differ.

[Unicode(false)] on the varchar column. EF Core assumes strings are Unicode and sends nvarchar parameters. Comparing an nvarchar parameter to a varchar column forces SQL Server into an implicit conversion, which can turn an index seek into a scan. This one attribute has fixed real performance problems for me.

[Column(TypeName = "decimal(12,2)")]. EF’s default for decimal on SQL Server is decimal(18,2). If your column is decimal(12,4) and you don’t say so, values get rounded on the way in.

DateOnly for date. .NET 6 added DateOnly and TimeOnly, and EF Core 8 maps them natively on SQL Server. If you’re on an older stack, turn off the “DateOnly / TimeOnly” option and you get DateTime and TimeSpan instead.

Nullability. PaidAt is nullable in SQL, so it’s DateTimeOffset?. InvoiceNumber is NOT NULL, so with nullable reference types on it’s a non-nullable string initialised with = null! — or, if you prefer, the required modifier via the “NOT NULL strings” option. With nullable reference types off, you get [Required] instead, because EF otherwise treats every string as optional.

DatabaseGeneratedOption.Identity. Easy to take for granted, but it matters in the opposite case. If a table has an int primary key without IDENTITY, EF Core still assumes by convention that the database generates it, and your inserts fail or send zero. The generator writes DatabaseGeneratedOption.None for integer keys that aren’t identity columns, so EF sends the value you set.

Dialects

The same idea looks different in every database, and this is where copy-pasting from a Stack Overflow type-mapping table goes wrong.

  • SQL Server’s timestamp isn’t a date. It’s the old name for rowversion, and it maps to byte[] with [Timestamp] for optimistic concurrency.
  • MySQL’s tinyint(1) is how MySQL spells boolean, so it becomes bool. A plain tinyint becomes sbyte, or byte when it’s UNSIGNED.
  • MySQL TIME can hold values up to 838 hours, which TimeOnly can’t represent, so it stays TimeSpan.
  • PostgreSQL folds unquoted identifiers to lower case. CREATE TABLE Users actually creates users, so the generator writes [Table("users")], not [Table("Users")]. Npgsql quotes identifiers, so getting this wrong means “relation does not exist” at runtime.
  • SERIAL, BIGSERIAL, GENERATED … AS IDENTITY and pg_dump’s SET DEFAULT nextval(...) all mean identity.
  • In SQLite, INTEGER is 64-bit, so it maps to long, and an INTEGER PRIMARY KEY is an alias for the rowid — database-generated even without AUTOINCREMENT. Unknown SQLite types fall back to SQLite’s own affinity rules.

The dialect is detected from the script (brackets and GO mean SQL Server, backticks and AUTO_INCREMENT mean MySQL, and so on), and you can override it.

Scripts from real tools

I care most about pasting whatever a tool exported, without cleaning it up first. SSMS’s “Script Table as → CREATE To” output has SET ANSI_NULLS ON, GO separators, bracketed types like [nvarchar](256), ON [PRIMARY], and it scripts foreign keys and defaults as separate ALTER TABLE statements after the table:

ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customers] ([CustomerId])
GO

The parser applies those constraints back to their tables. pg_dump output works the same way. Anything that isn’t a table definition — INSERT, CREATE INDEX, functions — is skipped and listed, so you know it was seen and ignored rather than lost.

If a statement genuinely can’t be parsed, you get the line and column and a hint to check the dialect. The columns are still pulled out with a simpler pattern, and that class is labelled “best effort” at the top so you don’t mistake it for a clean parse.

Keys that don’t fit in an attribute

Composite primary keys are the classic annotation problem. Before EF Core 7 there was no attribute for them at all. The generator emits the EF 7+ [PrimaryKey] attribute and puts the Fluent API version in a comment above it:

// EF Core 7+. Before 7: modelBuilder.Entity<OrderLines>().HasKey(e => new { e.OrderId, e.LineNo });
[PrimaryKey(nameof(OrderId), nameof(LineNo))]
public class OrderLines

A table with no primary key gets [Keyless] and a warning. EF can query keyless entities, but it can’t track, update or delete them, and it’s better to find that out now than after writing the repository.

Foreign keys and navigation properties

When both tables are in the script, a foreign key produces a reference navigation on the dependent side and a collection on the principal side. If a table has two foreign keys to the same parent — AuthorId and EditorId both pointing at Accounts — the collections get distinct names and [InverseProperty], so EF doesn’t have to guess which relationship is which. If the referenced table isn’t in the script, you just get the FK column and a note listing the missing table. Turn off “Navigation properties” if you prefer to add them by hand.

Annotations, Fluent API, or neither

Some teams keep entities attribute-free and configure everything in OnModelCreating. Switch “Mapping” to Fluent API and the classes become plain, with a separate snippet:

modelBuilder.Entity<Invoices>(entity =>
{
    entity.ToTable("Invoices", "dbo");
    entity.HasKey(e => e.InvoiceId);
    entity.Property(e => e.InvoiceId).ValueGeneratedOnAdd();
    entity.Property(e => e.CustomerId).HasColumnName("customer_id");
    entity.Property(e => e.InvoiceNumber)
        .HasMaxLength(20)
        .IsUnicode(false);
    entity.Property(e => e.Total).HasColumnType("decimal(12,2)");
});

“Plain POCO” drops the mapping entirely, which is handy for DTOs or Dapper.

Things it deliberately doesn’t do

It doesn’t translate DEFAULT GETDATE() into a C# initializer. A database default runs at insert time, in the database; a C# initializer runs when you construct the object. Those aren’t the same thing, so defaults stay as comments. Class names aren’t singularized by default either — Invoices stays Invoices — because English pluralization rules are unreliable. There’s a best-effort toggle if you want Invoice.

Going the other direction

If you’re designing the class first and need the table, C# to SQL generates CREATE TABLE statements for SQL Server, MySQL and PostgreSQL — the C# to SQL guide covers that workflow. And if what you actually have is a JSON payload rather than a table, JSON to C# is the right starting point.

For the table-first case, paste your script into the SQL to C# generator, read the warnings, and you’ll have entities that match the schema you actually have — including the parts EF’s conventions would have got wrong.