Dynamics / Anonymous Types

This feature enables simplified, targeted operations by leveraging anonymous types. It is useful for performing CRUD operations on specific columns or without a class model.

This feature is referred to as DYNAMICS because it enables dynamic capabilities in the context of an ORM. This terminology is distinct from the dynamics concept within the .NET ecosystem.

Querying a Data

Use the Query operation with the target table as a literal string and a filter expression as an anonymous object.

using (var connection = new SqlConnection(connectionString))
{
    var customer = connection.Query("[dbo].[Customer]", 10045).FirstOrDefault();
}

Or filter by an anonymous type:

using (var connection = new SqlConnection(connectionString))
{
    var param = new
    {
        FirstName = "John",
        LastName = "Doe"
    };
    var customer = connection.Query("[dbo].[Customer]", param).FirstOrDefault();
}

Or return specific columns:

using (var connection = new SqlConnection(connectionString))
{
    var customer = connection.Query("[dbo].[Customer]",
        10045,
        Field.From("Id","FirstName", "LastName")).FirstOrDefault();
}

Iterating the Result

Data fetched from the database is automatically converted into an enumerable of ExpandoObject.

using (var connection = new SqlConnection(connectionString))
{
    var customers = connection.Query("[dbo].[Customer]", new { Country = "Denmark" },
        Field.From("Id","FirstName", "LastName"));

    foreach (var customer in customers)
    {
        ...
    }
}

Inserting a Data

Use the Insert operation with the target table as a literal string and an anonymous entity object.

using (var connection = new SqlConnection(connectionString))
{
    var entity = new
    {
        FirstName = "John",
        LastName = "Doe",
        CreatedDateUtc = DateTime.UtcNow
    };
    var id = connection.Insert<int>("[dbo].[Customer]", entity);
}

Or via Dictionary or ExpandoObject:

using (var connection = new SqlConnection(connectionString))
{
    var entity = new Dictionary<string, object>
    {
        { "FirstName", "John" },
        { "LastName", "Doe" },
        { "CreatedDateUtc", DateTime.UtcNow }
    };
    var id = connection.Insert<int>("[dbo].[Customer]", entity);
}

The library will add the newly created identity column value into the Dictionary or ExpandoObject object if not already present.

Deleting a Data

Use the Delete operation with the target table as a literal string and a filter expression as an anonymous object.

using (var connection = new SqlConnection(connectionString))
{
    var deletedRows = connection.Delete("[dbo].[Customer]", new { IsActive = false });
}

Merging a Data

Use the Merge operation with the target table as a literal string and an anonymous entity object.

using (var connection = new SqlConnection(connectionString))
{
    var entity = new
    {
        Id = 10045,
        FirstName = "John",
        LastName = "Doe",
        Address = "New York",
        LastUpdatedDateUtc = DateTime.UtcNow
    };
    var updatedRows = connection.Merge("[dbo].[Customer]", entity);
}

Updating a Data

Use the Update operation with the target table as a literal string and an anonymous entity object.

using (var connection = new SqlConnection(connectionString))
{
    var entity = new
    {
        Id = 10045,
        FirstName = "James",
        LastUpdatedDateUtc = DateTime.UtcNow
    };
    var updatedRows = connection.Update("[dbo].[Customer]", entity);
}

In general, specific columns can always be targeted during Query, Insert, Merge, or Update operations.