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. The "read access" and "modify access"conditions may be adapted to the logged user as follows: a) Inherit your repository class from DefaultCRUDRepository, b) In the dependency injection section of your asp.net core project, specify a factory function to create your repository instances that takes logged user infos from the HttpContext object and pass it to the constructor of your repository class, c) in the constructor of your repository class build the "read access" and "modify access"conditions by using logged user infos and pass them to the base constructor.

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.

Starting form the 2.1.0 version, for a better control on how a ViewModel is projected to the DB model before an update or addition operation, instead of using IUpdateConnections you may declare how to project the whole ViewModel/DTO tree with a call to the static method: DeclareUpdateProjection<K>(Expression<Func<K,T>> proj) static method. once and for all at program start. This may be done, for instance, in the static constructor of your inherited repository. The proj argument is handled like tha analogous argument of DeclareUpdateProjection. Property names are inferred automatically based on a same-name convention that supports both flattening and unflattening. Thus, you need to specify just nested objects, nested collections and property names that do not conform to the same-name convention. Please don't forget to check null objects with Cond expressions since the code is executed in-memory and not used to create DB queries, so an omitted check might result in a "null object" exception. Below an example of projection declaration:

DefaultCRUDRepository<TestContext, Person>
.DeclareUpdateProjection<PersonDTOFlattened>
(
m =>

new Person
{
Spouse = m.SpouseName == null ? null : new Person
{
Children= m.SpouseChildren
.Select(l => new Person
{
Spouse = l.SpouseName == null ? null : new Person { }
}).ToList()
},
Children=  m.Children
.Select(l => new Person { }).ToList()
}
);


Please notice that just properties containing complex objects or collections are specified, since all simple properties are inferred automatically by the same-name convention.
Notice also the null check on SpouseName, and how SpouseName, as all other Spouse properties, is unflattened automatically into Spouse.Name.
ToList() is needed since the DB object (as it is usual with Entity Framework) contains ICollections instead of IEnumerables.

During database updates objects contained in any nested collection are merged with the objects contained in the associated collection in the data model. The merge operation requires a property that works as principal key for the objects contained the nested collection of the data model. This key property may be specified by decorating the data model collection with the CollectionKey attribute as shown in the example below:

public class Item
{
[JsonProperty(PropertyName = "id")]
public string Id { get; set; }

public string Name { get; set; }


public string Description { get; set; }

[CollectionKey("Id"), JsonProperty(PropertyName = "subItems")]
public ICollection<Item> SubItems { get; set; }

[JsonProperty(PropertyName = "assignedTo")]
public Person AssignedTo { get; set; }

[JsonProperty(PropertyName = "isComplete")]
public bool Completed { get; set; }
}

During the merge objects have their properties updated with the property values contained in any object in the associated DTO collection that have their same keys. DTO objects having no associated objects in the data model collection are added as new collection elements, while objects in the data model collection having no associated objects in the DTO collection are removed. The removal operation may be prevented by decorating the DTO collection with [CollectionChange(CollectionChangeMode.Add)].

The code for transferring data from a ViewModel/DTO to the corresponding DB model (and connected DB models) is generated dynamically, and compiled once and for all. 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