Home

Usage

using Dapper.FastCrud
OrmConfiguration.DefaultDialect = SqlDialect.MsSql|MySql|SqLite|PostgreSql

Insert

dbConnection.Insert(newEntity);  

newEntity will have its properties partially updated with the db generated values on return

Select

Select by primary key(s)

dbConnection.Get(new Asset {Id = 10});

Select All

dbConnection.Find<Entity>();

Select record set

var selectParams = new {
  FirstName = "John"
}
dbConnection.Find<Entity>(statement => statement  
            .Where($"{nameof(Entity.FirstName):C} = {nameof(selectParams.FirstName):P}")  
            .OrderBy($"{nameof(Entity.LastName):C} DESC")  
            .Skip(10)  
            .Top(20)  
            .WithParameters(selectparams);

:C and :P used here are string formatter specifiers. Please refer to SQL statements and clauses for all the available formatter specifiers.

Update

Update by primary key(s)

dbConnection.Update(updatedEntity);   

updatedEntity will have its properties partially updated with the db generated values on return

Update all

dbConnection.BulkUpdate(new Asset {Name = "Unknown"});

Update record set

• Option 1: Create a new temporary mapping for partial updating the entities.

var bulkUpdateParams = new {
  AssetName = "workstation"
};
var partialUpdateMapping = OrmConfiguration.GetDefaultEntityMapping<EmployeeDbEntity>
                                           .UpdatePropertiesExcluding(prop => prop.IncludeInUpdates(false),
                                                                      nameof(Asset.IsLost));

dbConnection.BulkUpdate(new Asset {IsLost = true}, statement => statement  
            .Where($"{nameof(Asset.Name):C} = {nameof(bulkUpdateParams.AssetName):P}"))
            .WithEntityMappingOverride(partialUpdateMapping);

• Option 2: Create a new entity pointing to the same table, having just the primary keys and the properties you require for the update, that can be used strictly for bulk updates.

• Option 3: Create your own statement using FastCrud's built-in SQL formatter and run it straight through Dapper. That way you benefit from all the mappings done through FastCrud and the FastCrud's formatter, while still having the flexibility of creating your own SQL statements.

string statement = Sql.Format<Asset>($@"
     UPDATE {nameof(Asset):T} 
     SET {nameof(Asset.IsLost):C} = 1
     WHERE {nameof(Asset.Name):C} = {nameof(bulkUpdateParams.AssetName):P}
");

Delete

Delete by primary key(s)

dbConnection.Delete(new Asset {Id = 10});

Delete all

dbConnection.BulkDelete<Entity>();

Delete record set

dbConnection.BulkDelete<Asset>(statement => statement 
            .Where($"{nameof(Asset.IsLost):C}=1"));

Count

Count all

    dbConnection.Count<Teacher>();

Count record set

    var countParams = new {
        TeacherFirstName = "John";
    };

    dbConnection.Count<Teacher>(statement => statement  
                .Where($"{nameof(Teacher.FirstName):C} = {nameof(countParams.TeacherFirstName:P}")  
                .WithParameters(countParams));

When the statement is set to join with other entities, a DISTINCT clause is added to the query.

Async

Async methods have an identical usage

Options

As you've noticed in the previous examples, a number of options are immediately available for tweaking the statement. Their availability varies by type of operation.

• WithTimeout
  • Enforces a maximum time span on the current command.
  • The default timeout can be set instead via OrmConfiguration.DefaultSqlStatementOptions.
• AttachToTransaction
  • Attaches the current command to an existing transaction.
• WithEntityMappingOverride
  • Overrides the entity mapping for the current statement. This is useful for Multiple entity mappings.
• OrderBy
  • Adds a SQL condition to order the records.
• Top&Skip
  • Limits the records returned by the query.
  • Don't forget to use OrderBy when using any of these.
• Where
  • Filters the records returned by the query.
  • When it comes to SQL clauses in general, FastCrud accepts formattable strings as parameters. While you can pass them inline with the $ symbol, keep in mind that they can also be merged, however when doing so they have to be assigned directly to a FormattableString type (more information on this topic can be found here):

// do not use 'var' as that will resolve the formattable strings
FormattableString firstNameQuery = $"{nameof(Person.FirstName):C} = {nameof(params.firstNameSearchParam):P}"; 
FormattableString lastNameQuery = $"{nameof(Person.LastName):C} = {nameof(params.lastNameSearchParam):P}"; 

dbConnection.Find<Person>(statement => statement.Where($"{firstNameQuery} AND {lastNameQuery}"));

• WithParameters
  • Sets the parameters to be used by the statement.
• When
  • Branches the statement builder based on a condition.
• WithAlias
  • Assigns an alias to the main entity, so that it can be referenced in the SQL clauses.
  • It is recommended to always use aliases when the statement contains multiple entities.
  • Check SQL statements and clauses for more information.
• Join
  • Includes a referenced entity into the query. More information on the JOINs can be found here. The following join options can be passed:
    • WithAlias
      • Sets up an alias for the referenced entity to be used in a relationship.
      • It is recommended to assign aliases to all the entities.
    • MapResults
      • If set to true (default), the unique referenced entities will be set on the navigation properties.
      • This flag can be overridden on an individual relationship (see below)
    • LeftOuterJoin
      • Specifies a LEFT OUTER JOIN to be used.
    • InnerJoin
      • Specifies an INNER JOIN.
    • On
      • When specified, you're manually targeting the JOIN ON clause for the current entity. By doing so you're forfeiting any relationships set up between the current entity and others. This can be used when no relationships were set up or a special ON clause needs to be set. When this is used and you still want to map the results onto some generic properties, you need to manually use the Referencing clause.
    • When
      • Branches the join options builder based on a condition.
    • Referencing
      • Optionally specifies the entity on the left side of the join. This can be used to provide more information necessary to locate the desired relationship registration, to override the relationship registration or to set up the relationship when no relationship has been set up through the mappings:
        • FromAlias
          • Specifies the alias of the referencing entity. This should've been already set up with a call to WithAlias.
        • FromProperty
          • The navigation property on the referencing entity.
        • ToProperty
          • The navigation property on the referenced entity.
        • MapResults
          • If set tot true (default), the unique referenced entities will be set on the navigation properties.
        • When
          • Branches the relationship options builder based on a condition.