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));

Joins are accepted here as well, however since this is passed down to the database, be careful to only join with relationships having at most ONE referenced entity. Let's pick an example:

• The Teacher entity above is set up to reference Student entities in a parent-child relationship.
• If we were to use a Find method having Teacher as the main entity and we ask for Student to be included as well then:
  1. We would receive back a unique list of Teacher entities.
  2. Every Teacher entity's Student collection navigation property will be filled up with unique Student entities.
• However, if we use a Count method:
  1. The query causes duplicate Teacher entities to be counted. FastCrud has no way of helping in this case since the entities themselves are not passed from the database. In other words, if "Teacher 1" had 10 students assigned and "Teacher 2" had 20, if we ask the database to count the teachers with students joined, irrespective whether we have asked for an INNER or LEFT JOIN, in this case we would receive a count of 30 teacher records.

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 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 tot 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.
    • 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.

e.g.:

dbConnection.Insert(entityToInsert, statement =>statement    
  .AttachToTransaction(dbTransaction)
  .WithTimeout(TimeSpan.FromSeconds(10)));