CRUD Repository

namespace: MvcControlsToolkit.Core.Business.Utilities

The DefaultCRUDRepository class makes easy all CRUD operations, and paged data retrieval on a EF DBSets possibly connected with other DBSets. It is the default implementation of the ICRUDRepository interface, so, please, refer to the interface documentation for the exact signature of all its methods. Here we discuss its peculiarities and usage.

The class itself has two generic arguments, the DBContext type and the DBSet type, while all its methods have the ViewModel type, and/or its principal key type as generic arguments. The class constructor accepts the DBContext, the DBSet and also optional "read access" and "modify access"conditions whose main purpose is to filter records the user has right to read and/or modify.

Retrieval operations use the projection on a ViewModel" extension . Customized projections on a specific ViewModel may be declared by calling the class DeclareProjection static method (see the "Repository" tab in the example at the end of the page).
Please refer to the Projection operator documentation for more details on how to specify a projection. Important: in case the projection contains conditional operators, and data need to be filtered/sorted/grouped you should call also the DeclareQueryProjection to specify how to project data on the ViewModel used for queries. Like in the example below:

DeclareProjection(
        m => m.Maintenance == null ?
        new ProductViewModel{} :
        new ProductMaintenanceViewModel
        {
            MaintenanceYearlyRate = (decimal)m.Maintenance.YearlyRate
        });
DeclareQueryProjection(m => new ProductViewModel { }, m => m.Id);

Where the second argument of DeclareQueryProjection specifies the ViewModel key.
In fact in case query might return several different item types LinQ queries must use a common ancestor of all item types.

Update operations include: Add<T>, Delete<U>, Update<T>, and UpdateList<T>. UpdateList<U> accepts two lists of ViewModels: the original list before some CRUD modifications are applied by the user, and the version of the same list after all user modifications. It uses these two lists to autodetect all Add, Update and Deletes performed by the user and applies all of them to the DBSet. Typically, the unmodified version of the list is obtained by storing the original list in the Razor View with the help of the store-model TagHelper.

The SaveChanges() method saves changes to the DB and, if needed, copies the newly created keys of all Added DB models back in their related ViewModels. The UpdateKeys() method may be called whenever changes are saved by another repository, since it performs just the keys copy operation.

Single entities connected with the entity being updated are updated automatically if the ViewModel contains data for updating them (ViewModel property name must be the concatenation of all property names on the path to the connected entity to update). ViewModels decide whih connected property to update by implementing the IUpdateConnections interface whose unique method bool MayUpdate(string prefix) returns true whenever prefix is the name of a property containing a connected entity that must be updated.

The first time a ViewModel is passed for update the code for transferring its data to the corresponding DB model (and connected DB models) is generated dynamically, compiled and the compiled code is cached. Thus all update operations are as fast as handwritten operations.

The DefaultCRUDRepository class has also methods to retrive data, namely:

  • Task<T> GetById<T, U>(U key); that gets a single item given its U type key and the ViewModel T type
  • Task<DataPage<T>> GetPage<T>(Expression<Func<T, bool>> filter, Func<IQueryable<T>, IOrderedQueryable<T>> sorting, int page, int itemsPerPage, Func<IQueryable<T>, IQueryable<T>> grouping=null) ; that gets a page of data given the page number, the items per page, a filter to apply (expressed in terms of ViewModel properties), and a chain of sorting operators. Please refer to the ICRUDRepository documentation for a description of the DataPage<T>> class. Below an example of usage with no filter applied:

    await Repository.GetPage<ProductViewModel>(
            null,
            q => q.OrderBy(m => m.Name),
            currPage, 3)
                    

    There is also a version of GetPage with two generics in case the grouping ViewModel is different from the item ViewModel. Please refer to the ICRUDRepository documentation for details.

The DefaultCRUDRepository constructor may be passed two optional filters:


        public DefaultCRUDRepository(D dbContext, DbSet<T>
            table,
            Expression<Func<T, bool>> accessFilter=null,
            Expression<Func<T, bool>> selectFilter = null)
        

Both of them must be expressed in terms of the DB model. accessFilter is applied to all DBSet update operations, while the selectFilter is applied to all retrieval operations. Their intended meaning is to restrict record access to the currently logged user. When needed, they are defined in the repository that inherit from DefaultCRUDRepository. More specifically, the inheriting repository specifies IHttpContextAccessor httpContextAccessor, in its constuctor. Then, when the repository is created, httpContextAccessor is filled by the dependency injection engine, and the inheriting repository may use httpContextAccessor.HttpContext.User to define accessFilter and selectFilter to be passed to the DefaultCRUDRepository constructor in terms of the current logged user id.

All CRUD repository operations are implementations of the ICRUDRepository interface methods, that is used by CRUD controllers. Thus, the DefaultCRUDRepository or classes inheriting from it may be injecetd, as they are, into CRUD controllers without writing any extra code.

Important: All generics in all DefaultCRUDRepository methods work properly with both classes and interfaces.

public class ProductRepository : 
    DefaultCRUDRepository<ApplicationDbContextProduct>
{
    private ApplicationDbContext db;
    public ProductRepository(ApplicationDbContext db)
        : base(db, db.Products){this.db = db; }
    public async Task<IEnumerable<AutoCompleteItem>> GetTypes(string search, int maxpages)
    {
        return (await DefaultCRUDRepository.Create(db, db.ProductTypes)
                .GetPage<AutoCompleteItem>(m => m.Display.StartsWith(search), 
                m => m.OrderBy(n => n.Display), 1, maxpages))
                .Data;
    }
    static ProductRepository()
    {
        DeclareProjection(
                m => m.Maintenance == null ?
                new ProductViewModel{} :
                new ProductMaintenanceViewModel
                {
                    MaintenanceYearlyRate = (decimal)m.Maintenance.YearlyRate
                });
        DeclareQueryProjection(m => new ProductViewModel { }, m => m.Id);
        DeclareProjection(
                m => m.Maintenance == null ?
                new ProductViewModelDetail{}:
                new ProductMaintenanceViewModelDetail{
                    MaintenanceYearlyRate = (decimal)m.Maintenance.YearlyRate
                });
        DefaultCRUDRepository<ApplicationDbContextProductType>
            .DeclareProjection(m => new AutoCompleteItem {
                Display=m.Name,
                Value=m.Id
        });
    }
}
public class CQGridsController : 
    ServerCrudController<ProductViewModelDetailProductViewModelint?>
{
 
    public override string DetailColumnAdjustView { get { return "_DetailRows"; } }
    public IWebQueryProvider queryProvider { getprivate set; }
    public CQGridsController(ProductRepository repository,
        IStringLocalizerFactory factory, IHttpContextAccessor accessor,
        IWebQueryProvider queryProvider)
        : base(factory, accessor)
    {
        Repository = repository;
        this.queryProvider = queryProvider;
    }
        
    [ResponseCache(Duration = 0, NoStore = true)]
    public async Task<IActionResult> CustomServerQuery()
    {
        var query = queryProvider.Parse<ProductViewModel>();
        int pg = (int)query.Page;
        if (pg < 1) pg = 1;
 
        var model = new ProductlistViewModel
        {
            Products = await Repository.GetPage(
            query.GetFilterExpression(),
            query.GetSorting() ??
                (q => q.OrderBy(m => m.Name)),
            pg, 5,
            query.GetGrouping()),
            Query = query
        };
        return View(model);
    }
 
    [HttpGet]
    public async Task<ActionResult> GetTypes(string search)
    {
        var res = search == null || search.Length < 3 ?
            new List<AutoCompleteItem>() :
            await (Repository as ProductRepository).GetTypes(search, 10);
        return Json(res);
    }
}

Fork me on GitHub