Dapper.FastCrud Documentation

Dapper.FastCrud uses a layered approach to manage query options. Understanding this hierarchy helps you know which interfaces to use for configuring your SQL statements:

1. Aggregated Options: These are the final, internal options used by the core algorithms. AggregatedSqlStatementOptions contains the primary query options, while AggregatedRelationalSqlStatementOptions stores options related to JOINed entities.
2. Situational Options: These are public interfaces that expose only a subset of the total available options, tailored to specific statement types. For example:
   • IStandardSqlStatementOptionsSetter: Methods available for all statement types.
   • IRangedConditionalSqlStatementOptionsSetter: Methods available only when a statement is expected to return a range of entities.
3. Situational Builders: These are the public fluent interfaces you interact with. They combine multiple situational option setters into a single builder and use an internal implementation to map your calls directly onto the underlying aggregated options builder.

Dapper.FastCrud provides several advanced features for database interaction:

• Composite Primary Keys: Supported for entities, but note that standard CRUD operations require UNIQUE primary keys.
• Multiple Entity Mappings: Useful for partial queries in large denormalized tables or data migrations.
• Transaction and Timeout Support: All CRUD methods accept a transaction, a command timeout, and a custom entity mapping.
• Opt-in Relationships: Supports self-referenced entities, one-to-one relationships, and multiple joins to the same target via aliases.
• Flexible Joins: JOIN support is extended to GET and COUNT methods. You can join with any navigation property (or none) and any ON clause without requiring a pre-set relationship in the mappings.
• SQL Formattables: A set of formatters available via the Sql static class to resolve raw names and SQL-ready counterparts.

You can use the Find<T> method on a dbConnection to build type-safe queries using a fluent statement builder. This allows for aliasing, joining multiple entities, filtering with Where, ordering, and pagination (Skip/Top).

To ensure type safety when referencing properties and aliases, use the following format specifiers:

• {nameof(Property):of alias}: Resolves the property name relative to a specific table alias.
• {nameof(Property):P}: Formats the property name as a SQL parameter.

Example of a complex query with an inner join and aliasing:

    var queryParams = new 
    {
        FirstName = "John",
        Street = "Creek Street"
    };

    var persons = dbConnection.Find<Person>(statement => statement
        .WithAlias("person")
        .Include<Address>(join => join
            .InnerJoin()
            .WithAlias("address"))
        .Where($@"
            {nameof(Person.FirstName):of person} = {nameof(queryParams.FirstName):P} 
            AND {nameof(Address.Street):of address} = {nameof(queryParams.Street):P}")  
        .OrderBy($"{nameof(Person.LastName):of person} DESC")  
        .Skip(10)  
        .Top(20)  
        .WithParameters(queryParams);

The Dapper.FastCrud.ModelGenerator produces partial classes decorated with attributes for mapping to database tables and columns. It handles primary keys, foreign keys, and navigation properties for relationships.

    /// <summary>
    /// Represents the 'Badges' table.
    /// </summary>
    [Table("Badges")]
    public partial class BadgeEntity
    {
        /// <summary>
        /// Represents the column 'Id'.
        /// </summary>
        [Key]
        [Column(Order = 1)]
        [ForeignKey(nameof(Employee))]
        public virtual int AssetId { get; set; }

        /// <summary>
        /// Represents the column 'EmployeeId'.
        /// </summary>
        [Key]
        [Column(Order = 2)]
        [ForeignKey(nameof(Employee))]
        public virtual Guid EmployeeId { get; set; }

        /// <summary>
        /// Represents the column 'Barcode'.
        /// </summary>
        public virtual string Barcode { get; set; }

        /// <summary>
        /// Represents the navigation property for the child-parent relationship involving <seealso cref="EmployeeEntity"/>
        /// </summary>
        public virtual EmployeeEntity? Employee { get; set; }
    }

You can use the Find<T> method on a dbConnection to build complex, type-safe queries using a fluent statement builder. This allows for aliasing, including related entities via joins, filtering with type-safe property names, ordering, skipping, and taking a specific number of records.

To ensure type safety in the Where clause, use the custom "formattables" like :of (to get the column name with an alias) and :P (to get a parameter placeholder).

// Create parameters for the query
var queryParams = new 
{
    FirstName = "John",
    Street = "Creek Street"
};

// Get persons using the above created query parameters
var persons = dbConnection.Find<Person>(statement => statement
   .WithAlias("person")
   .Include<Address>(join =>
        join.InnerJoin()
            .WithAlias("address"))
   .Where($@"
        {nameof(Person.FirstName):of person} = {nameof(queryParams.FirstName):P} 
        AND {nameof(Address.Street):of address} = {nameof(queryParams.Street):P}")
   .OrderBy($"{nameof(Person.LastName):of person} DESC")  
   .Skip(10)
   .Top(20)
   .WithParameters(queryParams);