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 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:

        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>
        m =>
        new Person
            Spouse = m.SpouseName ==
                null ? null : new Person
                Children= m.SpouseChildren.Select(l =>
                    new Person {Spouse = l.SpouseName ==
                        null ? null : new Person { }  })
            Children=  m.Children.Select(l =>
                new Person { })

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 Person
    public int Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public bool Male { get; set; }
    public virtual ICollection<Person> Children { get; set; }
    public virtual Person Parent { get; set; }
    public virtual Person Spouse { get; set; }
    public virtual Person SpouseOf { 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>(
            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>
            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.

Repository events

Developers may specify custom code to be executed immediately before each entity is created or added by overriding the empty virtual DefaultCRUDRepository methods below:

public virtual async Task BeforeUpdate(
    T entity, bool full, object dto)

public virtual async Task BeforeAdd(
    T entity, bool full, object dto)


Where entity is the database object created from the DTO passed to the Add/Update repository methods, full is the same full parameter passed to the Add/Update repository methods, and dto is the DTO passed to the Add/Update repository methods. The above methods are triggered also when updates and creations come from an UpdateList method call.

The BeforeAdd method is very usefull when Add is called with full set to false. In fact a false full value declares that DTO doesn't contain all values needed to define a database entity and, in this case, the BeforeAdd method may be used to retrive adequate default values for the unspecified properties from the database.

public class ProductRepository : 
    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))
    static ProductRepository()
                m => m.Maintenance == null ?
                new ProductViewModel{} :
                new ProductMaintenanceViewModel
                    MaintenanceYearlyRate = (decimal)m.Maintenance.YearlyRate
        DeclareQueryProjection(m => new ProductViewModel { }, m => m.Id);
                m => m.Maintenance == null ?
                new ProductViewModelDetail{}:
                new ProductMaintenanceViewModelDetail{
                    MaintenanceYearlyRate = (decimal)m.Maintenance.YearlyRate
            .DeclareProjection(m => new AutoCompleteItem {
public class CQGridsController : 
    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.GetSorting() ??
                (q => q.OrderBy(m => m.Name)),
            pg, 5,
            Query = query
        return View(model);
    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