Since you’ve been struggling to implement these features you might be interested in trying out a new “Auto CRUD” preview feature that will be in the next release that’s currently available in the latest v5.8.1 on MyGet.
It’s conceptually the same as “Auto Query” where you just need to implement the Request DTOs definition for your DB Table APIs and AutoQuery automatically provides the implementation for the Service. You’ll need to register the AutoQueryFeature plugin to enable this feature.
There’ll be documentation when released, but currently the best way to explore the available features is looking at the tests in AutoQueryCrudTests.cs.
Essentially the way it works is that you’ll define Request DTOs that implement one of the following interfaces which dictates the behavior of the Service:
ICreateDb<Table> - Create new Table EntryIUpdateDb<Table> - Update existing Table EntryIDeleteDb<Table> - Delete existing Table Entry
All Request DTOs also require either an IReturn<T> or IReturnVoid marker interface to specify the return type of the Service. Can also use IReturn<EmptyResponse> for an “empty” response where as IReturnVoid returns “no” response.
I’ll go through a simple example, supposing we have a simple POCO table we want to maintain:
public class Rockstar
{
[AutoIncrement]
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int? Age { get; set; }
public DateTime DateOfBirth { get; set; }
public DateTime? DateDied { get; set; }
public LivingStatus LivingStatus { get; set; }
}
We can create a Service that inserts new Rockstar by defining all the properties we want to allow API consumers to provide when creating a new Rockstar:
public class CreateRockstar : ICreateDb<Rockstar>,IReturn<CreateRockstarResponse>
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int? Age { get; set; }
public DateTime DateOfBirth { get; set; }
}
public class CreateRockstarResponse
{
public int Id { get; set; } // Id is auto populated with RDBMS generated Id
public ResponseStatus ResponseStatus { get; set; }
}
You can now insert Rockstars by calling this API:
client.Post(new CreateRockstar {
FirstName = "Kurt",
LastName = "Cobain",
Age = 27,
DateOfBirth = new DateTime(20,2,1967),
});
Similarly you can define “Update” and “Delete” Services the same way, e.g:
public class UpdateRockstar : Rockstar,
IUpdateDb<Rockstar>, IReturn<UpdateRockstarResponse> {}
public class UpdateRockstarResponse
{
public int Id { get; set; } // Id is auto populated with RDBMS generated Id
public Rockstar Result { get; set; } // selects & returns latest DB Rockstar
public ResponseStatus ResponseStatus { get; set; }
}
If your Response DTO contains any of these properties it will be populated by AutoQuery:
T Id - The Primary KeyT Result - The POCO you want to return (can be a subset of DB model)int Count - Return the number of rows affected (Delete’s can have >1)
Delete Services need only a Primary Key, e.g:
public class DeleteRockstar : IDeleteDb<Rockstar>, IReturnVoid
{
public int Id { get; set; }
}
and to Query the Rockstar table you have the full featureset of AutoQuery for a complete set of CRUD Services without needing to provide any implementation.
Advanced CRUD Example
I’ll now go through a more advanced example that implements Audit information as well as layered support for multi-tenancy to see how you can compose features.
So lets say you have an interface that all tables you want to contain Audit information implements:
public interface IAudit
{
DateTime CreatedDate { get; set; }
string CreatedBy { get; set; }
string CreatedInfo { get; set; }
DateTime ModifiedDate { get; set; }
string ModifiedBy { get; set; }
string ModifiedInfo { get; set; }
DateTime? SoftDeletedDate { get; set; }
string SoftDeletedBy { get; set; }
string SoftDeletedInfo { get; set; }
}
Optional, but it’s also useful to have a concrete base table form OrmLite which I would annotate like:
public abstract class AuditBase : IAudit
{
public DateTime CreatedDate { get; set; }
[Required]
public string CreatedBy { get; set; }
[Required]
public string CreatedInfo { get; set; }
public DateTime ModifiedDate { get; set; }
[Required]
public string ModifiedBy { get; set; }
[Required]
public string ModifiedInfo { get; set; }
[Index] //Check if Deleted
public DateTime? SoftDeletedDate { get; set; }
public string SoftDeletedBy { get; set; }
public string SoftDeletedInfo { get; set; }
}
We can then create a base Request DTO that all Audit Create Services will implement:
[Authenticate]
[AutoPopulate(nameof(IAudit.CreatedDate), Eval = "utcNow")]
[AutoPopulate(nameof(IAudit.CreatedBy), Eval = "userAuthName")] //or userAuthId
[AutoPopulate(nameof(IAudit.CreatedInfo), Eval = "`${userSession.DisplayName} (${userSession.City})`")]
[AutoPopulate(nameof(IAudit.ModifiedDate), Eval = "utcNow")]
[AutoPopulate(nameof(IAudit.ModifiedBy), Eval = "userAuthName")] //or userAuthId
[AutoPopulate(nameof(IAudit.ModifiedInfo), Eval = "`${userSession.DisplayName} (${userSession.City})`")]
public abstract class CreateAuditBase<Table,TResponse> : ICreateDb<Table>, IReturn<TResponse> {}
These all call #Script Methods which you can add/extend yourself.
The *Info examples is a superflous example showing that you can basically evaluate any #Script expression. Typically you’d only save User Id or Username
The [AutoPopulate] attribute tells AutoCrud that you want the DB Table to automatically populate these properties. They’re are 3 different
- Value - A constant value that can be used in C# Attributes, e.g
Value="Foo"
- Expression - A Lightweight #Script Expression which is only evaluated once and cached globally, e.g.
Expression = "date(2001,1,1)", useful for values that can’t be defined in C# Attributes like DateTime, can be any #Script Method.
- Eval - A #Script Expression that’s cached per request. E.g.
Eval="utcNow" calls the utcNow Script method which returns DateTime.UtcNow which is cached for that request so all other utcNow expressions will return the same exact value.
- NoCache - Don’t cache the expression, evaluate it each time
The AST is cached for all #Script expressions used in the AutoCrud so it’s still fast to evaluate even when results are not cached.
Now lets say we want to layer on additional generic functionality, we can inherit the base class and extend it with additional functionality, e.g. if we want our table to support Multitenancy we could extend it with:
[AutoPopulate(nameof(IAuditTenant.TenantId), Eval = "Request.Items.TenantId")]
public abstract class CreateAuditTenantBase<Table,TResponse>
: CreateAuditBase<Table,TResponse> {}
Where TenantId is added in a Global Request Filter, e.g. after inspecting the authenticated UserSession to determine which tenant they belong to.
Anyway we can now easily implement custom “Audited” and “Multi Tenant” CRUD Services by inheriting these base Services, e.g: our custom Table that implements our AuditBase class with a TenantId to capture the Tenant the record is in:
public class RockstarAuditTenant : AuditBase
{
[Index]
public int TenantId { get; set; }
[AutoIncrement]
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int? Age { get; set; }
public DateTime DateOfBirth { get; set; }
public DateTime? DateDied { get; set; }
public LivingStatus LivingStatus { get; set; }
}
Our service can now implement our base Audit & Multitenant enabled service:
public class CreateRockstarAuditTenant
: CreateAuditTenantBase<RockstarAuditTenant, CreateRockstarResponse>
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int? Age { get; set; }
public DateTime DateOfBirth { get; set; }
}
And all the decorated properties will be automatically populated when creating the Rockstar.
Here are the generic and concrete services used for Updates:
[Authenticate]
[AutoPopulate(nameof(IAudit.ModifiedDate), Eval = "utcNow")]
[AutoPopulate(nameof(IAudit.ModifiedBy), Eval = "userAuthName")] //or userAuthId
[AutoPopulate(nameof(IAudit.ModifiedInfo), Eval = "`${userSession.DisplayName} (${userSession.City})`")]
public abstract class UpdateAuditBase<Table,TResponse>
: IUpdateDb<Table>, IReturn<TResponse> {}
[AutoFilter(nameof(IAuditTenant.TenantId), Eval="Request.Items.TenantId")]
public abstract class UpdateAuditTenantBase<Table,TResponse>
: UpdateAuditBase<Table,TResponse> {}
public class UpdateRockstarAuditTenant
: UpdateAuditTenantBase<RockstarAuditTenant, RockstarWithIdResponse>
{
public int Id { get; set; }
public string FirstName { get; set; }
public LivingStatus? LivingStatus { get; set; }
}
Note that the [AutoPopulate] properties only appear on the DB Table, not the Request DTO since we don’t want external API consumers to populate them.
Here are the base & concrete Services for Soft Deletes which is an UPDATE behind-the-scenes to populate the SoftDelete* fields:
[Authenticate]
[AutoPopulate(nameof(IAudit.SoftDeletedDate), Eval = "utcNow")]
[AutoPopulate(nameof(IAudit.SoftDeletedBy), Eval = "userAuthName")] //or userAuthId
[AutoPopulate(nameof(IAudit.SoftDeletedInfo), Eval = "`${userSession.DisplayName} (${userSession.City})`")]
public abstract class SoftDeleteAuditBase<Table,TResponse>
: IUpdateDb<Table>, IReturn<TResponse> {}
[AutoFilter(QueryTerm.Ensure, nameof(IAuditTenant.TenantId), Eval = "Request.Items.TenantId")]
public abstract class SoftDeleteAuditTenantBase<Table,TResponse>
: SoftDeleteAuditBase<Table,TResponse> {}
public class SoftDeleteAuditTenant
: SoftDeleteAuditTenantBase<RockstarAuditTenant, RockstarWithIdResponse>
{
public int Id { get; set; }
}
Some Apps prefer to never delete fields and instead mark records as deleted so it leaves an audit trail. Here’s an example of a “Real” DELETE as well:
[Authenticate]
[AutoFilter(QueryTerm.Ensure, nameof(IAuditTenant.TenantId), Eval = "Request.Items.TenantId")]
public class RealDeleteAuditTenant
: IDeleteDb<RockstarAuditTenant>, IReturn<RockstarWithIdResponse>
{
public int Id { get; set; }
public int? Age { get; set; }
}
Now if you’re creating Soft Delete & Multi tenant services you’ll want to ensure that every query doesn’t return deleted items and only records in their tenant, which we can implement with:
[Authenticate]
[AutoFilter(QueryTerm.Ensure, nameof(IAudit.SoftDeletedDate), Template = SqlTemplate.IsNull)]
[AutoFilter(QueryTerm.Ensure, nameof(IAuditTenant.TenantId), Eval = "Request.Items.TenantId")]
public abstract class QueryDbTenant<From, Into> : QueryDb<From, Into> {}
The [AutoFilter] lets you add pre-configured filters to the query, Ensure is a new OrmLite feature which basically forces always applying this filter, even if the query contains other OR conditions.
This base class will then let you create concrete queries that doesn’t return soft deleted rows and only returns rows from the same tenant as the authenticated user, e.g:
public class QueryRockstarAudit : QueryDbTenant<RockstarAuditTenant, Rockstar>
{
public int? Id { get; set; }
}
Refer to AutoQueryCrudTests.cs for other features, e.g. you can use [AutoMap] to map DTO properties to DB Table properties with a different name.
You can use AutoMapping’s AutoPopulate API to intercept the mapping between Request DTO and the Object Dictionary containing the fields that are persisted. It also supports Auto Guid properties and Optimistic Concurrency using OrmLite’s RowVersion support.
If you have any questions or issues with this new feature please post them in a new thread.