Base Repository Classes - denmitchell/EDennis.AspNetCore.Base GitHub Wiki
EDennis.AspNetCore.Base provides four base repository classes, which cross writeability with temporality. Two of the base repos are writeable and two are readonly. Two of the base repos have temporal support (write to history tables) and two do not have temporal support. The table below summarizes the four repo types.
Importantly, while the two writeable repo classes can be used with any supported database, including relational and in-memory databases, the two readonly repo classes can be used with relational databases only.
WriteableRepo provides methods for reading from and writing to current-record tables.
WriteableRepo has two type parameters:
| Type Parameter | Constraints |
|---|---|
TEntity |
TEntity is a class with a default constructor and implements IHasSysUser |
TContext |
TContext extends Microsoft.EntityFrameworkCore.DbContext |
The WriteableRepo constructor accepts two arguments:
- TContext -- any subclass of Entity Framework Core's DbContext
- ScopeProperties -- a class designed to communicate the user name and other data (e.g., headers) from one or more MVC filters to the repo. This class should be dependency injected with a scoped lifetime.
public WriteableRepo(TContext context, ScopeProperties scopeProperties)WriteableRepo exposes two public properties.
| Property | Description |
|---|---|
public TContext Context { get; set; } |
Provides direct access to the DbContext |
public ScopeProperties ScopeProperties { get; set; } |
An object holding data injected into the repo |
WriteableRepo provides basic CRUD methods, which automatically save data to the database when needed.
| Method | Description |
|---|---|
public virtual TEntity GetById(params object[] keyValues) |
Retrieve a record by the primary key |
public virtual async Task<TEntity> GetByIdAsync(params object[] keyValues) |
Asynchronous version of GetById |
public virtual bool Exists(params object[] keyValues) |
Determine if a record with the provided key exists |
public virtual async Task<bool> ExistsAsync(params object[] keyValues) |
Asynchronous version of Exists |
public virtual TEntity Create(TEntity entity) |
Inserts a new TEntity record into the database |
public virtual async Task<TEntity> CreateAsync(TEntity entity) |
Asynchronous version of Create |
public virtual TEntity Update(TEntity entity, params object[] keyValues) |
Replaces the TEntity record with data from the provided object |
public virtual async Task<TEntity> UpdateAsync(TEntity entity, params object[] keyValues) |
Asynchronous version of Update |
public virtual void Delete(params object[] keyValues) |
Delete the record with the provided primary key |
public virtual async Task DeleteAsync(params object[] keyValues) |
Asynchronous version of Delete |
public virtual List<dynamic> GetFromDynamicLinq(string where, string orderBy, string select, int? skip, int? take) |
Use Dynamic Linq to query the database |
public virtual async Task<List<dynamic>> GetFromDynamicLinqAsync(string where, string orderBy, string select, int? skip, int? take) |
Asynchronous version of GetFromDynamicLinq |
The EDennis.AspNetCore.Base library provides an IServiceCollection extension method called AddDbContexts (plural), which enables the developer to configure 1-5 DbContext subclasses per statement. This extension method requires that the developer pass IConfiguration and IHostingEnvironment as parameters. This extension method also sets up dependency injection to TestDbContextCache when the environment is development. The EDennis.AspNetCore.Base library provides an AddRepos method that allows the developer to configure 1-5 repos per statement. This extension method also sets up dependency injection for ScopeProperties.
services.AddDbContexts<HrContext,HrHistoryContext>(Configuration, HostingEnvironment);
services.AddRepos<EmployeeRepo,PositionRepo,EmployeePositionRepo>();When using the AddDbContexts (plural) IServiceCollection extension method, the developer must ensure that the app's Configuration contains a ConnectionStrings section where each DbContext class and its connection string is represented as a key/value pair.
Sample appsettings.Development.json:
"ConnectionStrings": {
"HrContext": "Server=(localdb)\\mssqllocaldb;Database=Hr;Trusted_Connection=True;",
},Aside from some statements supporting unit/integration testing, WriteableRepo does not have any special requirements for the DbContext. The EDennis.AspNetCore.Base library does provide an abstract implementation of IDesignTimeDbContextFactory<TContext> that allows one to have a single constructor accepting DbContextOptions<YourDbContextClass> as a parameter (see MigrationsExtensionsDbContextDesignTimeFactory. The library also provides a MaxPlusOneValueGenerator to make it easier to use in-memory databases for testing models backed by sequences or identity specifications.
WriteableTemporalRepo extends WriteableRepo and provides some additional methods related to temporal operations.
WriteableTemporalRepo has three type parameters:
| Type Parameter | Constraints |
|---|---|
TEntity |
TEntity is a class with a default constructor and implements IEFCoreTemporalModel |
TContext |
TContext extends Microsoft.EntityFrameworkCore.DbContext |
THistoryContext |
THistoryContext extends Microsoft.EntityFrameworkCore.DbContext |
The WriteableTemporalRepo constructor accepts three arguments:
- TContext -- any subclass of Entity Framework Core's DbContext. This is the current-records context.
- THistoryContext -- any subclass of Entity Framework Core's DbContext. This is the history-records context.
- ScopeProperties -- a class designed to communicate the user name and other data (e.g., headers) from one or more MVC filters to the repo. This class should be dependency injected with a scoped lifetime.
public WriteableTemporalRepo(TContext context, THistoryContext historyContext, ScopeProperties scopeProperties)WriteableTemporalRepo exposes the two public properties inherited from WriteableRepo, as well as a temporal-only property.
| Property | Description |
|---|---|
public THistoryContext HistoryContext { get; set; } |
Provides direct access to the DbContext for the history records |
WriteableTemporalRepo provides all of the CRUD methods from WriteableRepo, as well as some temporal-related methods.
| Method | Description |
|---|---|
public virtual bool WriteUpdate(TEntity next, TEntity current) |
Allows configuration of when records are written to the history table during updates. The default is always. |
public virtual bool WriteDelete(TEntity current) |
Allows configuration of when records are written to the history table during deletes. The default is always. |
public List<TEntity> QueryAsOf(DateTime asOf, Expression<Func<TEntity, bool>> predicate, int pageNumber = 1, int pageSize = 10000, params Expression<Func<TEntity, dynamic>>[] orderSelectors) |
Executes a query, retrieving current and/or history records as of a particular date/time. NOTE: See below for a description of Order Selectors |
public List<TEntity> QueryAsOf(DateTime from, DateTime to, Expression<Func<TEntity, bool>> predicate, int pageNumber = 1, int pageSize = 10000, params Expression<Func<TEntity, dynamic>>[] orderSelectors) |
Executes a query, retrieving current and/or history records within the provided date/time range. NOTE: See below for a description of Order Selectors |
TEntity GetByIdAsOf(DateTime asOf, params object[] key) |
Retrieves a specific record by primary key as of the provided date/time. |
List<TEntity> GetByIdHistory(params object[] key) |
Retrieves the current value and entire history of a specific record by primary key. |
The temporal queries provided above allow developers to include "order selectors," which is an alternative way of representing order-by expressions in Linq. With order selectors, you use the lambda parameter name to indicate direction (d, desc, or descending = descending; a, asc, or ascending = ascending). For example to order by Name ascending and SysStart descending, the lamdba expression could look like the following:
asc => asc.Name, desc => desc.SysStart
//or
a => a.Name, d => d.SysStart
//or
ascending => ascending.Name, descending => descending.SysStartThe EDennis.AspNetCore.Base library provides an IServiceCollection extension method called AddDbContexts (plural), which enables the developer to configure 1-5 DbContext subclasses per statement. This extension method requires that the developer pass IConfiguration and IHostingEnvironment as parameters. This extension method also sets up dependency injection to TestDbContextCache when the environment is development. The EDennis.AspNetCore.Base library provides an AddRepos method that allows the developer to configure 1-5 repos per statement. This extension method also sets up dependency injection for ScopeProperties.
services.AddDbContexts<HrContext,HrHistoryContext>(Configuration, HostingEnvironment);
services.AddRepos<EmployeeRepo,PositionRepo,EmployeePositionRepo>();When using the AddDbContexts (plural) IServiceCollection extension method, the developer must ensure that the app's Configuration contains a ConnectionStrings section where each DbContext class and its connection string is represented as a key/value pair. Even if two DbContext classes share the same connection string, each DbContext class must be represented a a separate entry in the ConnectionStrings section.
Sample appsettings.Development.json:
"ConnectionStrings": {
"HrContext": "Server=(localdb)\\mssqllocaldb;Database=Hr;Trusted_Connection=True;",
"HrHistoryContext": "Server=(localdb)\\mssqllocaldb;Database=Hr;Trusted_Connection=True;",
},The WriteableTemporalRepo class requires separate, dedicated DbContext classes for both current records and history records. In general, the developer will find it convenient to create a third DbContext class as an abstract base class. The three DbContext classes include
- An abstract base DbContext class holding configurations common to both the current-records context and the history-records context (e.g., property configurations and DbSet or DbQuery declarations)
- A DbContext class for current records that extends the abstract base class, configures the table/view mappings and primary key mappings, and includes any optional support for testing (e.g.,
HasDataandHasValueGenerator) - A DbContext class for history records that extends the abstract base class, configures the table/view mappings and primary key mappings, and includes any optional support for testing (e.g.,
HasData). Note: If the underlying model for the class has navigation properties, by convention these navigation properties would generate foreign keys. To prevent foreign keys in the history context, the developer can include a call tomodelBuilder.IgnoreNavigationProperties().
Although the developer is free to choose a different primary key for the history tables, it is recommended to extend the primary key of the corresponding current-records table by adding the SysStart property to the key. Also, while the developer is free to choose a totally separate database for the history context, he/she may find it convenient to target a separate schema (e.g., 'dbo_history') in the same database.
For example configurations, see the EDennis.Samples.Hr.InternalApi2 project.
ReadonlyRepo provides methods for reading from relational databases.
ReadonlyRepo has two type parameters:
| Type Parameter | Constraints |
|---|---|
TEntity |
TEntity is a class with a default constructor |
TContext |
TContext extends Microsoft.EntityFrameworkCore.DbContext |
The ReadonlyRepo constructor accepts one argument:
- TContext -- any subclass of Entity Framework Core's DbContext
public ReadonlyRepo(TContext context)ReadonlyRepo exposes two public properties.
| Property | Description |
|---|---|
public TContext Context { get; set; } |
Provides direct access to the DbContext |
public IQueryable<TEntity> Query { get => Context.Query<TEntity>(); } |
Provides direct access to the DbQuery |
By exposing the Query property, the library gives developers full freedom to compose IQueryable expressions with Linq. Of course, this means that the ReadonlyRepo class is a very thin abstraction over Entity Framework.
ReadonlyRepo provides four read methods that apply to any relational database and two read methods that work with SQL Server only.
| Method | Description |
|---|---|
public virtual List<TEntity> GetFromSql(string sql) |
Retrieves a set of records using SQL |
public virtual async Task<List<TEntity>> GetFromSqlAsync(string sql) |
Asynchronous version of GetFromSql |
public virtual T GetScalarFromSql<T>(string sql) |
Retrieves a scalar value using SQL |
public virtual async Task<T> GetScalarFromSqlAsync<T>(string sql) |
Asynchronous version of GetScalarFromSql |
public virtual string GetFromJsonSql(string fromJsonSql) |
Retrieves a JSON string using the provided FROM JSON SQL Server SELECT statement† |
public virtual async Task<string> GetFromJsonSqlAsync(string fromJsonSql) |
Asynchronous version of GetFromJsonSql† |
public virtual List<dynamic> GetFromDynamicLinq(string where, string orderBy, string select, int? skip, int? take) |
Use Dynamic Linq to query the database |
public virtual async Task<List<dynamic>> GetFromDynamicLinqAsync(string where, string orderBy, string select, int? skip, int? take) |
Asynchronous version of GetFromDynamicLinq |
public virtual List<dynamic> GetFromStoredProcedure(string spName, IEnumerable<KeyValuePair<string,string>> parms) |
Call a stored procedure with parameters |
public virtual async Task<List<dynamic>> GetFromStoredProcedureAsync(string spName, IEnumerable<KeyValuePair<string,string>> parms) |
Asynchronous version of GetFromStoredProcedure |
public virtual string GetJsonColumnFromStoredProcedure(string spName, IEnumerable<KeyValuePair<string,string>> parms) |
Call a stored procedure that returns a JSON string |
public virtual async Task<string> GetJsonColumnFromStoredProcedureAsync(string spName, IEnumerable<KeyValuePair<string,string>> parms) |
Asynchronous version of GetJsonColumnFromStoredProcedure |
† The implementation of these methods work with SQL Server only. The developer is free to replace the implementation with something that works with another relational database (e.g., using Oracle json_array or json_object functions).
The EDennis.AspNetCore.Base library provides an IServiceCollection extension method called AddDbContexts (plural), which enables the developer to configure 1-5 DbContext subclasses per statement. This extension method requires that the developer pass IConfiguration and IHostingEnvironment as parameters. This extension method also sets up dependency injection to TestDbContextCache when the environment is development. The EDennis.AspNetCore.Base library provides an AddRepos method that allows the developer to configure 1-5 repos per statement. This extension method also sets up dependency injection for ScopeProperties.
services.AddDbContexts<HrContext,HrHistoryContext>(Configuration, HostingEnvironment);
services.AddRepos<EmployeeRepo,PositionRepo,EmployeePositionRepo>();When using the AddDbContexts (plural) IServiceCollection extension method, the developer must ensure that the app's Configuration contains a ConnectionStrings section where each DbContext class and its connection string is represented as a key/value pair. Even if two DbContext classes share the same connection string, each DbContext class must be represented a a separate entry in the ConnectionStrings section.
Sample appsettings.Development.json:
"ConnectionStrings": {
"HrContext": "Server=(localdb)\\mssqllocaldb;Database=Hr;Trusted_Connection=True;",
},ReadonlyRepo assumes that its associated DbContext class will define a DbQuery for the model class associated with the repo. Generally speaking, the developer will define separate model classes for the view and the underlying table and also create mappings to the view (via modelBuilder.Query<...>.ToView(...)) and underlying table (via modelBuilder.Entity<...>.ToTable(...)). The model class and mappings for the table are used to create the table in LocalDb for testing purposes.
As mentioned above, the EDennis.AspNetCore.Base library provides an abstract implementation of IDesignTimeDbContextFactory<TContext> that allows one to have a single constructor accepting DbContextOptions<YourDbContextClass> as a parameter (see MigrationsExtensionsDbContextDesignTimeFactory.
ReadonlyTemporalRepo extends ReadonlyRepo and provides some additional methods related to temporal operations.
ReadonlyTemporalRepo shares the same three type parameters with WriteableTemporalRepo:
| Type Parameter | Constraints |
|---|---|
TEntity |
TEntity is a class with a default constructor and implements IEFCoreTemporalModel |
TContext |
TContext extends Microsoft.EntityFrameworkCore.DbContext |
THistoryContext |
THistoryContext extends Microsoft.EntityFrameworkCore.DbContext |
The ReadonlyTemporalRepo constructor accepts three arguments:
- TContext -- any subclass of Entity Framework Core's DbContext. This is the current-records context.
- THistoryContext -- any subclass of Entity Framework Core's DbContext. This is the history-records context.
public ReadonlyTemporalRepo(TContext context, THistoryContext historyContext)WriteableTemporalRepo exposes the two public properties inherited from WriteableRepo, as well as a temporal-only property.
| Property | Description |
|---|---|
public THistoryContext HistoryContext { get; set; } |
Provides direct access to the DbContext for the history records |
ReadonlyTemporalRepo inherits all of the methods from ReadonlyRepo, as well as two temporal-related methods.
| Method | Description |
|---|---|
public List<TEntity> QueryAsOf(DateTime asOf, Expression<Func<TEntity, bool>> predicate, int pageNumber = 1, int pageSize = 10000, params Expression<Func<TEntity, dynamic>>[] orderSelectors) |
Executes a query, retrieving current and/or history records as of a particular date/time. NOTE: See below for a description of Order Selectors |
public List<TEntity> QueryAsOf(DateTime from, DateTime to, Expression<Func<TEntity, bool>> predicate, int pageNumber = 1, int pageSize = 10000, params Expression<Func<TEntity, dynamic>>[] orderSelectors) |
Executes a query, retrieving current and/or history records within the provided date/time range. NOTE: See above for a description of Order Selectors |
Note that ReadonlyTemporalRepo does not provide GetByIdAsOf or GetByIdHistory because the underlying implementation uses an DbQuery, which does not expose a primary key.
The EDennis.AspNetCore.Base library provides an IServiceCollection extension method called AddDbContexts (plural), which enables the developer to configure 1-5 DbContext subclasses per statement. This extension method requires that the developer pass IConfiguration and IHostingEnvironment as parameters. This extension method also sets up dependency injection to TestDbContextCache when the environment is development. The EDennis.AspNetCore.Base library provides an AddRepos method that allows the developer to configure 1-5 repos per statement. This extension method also sets up dependency injection for ScopeProperties.
When using the AddDbContexts (plural) IServiceCollection extension method, the developer must ensure that the app's Configuration contains a ConnectionStrings section where each DbContext class and its connection string is represented as a key/value pair. Even if two DbContext classes share the same connection string, each DbContext class must be represented a a separate entry in the ConnectionStrings section.
services.AddDbContexts<HrContext,HrHistoryContext>(Configuration, HostingEnvironment);
services.AddRepos<EmployeeRepo,PositionRepo,EmployeePositionRepo>();Sample appsettings.Development.json:
"ConnectionStrings": {
"HrContext": "Server=(localdb)\\mssqllocaldb;Database=Hr;Trusted_Connection=True;",
"HrHistoryContext": "Server=(localdb)\\mssqllocaldb;Database=Hr;Trusted_Connection=True;",
},The ReadonlyTemporalRepo class requires separate, dedicated DbContext classes for both current records and history records. In general, the developer will find it convenient to create a third DbContext class as an abstract base class. The three DbContext classes include
- An abstract base DbContext class holding configurations common to both the current-records context and the history-records context (e.g., property configurations and DbQuery declarations)
- A DbContext class for current records that extends the abstract base class, configures the table/view mappings and primary key mappings.
- A DbContext class for history records that extends the abstract base class, configures the table/view mappings and primary key mappings. Note: If the underlying model for the class has navigation properties, by convention these navigation properties would generate foreign keys. To prevent foreign keys in the history context, the developer can include a call to
modelBuilder.IgnoreNavigationProperties().
Although the developer is free to choose a different primary key for the history tables, it is recommended to extend the primary key of the corresponding current-records table by adding the SysStart property to the key. Also, while the developer is free to choose a totally separate database for the history context, he/she may find it convenient to target a separate schema (e.g., 'dbo_history') in the same database.
For example configurations, see the EDennis.Samples.Hr.InternalApi2 project.