Bulletcode.NET

Database

Bulletcode.NET uses Entity Framework Core to perform database operations. Although Entity Framework is generally database-agnostic, Bulletcode.NET only supports the SQL Server database. The distributed cache provider (see the Session chapter) uses the SQL Server client library directly, and some other features provided by the framework are also specific to SQL Server.

Database context

The application’s database context should inherit CoreDbContext. The base context defines the following database tables:

• Users — stores local users (see Authentication)
• Events — is used for logging events (see Logging)
• Cache — stores session data (see Session)

The User entity contains an ID, email address, role, authorization key, password hash, and token used for resetting the password.

The Event entity contains an ID, date and time, log level, category name, event ID and name, message and scope data serialized in JSON format; it also contains columns which can be used to more efficiently filter events by certain scope data, such as request ID and path, IP address, user ID and name.

The Cache entity corresponds to the table schema used by the Distributed SQL Server Cache mechanism.

Extending entities

The application can extend the User and Event entities by creating custom classes which inherit the default ones, and defining additional properties is necessary. The custom entities can then be passed as template parameters to the CoreDbContext base class:

public class User : Bulletcode.Web.Core.Data.Models.User
{
    // add custom properties
}

public class Event : Bulletcode.Web.Core.Data.Models.Event
{
    // add custom properties
}

public class ApplicationDbContext : CoreDbContext<User, Event>
{
    public ApplicationDbContext( DbContextOptions options )
        : base( options )
    {
    }
}

The User class can override the virtual DisplayName property which is used to retrieve the user name which is stored in the ClaimsPrincipal object when the user is authenticated (see the Authentication chapter for more information), for example:

public class User : Bulletcode.Web.Core.Data.Models.User
{
    [Required]
    [StringLength( 100 )]
    public string Name { get; set; }

    public override string DisplayName => Name;
}

Initial user

The application’s database context should create the initial user which can be used to log into the application, for example:

protected override void OnModelCreating( ModelBuilder modelBuilder )
{
    base.OnModelCreating( modelBuilder );

    var userPasswordService = Database.GetService<IUserPasswordService<User>>();

    modelBuilder.Entity<User>( b => {
        var user = new User { Id = 1, Email = "admin@test.com", Role = "admin", IsEnabled = true };
        userPasswordService.InitializeSeedDataPassword( user, "asdqwe" );
        b.HasData( user );
    } );
}

The InitializeSeedDataPassword() function ensures that the password hash is generated using fixed salt, based on the user’s email address, and the authentication key is also fixed. This way, the password hash and authentication key won’t be updated every time a new migration is created.

Data services

Bulletcode.NET provides a number of services for reading and manipulating the User and Event entities:

• IUserService — implements basic CRUD operations on the Users table, including retrieving users by ID, email or the password reset token, adding, updating and deleting users.
• IUserPasswordService — implements database operations related to checking and updating password hashes and generating password reset tokens. The application should generally use the higher level IAccountService instead (see Authentication).
• IUserSearchService — allows retrieving a list of users with support for paging, sorting and filtering.
• IUserFormService — handles adding and updating user objects using the UserFormViewModel
• IEventService — implements basic CRUD operations on the Events table, including retrieving events by ID, adding events and deleting events older than the given threshold.
• IEventSearchService — allows retrieving a list of events with support for paging, sorting and filtering.

The basic CRUD services operate directly on the database context. The IUserService logs the modifications for auditing purposes.

The IUserSearchService and IEventSearchService return a DataProvider object, described below. The application can implement custom services which inherit the default implementation in order to return additional columns, handle additional filtering criteria, and support additional sorting attributes.

The IUserFormService is used to implement the user controller in the application. The application can implement a custom service which inherits the default implementation in order to map additional properties from the view model to the user entity and vice versa.

In order to use these services, the application must register them by calling AddUserService() and AddEventService():

services
    .AddUserService<ApplicationDbContext>()
    .AddEventService<ApplicationDbContext>();

The application’s database context must be passed to these methods as a template parameter.

If the application uses its own User and Event entities, the following overloaded versions of these methods must be called instead:

services
    .AddUserService<User, UserSearchViewModel, UserFormViewModel, ApplicationDbContext>()
    .AddEventService<Event, EventSearchViewModel, ApplicationDbContext>();

In addition to the entity types, the view model types used for filtering lists and for adding and editing users should also be specified. The application can use the default view model types implemented by Bulletcode.NET or custom classes which inherit them.

Customizing built-in services

The following example shows how an application can customize the IUserSearchService to include additional columns in the search results:

public class AppUserSearchService : UserSearchService<User, UserSearchViewModel, ApplicationDbContext>
{
    public AppUserSearchService( ApplicationDbContext dbContext )
        : base( dbContext )
    {
    }

    protected override IQueryable<User> ApplySelect( IQueryable<User> query )
    {
        return query.Select( u => new User
        {
            Id = u.Id,
            Name = u.Name,
            Email = u.Email,
            PhoneNumber = u.PhoneNumber,
            IsEnabled = u.IsEnabled,
            Role = u.Role,
        } );
    }
}

This service should be registered before calling AddUserService() to make sure it overrides the framework’s service:

services
    .AddScoped<IUserSearchService<User, UserSearchViewModel>, AppUserSearchService<User, UserSearchViewModel, ApplicationDbContext>>()
    .AddUserService<User, UserSearchViewModel, UserFormViewModel, ApplicationDbContext>();

The application can also add custom methods to the services inherited from Bulletcode.NET, for example:

public class AppUserService : UserService<User, ApplicationDbContext>
{
    private readonly ApplicationDbContext _dbContext;

    public AppUserService(
        ApplicationDbContext dbContext,
        ILogger<UserService<User, ApplicationDbContext>> logger
        ) : base( dbContext, logger )
    {
        _dbContext = dbContext;
    }

    public virtual async Task<TUser> GetUserBySsnAsync( string ssn )
    {
        return await _dbContext.Set<TUser>().Where( u => u.Ssn == ssn ).FirstOrDefaultAsync();
    }
}

The application can inject the service using the AppUserService class, to be able to use the custom method. However, the framework will use the IUserService interface to access this service, so it should be declared as an alias to ensure that the same instance of the service is used in both cases:

services
    .AddScoped<AppUserService>()
    .AddAlias<IUserService<User, ApplicationDbContext>, AppUserService>()
    .AddUserService<User, UserSearchViewModel, UserFormViewModel, ApplicationDbContext>();

See the Dependency Injection chapter for more information about service aliases.

Data provider

Bulletcode.NET implements a Data Provider mechanism, which is an enumerable collection of objects, with support for paging and sorting. This mechanism is used by the GridView view component in applications using Razor views, and the GridView component in ClientView applications. Bulletcode.NET also provides an data provider implementation based on Entity Framework database queries. This makes it easy to implement grids with efficient filtering, paging and sorting implemented at the database level.

IDataProvider inherits the IEnumerable interface, and contains a PrepareAsync() method, which can be used to asynchronously populate the data from the data source (such as the database), a DataType property which specifies the type of objects, and optional Sorting and Pagination properties which provide information about the sort order and available sort attributes, and the current page, page size, and the total number of objects.

DataProvider<T> is the basic implementation of the IDataProvider interface. It can be used directly by passing the data to the constructor, and optionally setting the Pagination and Sorting properties. It can also be inherited in order to implement custom logic for populating the data; in that case, the GetDataAsync() method should be overridden.

QueryDataProvider

QueryDataProvider<T> is the implementation of the IDataProvider interface which uses an Entity Framework query as the data source. If a Pagination object is passed, its TotalCount property is calculated by calling CountAsync() on the IQueryable. In addition, Skip() and Take() are called when retrieving the data, according to the specified Page and PageSize. If a Sorting object is passed, the query is sorted using the specified Order.

The PaginationBuilder can be used to create a Pagination object based on query string parameters passed to the request. The application can override the default page size (which is 10, by default), and the minimum and maximum page size which can be specified using a query string parameter. The parameter names can also be overridden, in case a page contains multiple grids; the default names are "Page" and "PageSize".

The SortingBuilder<T> can be used to create a Sorting object based on the specified sort attributes and the query string parameter. For example:

builder
    .SetDefaultOrder( "email" )
    .AddAttribute( "email", query => query.OrderBy( u => u.Email ) )
    .AddAttribute( "role", query => query.OrderBy( u => u.Role ).ThenBy( u => u.Email ) );

For each attribute, one or more order criteria can be specified. The default sort order can be specified, and, if necessary, the name of the query string parameter can be overridden; the default name is "Sort". If the attribute is prefixed by a "-", the sort order is reversed; for example "-email" means that users are sorted by email in descending order.

SearchService

SearchService<T> is an abstract base class for data services for retrieving a list of objects with support for paging, sorting and filtering. A data service which inherits this method can override the ConfigurePagination() method to change the default page size, and other paging options, and override the ConfigureSotring() method to specify the default sort order and sort attributes. For example:

public class ExampleSearchService : SearchService<Example>
{
    private readonly ApplicationDbContext _dbContext;

    public ExampleSearchService( ApplicationDbContext dbContext )
    {
        _dbContext = dbContext;
    }

    public DataProvider<Example> Search( ExampleSearchViewModel model, HttpRequest request )
    {
        IQueryable<Example> query = _dbContext.Examples;

        query = query.Select( e => new Example
        {
            Id = e.Id,
            Name = e.Name,
        } );

        if ( !string.IsNullOrEmpty( model.Name ) )
            query = query.Where( e => e.Name.Contains( model.Name ) );

        return CreateDataProvider( query, request );
    }

    protected override void ConfigureSorting( SortingBuilder<Example> builder )
    {
        builder
            .SetDefaultOrder( "name" )
            .AddAttribute( "name", query => query.OrderBy( a => a.Name ).ThenBy( a => a.Id ) );
    }
}

In search services implemented by Bulletcode.NET, the Select() clause and the Where() clauses are implemented in separate virtual methods, ApplySelect() and ApplyFilter(), to make it easier for the application to override them if additional columns must be retrieved or additional filter criteria must be implemented, as shown in the code example in the Customizing built-in services section above.

Utilities

CaseSensitive attribute

In addition to standard .NET data annotations, such as Key and StringLength, Entity Framework Core defines some additional attributes, such as Precision and Unicode. Bulletcode.NET defines a CaseSensitive attribute, which indicates that a column should use case-sensitive collation. For example:

[Unicode( false )]
[StringLength( 64 )]
[CaseSensitive]
public string Token { get; set; }

To use this attribute, call the UseAttributeConventionSet() extension method of the DbContextOptionsBuilder:

services.AddDbContext<ApplicationDbContext>( options => options
    .UseSqlServer( configuration.GetConnectionString( "ApplicationDbContextConnection" ) )
    .UseAttributeConventionSet()
);

By default, the "Latin1_General_CS_AS" collation is used for case-sensitive columns. The application can configure a custom collation by passing a callback function to the UseAttributeConventionSet() method.

HasJsonConversion method

When data is stored in a database column in JSON format, it can be automatically deserialized when reading from the database, and serialized when writing to the database. Use the HasJsonConversion() extension method of the PropertyBuilder to enable such conversion when creating the model for the database context:

modelBuilder.Entity<Example>( b => {
    b.Property( b => b.Data ).HasJsonConversion();
} );

You can also specify custom JsonSerializerOptions that should be used by the converter:

var options = new JsonSerializerOptions( JsonSerializerDefaults.Web );

modelBuilder.Entity<Example>( b => {
    b.Property( b => b.Data ).HasJsonConversion( options );
} );

EmbeddedSql method

When the application uses SQL stored procedures or functions, it’s useful to place them in separate .sql files, which are embedded into the application. The EmbeddedSql() extension method can be used to load the SQL string from the specified embedded resource and execute it when applying a migration, for example:

public partial class ExampleProcedure : Migration
{
    protected override void Up( MigrationBuilder migrationBuilder )
    {
        migrationBuilder.EmbeddedSql( this, "20250612183907_ExampleProcedure.sql" );
    }

    protected override void Down( MigrationBuilder migrationBuilder )
    {
        migrationBuilder.Sql( "DROP PROCEDURE dbo.ExampleProcedure" );
    }
}

The .sql file should be located in the same directory and should have the same file name as the migration class. The migration object and the full name of the .sql file should be passed to the EmbeddedSql() method.

The application’s .csproj file should contain the following item to include the .sql files as embedded resources:

<EmbeddedResource Include="Data\Migrations\*.sql" />

LongRunningCommand method

When executing a potentially long-running SQL query, you can call the LongRunningCommand() extension method of the DbContext:

public async Task DeleteMessagesOlderThan( DateTime threshold )
{
    using var _ = _dbContext.LongRunningCommand( 60 );

    await _dbContext.Set<Message>().Where( m => m.DateTime < threshold ).ExecuteDeleteAsync();
}

All queries executed until the LongRunningCommand object is disposed use the specified timeout, which can be specified in seconds, or as a TimeSpan value.

IsConstraintConflict method

The IsConstraintConflict() extension method of the DbUpdateException can be used to detect if the exception was caused by the constraint conflict error (which has error code 547 in SQL Server). This error occurs, for example, when attempting to delete a row which is referenced by another table, and the reference is not configured with CASCADE or SET NULL option.

The application’s controller can detect and handle such error by displaying an appropriate error page, for example:

try
{
    await _userService.DeleteUserAsync( user );
}
catch ( DbUpdateException ex )
{
    if ( ex.IsConstraintConflict() )
        return Conflict();
    throw;
}

See the Diagnostics chapter for information about creating an error page for the application.