Ormlite createdBy & lastUpdatedBy

Hi,
I have an insertFilter & updateFilter to set audit time:

   OrmLiteConfig.InsertFilter = (dbCmd, row) =>
     {
            var entity = row as BaseEntity;
            if (entity != null)
            {
                entity.CreatedAt = DateTime.UtcNow;
            }
     }

        OrmLiteConfig.UpdateFilter = (dbCmd, row) =>
        {
            var entity = row as BaseEntity;
            if (entity != null)
            {
                entity.UpdatedAt = DateTime.UtcNow;
            }
        };

I also would like to save the user info createBy & lastUpdatedBy.
My users tables are create as the following: container.Resolve<IAuthRepository>().InitSchema();
How do I pull the current user in the filter and create a reference in the database to a servicestack user?
Thankls

It’s not possible unless you’re using a Host Framework that supports HTTP Request Context access via a singleton.

If your Host Framework supports this, you can use the RequestContext.Instance.Items collection to store and access Request Info you need to make available, e.g:

What do you mean by: “If your Host Framework supports this”
I am using service stack framework. (AppHost)

ServiceStack runs on Multiple hosts which one are you using?

Or if you started from a ServiceStack template, which one was it?

app.UseServiceStack(new AppHost
            {
                AppSettings = new NetCoreAppSettings(Configuration)
            });

this is how I start my app.
Thanks

You’re using .NET Core which has disabled Request Context singletons, I’d recommend not relying on it otherwise follow my first link to see how you can enable it.

So, if you recommendation is avoid it, what is the way to get the current user on run-time?

You can’t access it statically, you need the IRequest context instance (available from base.Request in Services and injected in most filters and hooks) which will let you resolve the session with req.GetSession() which has the info you need for IsAuthenticated users.

To where and how to resolve it. I don’t see anything inside IDbCommand which is the filter context

That is a static OrmLite delegate, it’s not a ServiceStack Filter or Hook nor does it have a dependency on ServiceStack.

As I said you can’t access the runtime Request Context from static contexts.

My 2nd link showed the approach I’d take by calling an extension method to inject the audit info before inserting the record, e.g:

db.Insert(new Record { ... }.WithAudit(Request));

//Extension Method Example:
public static class AuditExtensions
{
    public static T WithAudit<T>(this T row, IRequest req) where T : IAudit => 
        row.WithAudit(req.GetSession().UserAuthId);

    public static T WithAudit<T>(this T row, string userId) where T : IAudit
    {
        row.CreatedBy = userId;
        row.CreatedDate = DateTime.UtcNow;
        return row;
    }
}

Sorry. I have missed that.
What about having a reference to users table created by container.Resolve<IAuthRepository().InitSchema();
?
Thanks

Here’s the source code for the OrmLiteAuthRepository, it uses the UserAuth and UserAuthDetails to query the user tables by default but can also be extended to use custom User Tables

1 Like

Is it possible to create a dependency that depends on I request?

Something like (Pseudo…)

public class AuthContext{
    public IRequest  Request { get; set; }
    public int CurrentUserId { get {return Request.GetSession().UserAuthId} }
}

Inject it like:

container.RegisterAutoWired<AuthContext>().ReusedWithin(ReuseScope.Request);

Then I’ll be able to read AuthContext in all services and repositories…

Thanks

No, IRequest is the runtime HTTP Request Context not an IOC dependency. Access it from base.Request in your Services or the IRequest param in ServiceStack filters/hooks/etc.

Is your WithAudit solution will work for relational saves?

Such as SaveAsync(…,true)

You can see the full source code (and have full control over) what it does, It’s not related to OrmLite it’s simply an extension method that populates a C# instance.

If you’re asking if it populates child properties of the class, it doesn’t as can be seen by the source code. You’ll either need to call the ext method on the instances you want to save or use some reflection to auto populate any properties that implement IAudit or whatever custom Interface or base class your App is using. e,g:

db.Insert(new Record { 
    ... 
    ChildItems = childItems.Map(x => x.WithAuth(Request)),
}.WithAudit(Request));

Or this can be further condensed to:

    ChildItems = childItems.WithAudit(Request),

When you add a new ext method:

public static class AuditExtensions
{
    public static List<T> WithAudit<T>(this List<T> rows, IRequest req) where T : IAudit => 
        rows.Map(x => x.WithAudit(req.GetSession().UserAuthId));

    public static T WithAudit<T>(this T row, IRequest req) where T : IAudit => 
        row.WithAudit(req.GetSession().UserAuthId);

    public static T WithAudit<T>(this T row, string userId) where T : IAudit
    {
        row.CreatedBy = userId;
        row.CreatedDate = DateTime.UtcNow;
        return row;
    }
}

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 Entry
  • IUpdateDb<Table> - Update existing Table Entry
  • IDeleteDb<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 Key
  • T 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.

Hi.
Really appreciate your effort!!!
Im right now in a corona virus situation…
Ill be checking that soon.
Thanks again!

Hi.
Since the new feature does not have any capabilities in saving related.
Im still struggling to make a simple audit to work. createdBy lastUpdatedBy.
Is there any chance that the filters will not be static in the future? or maybe to find a good way to have security context in the entire app?

My problem is to set the audit for relateds. Many To One and One.
I am saving a top object and would like to set the entire tree with createdBy lastUpdatedBy.
Any chance this would be available in the future?
Thanks
Ps - it can be solved with reflection and a some kind of infrastrcture - but I think it would definetly be a code smell.
Security context is important in a framework that provides security.
Thanks again

The Example above shows examples of using [AutoPopulate] to populate audit fields, there’s also support for Executable Crud Audit Events with the new OrmLiteCrudEvents provider which gives you an executable audit history for your AutoCrud Services for essentially no effort.

If you need to save related fields, then you can’t use AutoCrud and would need to create your own custom implementation at which point you would populate the audit fields yourself. There shouldn’t be any reflection or filters used as your custom implementation would be populating the Audit fields of the concrete types and saving them directly. I also don’t see why you’d use reflection to populate your own Audit fields, you know what the structure of your audit fields are, so have your tables that contain them implement the same interface.