-
Notifications
You must be signed in to change notification settings - Fork 4
CRUD Creating
There are several overloads for inserting data:
- Insert - Insert an entity
- Insert<TPrimaryKey> - Insert an entity and get back its primary key
- InsertRange - Efficiently insert many entities
- InsertRange<TPrimaryKey> - insert many entities, and record their primary keys.
// Synchronous
void Insert(object entity, int? commandTimeout = null, bool? verifyAffectedRowCount = null);
// Asynchronous
Task InsertAsync(object entity, int? commandTimeout = null, bool? verifyAffectedRowCount = null, CancellationToken cancellationToken = default);Inserts an entity into the table, without reading back the generated primary key. This is useful if the key is not generated by the database (e.g. a GUID/UUID), or is not a single integer/long. It's also useful if you don't care about the generated key.
// Synchronous
TPrimaryKey Insert<TPrimaryKey>(object entity, int? commandTimeout = null);
// Asynchronous
Task<TPrimaryKey> InsertAsync<TPrimaryKey>(object entity, int? commandTimeout = null, CancellationToken cancellationToken = default);Inserts an entity into the table and returns it's generated identity.
int and long) type are supported, since only those are generated by the database. For all other types of primary key, pre-generate the key and use the non-genericvoid Insert(entity).
[Table("Users")]
public class UserEntity
{
public int Id { get; set; }
public string Name { get; set; }
}
...
var entity = new UserEntity { Name = "Little bobby tables" };
entity.Id = database.Insert<int>(entity);MS-SQL 2012 +
INSERT INTO [Users] ([Name])
VALUES (@Name);
SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS [id]PostgreSQL
INSERT INTO user (name)
VALUES (@Name)
RETURNING id[Table("Users")]
public class UserEntity
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public Guid Id { get; set; }
public string Name { get; set; }
}
...
var entity = new UserEntity { Id = Guid.NewGuid(), Name = "Little bobby tables" };
database.Insert(entity);MS-SQL 2012 +
INSERT INTO [Users] ([Id], [Name])
VALUES (@Id, @Name);PostgreSQL
INSERT INTO user (id, name)
VALUES (@Id, @Name);[Table("Users")]
public class UserEntity
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string FirstName { get; set; }
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string LastName { get; set; }
public int Age { get; set; }
}
...
var entity = new UserEntity { FirstName = "Bobby", LastName = "Drop-Tables", Age = 4 };
database.Insert(entity);MS-SQL 2012 +
INSERT INTO [Users] ([FirstName], [LastName], [Age])
VALUES (@FirstName, @LastName, @Age);PostgreSQL
INSERT INTO users (firstName, lastName, age)
VALUES (@FirstName, @LastName, @Age);// Synchronous
SqlCommandResult InsertRange<TEntity>(IEnumerable<TEntity> entities, int? commandTimeout = null);
// Asynchronous
Task<SqlCommandResult> InsertRangeAsync<TEntity>(IEnumerable<TEntity> entities, int? commandTimeout = null, CancellationToken cancellationToken = default);Efficiently inserts multiple entities. This is identical in behaviour to consecutive calls to Insert but generates the SQL statement only once.
📝 for performance, it is recommended to wrap all bulk actions in a transaction.
// Synchronous
void InsertRange<TEntity, TPrimaryKey>(IEnumerable<TEntity> entities, Action<TEntity, TPrimaryKey> setPrimaryKey, int? commandTimeout = null);
// Asynchronous
Task InsertRangeAsync<TEntity, TPrimaryKey>(IEnumerable<TEntity> entities, Action<TEntity, TPrimaryKey> setPrimaryKey, int? commandTimeout = null, CancellationToken cancellationToken = default);Inserts multiple entities, and for each one entity, calls an action with it and it's primary key. This allows you to set the primary key on each entity after inserting.
📝 for performance, it is recommended to wrap all bulk actions in a transaction.
int and long) type are supported, since only those are generated by the database. For all other types of primary key, pre-generate the keys and use InsertRange<TEntity>(entities).
[Table("Users")]
public class UserEntity
{
public int Id { get; set; }
public string Name { get; set; }
}
...
var entities = new []
{
new User { Name = "Little bobby tables" },
new User { Name = "Jimmy" };
};
using (var unitOfWork = database.StartUnitOfWork())
{
unitOfWork.InsertRange<User, int>(entities, (e, k) => { e.Id = k; });
unitOfWork.SaveChanges();
}MS-SQL 2012 +
INSERT INTO [Users] ([Name])
VALUES (@Name);
SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS [id]PostgreSQL
INSERT INTO Users (Name)
VALUES (@Name)
RETURNING Id[Table("Users")]
public class UserEntity
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public Guid Id { get; set; }
public string Name { get; set; }
}
...
var entities = new []
{
new User { Id = Guid.NewGuid(), Name = "Little bobby tables" },
new User { Id = Guid.NewGuid(), Name = "Jimmy" }
};
using (var unitOfWork = database.StartUnitOfWork())
{
unitOfWork.InsertRange(entities);
unitOfWork.SaveChanges();
}MS-SQL 2012 +
INSERT INTO [Users] ([Id], [Name])
VALUES (@Id, @Name);PostgreSQL
INSERT INTO user (id, name)
VALUES (@Id, @Name);[Table("Users")]
public class UserEntity
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string FirstName { get; set; }
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string LastName { get; set; }
public int Age { get; set; }
}
...
var entity = new UserEntity { FirstName = "Bobby", LastName = "Drop-Tables", Age = 4 };
database.Insert(entity);
var entities = new []
{
new User { FirstName = "Bobby", LastName = "Drop-Tables", Age = 4 },
new User { FirstName = "Jimmy", LastName = "Little", Age = 6 }
};
using (var unitOfWork = database.StartUnitOfWork())
{
unitOfWork.InsertRange(entities);
unitOfWork.SaveChanges();
}MS-SQL 2012 +
INSERT INTO [Users] ([FirstName], [LastName], [Age])
VALUES (@FirstName, @LastName, @Age);PostgreSQL
INSERT INTO user (firstName, lastName, age)
VALUES (@FirstName, @LastName, @Age);