Entity mapping and CRUD

How an entity type maps to a table and its columns, and the methods that insert, update and delete entities — one at a time or in bulk, with optimistic concurrency.

Entity Mapping

You can configure how entity types are mapped to database tables and columns using either the fluent API or data annotation attributes.

Note

Mapping configured via the fluent API takes precedence over mapping configured via data annotation attributes. When a fluent mapping exists for an entity type, the data annotations on this entity type are ignored. When a fluent mapping exists for an entity property, the data annotations on this property are ignored.

Fluent API

You can use the fluent API to configure how entity types are mapped to database tables and columns.

DbConnectionExtensions.Configure(config =>
{
    config.Entity<Product>()
        .ToTable("Products");

    config.Entity<Product>()
        .Property(a => a.Id)
        .HasColumnName("ProductId")
        .IsIdentity()
        .IsKey();

    config.Entity<Product>()
        .Property(a => a.DiscountedPrice)
        .IsComputed();

    config.Entity<Product>()
        .Property(a => a.IsOnSale)
        .IsIgnored();

    config.Entity<Product>()
        .Property(a => a.Version)
        .IsRowVersion();

    config.Entity<User>()
        .Property(a => a.ConcurrencyToken)
        .IsConcurrencyToken();
});

Method	Configures
`Entity<TEntity>()`	Starts configuring the mapping for the entity type `TEntity`.
`ToTable(tableName)`	The table where entities of that type are stored.
`Property(propertyExpression)`	Starts configuring the mapping for one property.
`HasColumnName(columnName)`	The column where the property is stored.
`IsKey()`	The property is part of the key by which entities are identified.
`IsIdentity()`	The property is generated by the database on insert.
`IsComputed()`	The property is generated by the database on insert and update.
`IsRowVersion()`	The property is a native database-generated concurrency token.
`IsConcurrencyToken()`	The property is an application-managed concurrency token.
`IsIgnored()`	The property is not mapped to a column.

Data annotation attributes

Entity mapping can also be configured with the standard attributes from System.ComponentModel.DataAnnotations and System.ComponentModel.DataAnnotations.Schema:

[Table("Products")]                                     // Table name; defaults to the type name
class Product
{
    [Key]                                               // Identifies the entity (usually the primary key)
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    [Column("ProductName")]                             // Column name; defaults to the property name
    public string Name { get; set; }

    [Timestamp]                                         // Native database-generated concurrency token
    public byte[] Version { get; set; }

    [ConcurrencyCheck]                                  // Application-managed concurrency token
    public byte[] ConcurrencyToken { get; set; }

    [NotMapped]                                         // Never read from or written to the database
    public decimal TotalPrice => this.UnitPrice * this.Quantity;
}

Attribute	Effect
`TableAttribute`	The table where entities of the type are stored. Without it, the entity type's name (excluding its namespace) is used.
`ColumnAttribute`	The column where the property is stored. Without it, the property name is used.
`KeyAttribute`	The property (or properties) by which entities of the type are identified.
`DatabaseGeneratedAttribute`	The property is generated by the database. Unless `DatabaseGeneratedOption.None` is used it is skipped when inserting and updating, and its value is read back from the database onto the entity afterwards.
`TimestampAttribute`	The property is a native database-generated concurrency token: it is checked during update and delete, which fail if the database value no longer matches the original, and it is read back after insert and update.
`ConcurrencyCheckAttribute`	The property is an application-managed concurrency token, checked during update and delete the same way.
`NotMappedAttribute`	The property is ignored entirely - never read from and never written to the database.

Entity manipulation methods

The examples below use these entity types:

class Product
{
    [Key]
    public long Id { get; set; }
    public long SupplierId { get; set; }
    public string Name { get; set; }
    public decimal UnitPrice { get; set; }
    public int UnitsInStock { get; set; }
    public bool IsDiscontinued { get; set; }
}

enum UserState { Active, Inactive, Suspended }

class User
{
    [Key]
    public long Id { get; set; }
    public DateTime LastLoginDate { get; set; }
    public UserState State { get; set; }
}

InsertEntities / InsertEntitiesAsync

Inserts a sequence of new entities into a database table.

connection.InsertEntities(GetNewProducts());

InsertEntity / InsertEntityAsync

Inserts a new entity into a database table.

connection.InsertEntity(GetNewProduct());

UpdateEntities / UpdateEntitiesAsync

Updates existing entities in a database table based on their keys.

var usersWithoutLoginInPastYear = connection.Query<User>(
    """
    SELECT  *
    FROM    Users
    WHERE   LastLoginDate < DATEADD(YEAR, -1, GETUTCDATE())
    """
);

foreach (var user in usersWithoutLoginInPastYear)
{
    user.State = UserState.Inactive;
}

connection.UpdateEntities(usersWithoutLoginInPastYear);

UpdateEntity / UpdateEntityAsync

Updates an existing entity in a database table based on its key.

if (user.LastLoginDate < DateTime.UtcNow.AddYears(-1))
{
    user.State = UserState.Inactive;
    connection.UpdateEntity(user);
}

DeleteEntities / DeleteEntitiesAsync

Deletes a sequence of entities from a database table based on their keys.

connection.DeleteEntities(products.Where(a => a.IsDiscontinued));

DeleteEntity / DeleteEntityAsync

Deletes an entity from a database table based on its key.

if (product.IsDiscontinued)
{
    connection.DeleteEntity(product);
}

Enum support

Enum values are sent to the database either as their string representation or as integers, controlled by EnumSerializationMode. Reading maps both representations back to the enum value automatically.

enum UserRole
{
  Admin = 1,
  User = 2,
  Guest = 3
}

class User
{
    [Key]
    public long Id { get; set; }
    public string UserName { get; set; }
    public UserRole Role { get; set; }
}

var user = new User { Id = 1, UserName = "adminuser", Role = UserRole.User };

connection.InsertEntity(user);
// Column "Role" contains the string "User" with EnumSerializationMode.Strings (the default),
// and the integer 2 with EnumSerializationMode.Integers.

The column type has to match the mode - NVARCHAR(200) for Strings, INT for Integers:

CREATE TABLE Users
(
    Id BIGINT,
    UserName NVARCHAR(255),
    Role NVARCHAR(200)   -- INT when EnumSerializationMode.Integers is used
)