The EF Core Fluent API Catalog: Column Types, Default and Computed Values, Sequences and HiLo, Index Options and Bulk Conventions

Version note: The code in this post was written and tested with EF Core 10 and SQL Server 2025; the EF Core 11 note was tested with EF Core 11.0.0.

Guess First

In Bookstore, the online bookstore used as the shared example across this site's series, the shipping fee is 4.99 by default. You define it as a default value in the database:

b.Property(x => x.ShippingFee).HasDefaultValue(4.99m);

Marketing launches a "free shipping over 40" campaign. The code does this:

var order = new Order { Customer = customer, Subtotal = 40m, ShippingFee = 0m };
db.Orders.Add(order);
await db.SaveChangesAsync();

What ShippingFee reaches the database? The answer is in the section on default values; it's not the 0 most people expect.

In post 04 we compared the Fluent API with data annotations and spread the configuration out with IEntityTypeConfiguration. This post is a catalog: we collect the Fluent API calls you use day to day while setting up the Bookstore model for SQL Server, together with the SQL they produce. The model continues from post 30 (Book gained Slug, Order gained an order number and amount columns); in this post we moved from EnsureCreated to migrations and took the SQL from dotnet ef migrations script.

Bulk Settings: ConfigureConventions

The first job is to change the defaults instead of configuring every property in the model one by one. Since EF Core 6, ConfigureConventions is the place for that:

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Properties<string>().HaveMaxLength(200);
    configurationBuilder.Properties<decimal>().HavePrecision(18, 2);
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasSequence<int>("OrderNumbers", "ordering")
        .StartsAt(100001)
        .IncrementsBy(1);

    modelBuilder.ApplyConfigurationsFromAssembly(typeof(BookstoreContext).Assembly);
    // ...
}

After these two lines every string in the model becomes nvarchar(200) and every decimal decimal(18,2). The entity's own configuration overrides this default: I gave Author.Name HasMaxLength(100) and Book.Price HasPrecision(10, 2), and that's how they came out in the script. Customer.FullName, where I said nothing, became nvarchar(200). Book.Tags, a List<string>, doesn't count as a string; it stayed nvarchar(max) as a JSON column.

The decimal setting isn't decoration. When I removed it and generated a migration, this warning came for every decimal property:

warn: Microsoft.EntityFrameworkCore.Model.Validation[30000]
      No store type was specified for the decimal property 'Subtotal' on entity type 'Order'. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values in 'OnModelCreating' using 'HasColumnType', specify precision and scale using 'HasPrecision', or configure a value converter using 'HasConversion'.

The "silently truncated" part matters: a value below the cent or a large amount that doesn't fit gets cut off without an error. Give every decimal that holds money a precision, and use the bulk setting so you don't forget one.

Column Types

The Book configuration contains most of the calls that determine column types:

public class BookConfiguration : IEntityTypeConfiguration<Book>
{
    public void Configure(EntityTypeBuilder<Book> b)
    {
        b.ToTable("Books", "catalog", t => t.HasCheckConstraint("CK_Books_Price", "[Price] >= 0"));

        b.Property(x => x.Title).UseCollation("Turkish_CI_AS");
        b.Property(x => x.Slug).HasMaxLength(220).IsUnicode(false);
        b.Property(x => x.Isbn)
            .HasMaxLength(13)
            .IsFixedLength()
            .IsUnicode(false)
            .HasComment("ISBN-13 without dashes");
        b.Property(x => x.Price).HasPrecision(10, 2);

        b.HasAlternateKey(x => x.Isbn);
        b.HasIndex(x => x.Title);
        b.HasIndex(x => x.Slug).IsUnique();
        b.HasIndex(x => x.PublishedOn)
            .IsDescending()
            .IncludeProperties(x => new { x.Title, x.Price })
            .HasDatabaseName("IX_Books_PublishedOn_Newest");
    }
}

CREATE TABLE [catalog].[Books] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(200) COLLATE Turkish_CI_AS NOT NULL,
    [Slug] varchar(220) NULL,
    [Isbn] char(13) NOT NULL,
    [Price] decimal(10,2) NOT NULL,
    [PublishedOn] date NOT NULL,
    [Tags] nvarchar(max) NOT NULL,
    [AuthorId] int NOT NULL,
    CONSTRAINT [PK_Books] PRIMARY KEY ([Id]),
    CONSTRAINT [AK_Books_Isbn] UNIQUE ([Isbn]),
    CONSTRAINT [CK_Books_Price] CHECK ([Price] >= 0),
    CONSTRAINT [FK_Books_Authors_AuthorId] FOREIGN KEY ([AuthorId]) REFERENCES [catalog].[Authors] ([Id]) ON DELETE CASCADE
);
DECLARE @description AS sql_variant;
SET @description = N'ISBN-13 without dashes';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', N'catalog', 'TABLE', N'Books', 'COLUMN', N'Isbn';

Line by line: HasMaxLength gave the length, IsUnicode(false) the type without the n (varchar), IsFixedLength the fixed length (char). An ISBN is always 13 digits, so char(13) is the right type; the slug appears in URLs, so it's ASCII. HasPrecision(10, 2) overrode the bulk setting. HasComment wrote the MS_Description that scaffolding read in post 29; if someone else reads the schema, it's cheap documentation. If none of these is enough you can give a raw type such as HasColumnType("money"), but then you lose provider independence.

UseCollation("Turkish_CI_AS") keeps the promise from post 30. The column is now compared with Turkish rules; without EF.Functions.Collate, a plain StartsWith("ince") query found "İnce Memed", and because the IX_Books_Title index was built with the same collation the search stayed sargable. For conversions that change how the value is stored (an enum stored as a string, strongly typed ids), see HasConversion in post 20; it is part of this catalog too.

Default and Computed Values

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> b)
    {
        b.Property(x => x.OrderNumber)
            .HasDefaultValueSql("NEXT VALUE FOR ordering.OrderNumbers");
        b.HasIndex(x => x.OrderNumber).IsUnique();

        b.Property(x => x.CreatedAt).HasDefaultValueSql("SYSUTCDATETIME()");
        b.Property(x => x.ShippingFee)
            .HasDefaultValue(4.99m)
            .ValueGeneratedNever();
        b.Property(x => x.TotalAmount)
            .HasComputedColumnSql("[Subtotal] - [DiscountAmount] + [ShippingFee]", stored: true);
    }
}

CREATE TABLE [Orders] (
    [Id] int NOT NULL IDENTITY,
    [OrderNumber] int NOT NULL DEFAULT (NEXT VALUE FOR ordering.OrderNumbers),
    [CreatedAt] datetime2 NOT NULL DEFAULT (SYSUTCDATETIME()),
    [Subtotal] decimal(18,2) NOT NULL,
    [DiscountAmount] decimal(18,2) NOT NULL,
    [ShippingFee] decimal(18,2) NOT NULL DEFAULT 4.99,
    [TotalAmount] AS [Subtotal] - [DiscountAmount] + [ShippingFee] PERSISTED,
    [CustomerId] int NOT NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Orders_Customers_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [Customers] ([Id]) ON DELETE CASCADE
);

HasDefaultValueSql takes a SQL expression, HasDefaultValue a constant. The stored: true parameter of HasComputedColumnSql makes the column PERSISTED: the value is computed when the row is written and stored on disk, it isn't recomputed on every read, and you can build an index on it. Without stored, the column is computed every time it's read. I made the setter of TotalAmount private; it makes no sense for the application to write a value to a computed column. A computed column can only look at columns in the same row; it can't sum the order lines, which is why Subtotal is a separate column.

Now the answer to the opening question. On the first attempt ShippingFee only had HasDefaultValue(4.99m), and the campaign order's INSERT went out like this:

INSERT INTO [Orders] ([CustomerId], [DiscountAmount], [Subtotal])
OUTPUT INSERTED.[Id], INSERTED.[CreatedAt], INSERTED.[OrderNumber], INSERTED.[ShippingFee], INSERTED.[TotalAmount]
VALUES (@p0, @p1, @p2);
OrderNumber=100007 ShippingFee=4.99 TotalAmount=44.99

ShippingFee isn't in the column list. For a property with a database default, EF Core interprets the CLR default (0 for decimal) as "no value given" and leaves the column out of the INSERT; the database then writes 4.99. The customer pays for shipping you said was free. EF Core 8 fixed the most obvious form of this problem, bools whose default is true; for other types the problem is still there.

There are two ways out. The first is HasSentinel: you change the value that means "no value given" (e.g. HasSentinel(-1m), with -1 as the property's initial value). The second, the one I chose: tell EF Core to always send the value with ValueGeneratedNever() and give the default in C# (public decimal ShippingFee { get; set; } = 4.99m;). The database default stays in place; it's still useful for filling a new column added by a migration or for INSERTs from other applications. The same order this time:

INSERT INTO [Orders] ([CustomerId], [DiscountAmount], [ShippingFee], [Subtotal])
OUTPUT INSERTED.[Id], INSERTED.[CreatedAt], INSERTED.[OrderNumber], INSERTED.[TotalAmount]
VALUES (@p0, @p1, @p2, @p3);
OrderNumber=100007 ShippingFee=0 TotalAmount=40.00

Look at the OUTPUT INSERTED line too: EF Core reads back every value the database generates (Id, CreatedAt, OrderNumber, TotalAmount) in the same command and writes it to the entity. Since ValueGeneratedNever() doesn't change the schema, it didn't need a migration either; when I tried, EF Core generated an empty migration and I deleted it. Conversely, if something you can't see in the model, such as a trigger, generates the value in the database, you tell EF Core with ValueGeneratedOnAdd() or ValueGeneratedOnAddOrUpdate(); it then reads the value back after each save.

Sequences, HiLo and Alternate Keys

The order number is shown to customers, so I want it separate from Id and starting at 100001. HasSequence defines a sequence in the database, and the default of OrderNumber takes the next value from it:

CREATE SEQUENCE [catalog].[AuthorIds] START WITH 1 INCREMENT BY 10 NO CYCLE;

CREATE SEQUENCE [ordering].[OrderNumbers] AS int START WITH 100001 INCREMENT BY 1 NO CYCLE;

Unlike identity, a sequence isn't tied to a table: several tables can share the same sequence, and you can fetch a value before the INSERT. After the six orders in the seed, the new order got number 100007.

The first sequence is for Author.Id and belongs to HiLo: b.Property(x => x.Id).UseHiLo("AuthorIds", "catalog");. HiLo reserves keys in blocks instead of fetching them from the database one at a time. When I added a new author, two commands went out:

SELECT NEXT VALUE FOR [catalog].[AuthorIds]

INSERT INTO [catalog].[Authors] ([Id], [Name])
VALUES (@p0, @p1);

EF Core took one value from the sequence and will hand out the next nine Ids itself; INCREMENT BY 10 is the block size. In the INSERT the Id now comes from the application, and there is no OUTPUT INSERTED.[Id]. The advantage is that the entity's key is known before SaveChanges and many INSERTs can go out in one batch. The cost is that every time the application restarts, the unused part of its block is skipped; there will be gaps in the Ids. If the Ids must be consecutive, don't use HiLo; for the order number we chose a separate sequence anyway.

HasAlternateKey(x => x.Isbn) produced a unique constraint named AK_Books_Isbn. The difference from a unique index is that EF Core treats it as a key: another entity's foreign key can point to the ISBN instead of the Id (HasPrincipalKey). If all you want is to guarantee uniqueness, a unique index is enough.

Index Options and Constraints

CREATE INDEX [IX_Books_PublishedOn_Newest] ON [catalog].[Books] ([PublishedOn] DESC) INCLUDE ([Title], [Price]);

CREATE UNIQUE INDEX [IX_Books_Slug] ON [catalog].[Books] ([Slug]) WHERE [Slug] IS NOT NULL;

CREATE INDEX [IX_Books_Title] ON [catalog].[Books] ([Title]);

The "new releases" page sorts books by publication date in reverse and shows title and price. IsDescending() (EF Core 7) builds the index in that order, and IncludeProperties adds title and price to the leaf level of the index; the query can be answered from the index without touching the table. HasDatabaseName replaced the name IX_Books_PublishedOn that the convention would have given.

I didn't write the WHERE [Slug] IS NOT NULL filter on IX_Books_Slug. Because Slug is nullable, EF Core adds this filter to the unique index on SQL Server by itself; otherwise SQL Server would count two NULLs as a conflict and a second book without a slug couldn't be added. If you need a different condition you give it yourself with HasFilter("..."), and HasFilter(null) removes the filter. When I tried to add a second book with the same slug:

Cannot insert duplicate key row in object 'catalog.Books' with unique index 'IX_Books_Slug'. The duplicate key value is (dune).

ToTable("Books", "catalog", t => t.HasCheckConstraint(...)) set both the schema and the check constraint in the same call. Authors and Books now live in the catalog schema, the order tables in dbo. A book with a negative price is stopped in the database even if it slips past the validation in the application:

The INSERT statement conflicted with the CHECK constraint "CK_Books_Price". The conflict occurred in database "efseries_ext_31", table "catalog.Books", column 'Price'.

The default constraints in the script have no names; SQL Server gives them random ones. With EF Core 10 you can name them, as in HasDefaultValue(4.99m, "DF_Orders_ShippingFee"), or have all of them named automatically with modelBuilder.UseNamedDefaultConstraints(). If you already have migrations, the next one renames every default constraint; decide this early.

ToSqlQuery and ToView

For reports I want a read model that gives the units sold per book, but I don't want to create a table or a view for it. ToSqlQuery maps a keyless entity to a SQL query:

modelBuilder.Entity<BookSalesSummary>()
    .HasNoKey()
    .ToTable((string?)null)
    .ToSqlQuery("""
        SELECT b.Id AS BookId, b.Title, SUM(oi.Quantity) AS Units
        FROM catalog.Books AS b
        JOIN dbo.OrderItems AS oi ON oi.BookId = b.Id
        GROUP BY b.Id, b.Title
        """);

SELECT [b].[BookId], [b].[Title], [b].[Units]
FROM (
    SELECT b.Id AS BookId, b.Title, SUM(oi.Quantity) AS Units
    FROM catalog.Books AS b
    JOIN dbo.OrderItems AS oi ON oi.BookId = b.Id
    GROUP BY b.Id, b.Title
) AS [b]
WHERE [b].[Units] > 2
ORDER BY [b].[Units] DESC
  { Title = The Hobbit, Units = 7 }
  { Title = Emma, Units = 6 }
  { Title = Dune, Units = 3 }

The query becomes a subquery, with Where and OrderBy on the outside. The ToTable((string?)null) line was again added afterwards: without it the first migration contained an empty BookSalesSummaries table. ToSqlQuery maps the read side, but EF Core also maps the entity to a default table, and the migration creates that table. If you want to read the same model from a view in the database, you write ToView("vw_BookSales"); in that case the migration doesn't create the view, and you have to create it yourself. We'll do that inside a migration in the next post.

Complex Type Configuration in EF Core 11

EF Core 11 note: until now, to configure the properties of the complex types we saw in posts 26 and 28, you first had to go down into the ComplexProperty builder. In EF Core 11 the lambda chain works directly, and you can build an index on a complex type property:

modelBuilder.Entity<Customer>(b =>
{
    b.ComplexProperty(c => c.Address);
    b.Property(c => c.Address.City).HasMaxLength(100);
    b.Property(c => c.Address.PostalCode).HasMaxLength(10).IsUnicode(false);
    b.HasIndex(c => c.Address.PostalCode);
});

CREATE TABLE [Customers] (
    [Id] int NOT NULL IDENTITY,
    [FullName] nvarchar(max) NOT NULL,
    [Address_City] nvarchar(100) NOT NULL,
    [Address_PostalCode] varchar(10) NOT NULL,
    [Address_Street] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Customers] PRIMARY KEY ([Id])
);

CREATE INDEX [IX_Customers_Address_PostalCode] ON [Customers] ([Address_PostalCode]);

I got this output in a separate project targeting net11.0, with EF Core 11.0.0 and GenerateCreateScript(); Bookstore itself stays on EF Core 10. I tried the same code on EF Core 10: it compiles, but throws an ArgumentException while building the model: "The expression 'c => c.Address.City' is not a valid member access expression." When I moved the properties into the ComplexProperty(c => c.Address, a => ...) builder, the same error came from the HasIndex(c => c.Address.PostalCode) line instead. On EF Core 10, if you want an index on the postal code, you need to model the address as an owned type.

Common Pitfalls

First, HasDefaultValue clashing with the CLR default. As with the shipping fee above, if 0 or false is a meaningful value and the database default is different, EF Core never sends that value. For every property with a default, ask "what happens if I deliberately write the CLR default?"; the answer is HasSentinel or ValueGeneratedNever.

Second, mapping decimals without a precision. The warning gets lost in the build output, and the result is amounts silently cut off. One line in ConfigureConventions is enough.

Third, keyless models such as ToSqlQuery or a function's row type turning into tables in a migration. Read the script after generating a migration; if you see a CREATE TABLE you didn't expect, ToTable((string?)null) is missing.

Fourth, expecting consecutive Ids from HiLo. Blocks are skipped as the application restarts; don't use the Id as a number shown to customers.

A Small Experiment

★☆☆ Add IsUnicode(false) to Customer.Email, generate a migration and read the old and new types of the AlterColumn in the migration file.

★★☆ Add a Status property to Order with a default of "Pending". Add one order without setting Status and another with "Shipped"; looking at the column lists of the INSERTs, explain in which one the default kicked in.

★★★ Replace the HiLo on Author.Id with UseSequence. Compare the INSERTs of the two approaches with LogTo and count how many commands reach the database when you add 100 authors.

In this post we changed the model and always applied the migration from scratch to an empty database. In real life migrations go to a database full of data: a column gets renamed, a column is split in two, two migrations are opened on two branches at the same time. In the next post we'll talk about migrations in practice: rename or drop, migrations that move data, managing views and functions with migrations, team conflicts and squashing.