Using of IRepository, IGuidRepository, ICustomKeyRepository (Chapter 5)

The Repository Classes, which is developed for the Repository Pattern and Unit of Work in ASP.NET CORE. For more concept on using Repository Pattern Implementation, please see relative Microsoft Documentations or other training web site.

It is recommended to use the Repository classes instead of directly access the DataContext to operate all database operation. It is because we can implement any database operation on different database providers, so that our API do not limit to any single database system. Also, we can implement many automated data handling at the repository layer. In this chapter, we will introduce the functions provided in the Repository Class.

Using of Repository Class

To use repository, first we must declare the data table class with inheritance of base classes:

baseEntity : The table using integer identity field “Id” as primary key.
baseGuidEntity : The table using Guid field “guid” as primary key.
baseCustomKeyEntity : The table using any field(s) as primary key combination.

The definition should like this :

namespace BlueSky.Core.Domain.Common
{
    [Table("CountyDef")]
    public class County : BaseCustomKeyEntity, IActiveFilterEntity, ISoftDeletedEntity ,ILocalizedEntity
    {
        [Column(TypeName = "varchar(2)")]
        public string CountryCode { get; set; }
        [Column(TypeName = "varchar(5)")]
        public string StateProvinceCode { get; set; }
        [Column(TypeName = "varchar(5)")]
        public string CountyCode { get; set; }
        public int? NopStateId { get; set; }
        [Column(TypeName = "varchar(100)")]
        public string CountyName { get; set; }        
        public int? DisplayOrder { get; set; }

        /// <summary> Implement for Localization Service for CustomKeyEntity, to define the CustomKey Value </summary>
        [NotMapped]
        public override string CustomKey { get { return CountryCode + "," + StateProvinceCode + "," + CountyCode; } }

        /// <summary>Implementation of IActiveFilterEntity </summary>
        public bool Active { get; set; }
        /// <summary>Implementation of ISoftDeletedEntity </summary>
        public bool Deleted { get; set; }

        public County()
        {
            Active = true;
            Deleted = false;
        }
    }
}

each base class is required to use different repository class in pair

public class MyTable : baseEntity	IRepository<MyTable> _myTableRepository;
public class MyTable2 : baseGuidEntity	IGuidRepository<MyTable2> _myTable2Repository;
public class MyTable3 : baseCustomKeyEntity	ICustomKeyRepository<MyTable3> _myTable3Repository;

Declaration of Repository Class

When we needed to access the data table, for example in a service, we should declare the repository object like this

namespace CCOMS.Api.Services
{
    public class ClientParcelsService : IClientParcelsService
    {
        #region Properties
        private readonly ApiDataContext _context;
        private readonly IMapper _mapper;
        private readonly AppSettings _appSettings;
        private readonly IGuidRepository<ClientParcel> _clientParcelRepository;
        #endregion

        #region Ctor
        public ClientParcelsService(
            ApiDataContext context,
            IMapper mapper,
            IOptions<AppSettings> appSettings,
            IGuidRepository<ClientParcel> clientParcelRepository)
        {
            _context = context;
            _mapper = mapper;
            _appSettings = appSettings.Value;
            _clientParcelRepository = clientParcelRepository;
        }
        #endregion
        ...............

See this example, we have declare a “_clientParcelRepository” object, then we can easily to retrieve, query, update the database by the repository class.

Functionility of Repository Class

(1) Get a record by ID, Guid or Expression

Task<TEntity> GetByIdAsync(int? id, bool includeDeleted = true, bool activeOnly = false);           // Provided by IRepository

Task<TEntity> GetByIdAsync(Guid guid, bool includeDeleted = true, bool activeOnly = false);         // Provided by IGuidRepository

Task<IList<TEntity>> GetByExpressionSingleOrDefaultAsync(Expression<Func<TEntity, bool>> predicate, bool includeDeleted = true, bool activeOnly = false);       // Provided by ICustomKeyRepository

Input Parameters :
int? id	The Id field lookup value
Guid guid	The guid field lookup value
Expression<Func<TEntity, bool>> predicate	For custom key table, we are not sure that what is the key field name and how many key fields there. Therefore, we needed to use Linq expression to search the record e.g _clientParcelRepository.GetByExpressionSingleOrDefaultAsync(p => p.ParcelCode == myParcelCode);
includeDeleted	true / false – to indicate whether search include deleted record or not (for table interface with ISoftDeletedEntity and IStatusControlEntity)
activeOnly	true / false – to indicate whether search include active Only or all records (for table interface with IActiveFilterEntity and IStatusControlEntity)

private async Task<Client> GetClientAsync(Guid ClientGuid)
{
    var client = await _clientRepository.GetByIdAsync(ClientGuid, includeDelete: false, activeOnly: true);
    if (client == null)
        throw new AppException(_context.errorList.Response(ErrorCode.RecordNotFound, _detail: "Client Guid : " + ClientGuid.ToString() + " not found." ).ToString());
        return client;
}

(2) Get list of records by IDs, Guids or Expression

Task<IList<TEntity>> GetByIdsAsync(IList<int> ids, bool includeDeleted = true, bool activeOnly = false);   // Provided by IRepository

Task<IList<TEntity>> GetByIdsAsync(IList<Guid> guids, bool includeDeleted = true, bool activeOnly = false);    // Provided by IGuidRepository

Task<IList<TEntity>> GetByExpressionAsync(Expression<Func<TEntity, bool>> predicate, bool includeDeleted = true, bool activeOnly = false);   // Provided by ICustomKeyRepository

Input Parameters :
IList<int> ids	A List if Id field lookup value in IList
IList<Guid> guid	A list of guid field lookup value in IList
Expression<Func<TEntity, bool>> predicate	For custom key table, we are not sure that what is the key field name and how many key fields there. Therefore, we needed to use Linq expression to search the record e.g _clientParcelRepository.GetByExpressionAsync(p => p.ParcelCode == myParcelCode);
includeDeleted	true / false – to indicate whether search include deleted record or not (for table interface with ISoftDeletedEntity and IStatusControlEntity)
activeOnly	true / false – to indicate whether search include active Only or all records (for table interface with IActiveFilterEntity and IStatusControlEntity)

(3) Get All Records (Search Records)

Task<IList<TEntity>> GetAllAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null, bool includeDeleted = true, bool activeOnly = false);  // Provided by IRepository

Task<IList<TEntity>> GetAllAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null, bool includeDeleted = true, bool activeOnly = false);  // Provided by IGuidRepository

Task<IList<TEntity>> GetAllAsync(Func<IQueryable<TEntity>, Task<IQueryable<TEntity>>> func = null, bool includeDeleted = true, bool activeOnly = false)  // Provided by ICustomKeyRopository

Input Parameters :
Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null	Using a IQueryable expression to query data , see example code
includeDeleted	true / false – to indicate whether search include deleted record or not (for table interface with ISoftDeletedEntity and IStatusControlEntity)
activeOnly	true / false – to indicate whether search include active Only or all records (for table interface with IActiveFilterEntity and IStatusControlEntity)

This features are usually used for data searching. In this example, we first to declare and search model (normally named as xxxxSearchCommand) in the model folder.

    public partial record ClientSearchCommand : BasePageableModel
    {
        /// <summary>客戶 Guid, 如網上注冊，客戶 Guid 同跟用戶編號一樣</summary>
        public Guid? EntityGuid { get; set; }
        /// <summary>上級客戶編號</summary>
        public Guid? MasterClientGuid { get; set; }
        /// <summary>業務員編號或協銷聯盟編號</summary>
        public string SalesCode { get; set; }
        /// <summary>客戶分類, 網上注冊用戶為 N</summary>
        public string ClientTypeCode { get; set; }
        /// <summary>客戶名稱, 如公司客戶則公司名稱</summary>
        public string ClientName { get; set; }
        /// <summary>國家 (地址)</summary>
        public string CountryCode { get; set; }
        /// <summary>州/省 (地址)</summary>
        public string StateProvinceCode { get; set; }
        /// <summary>市/縣</summary>
        public string CountyCode { get; set; }
        /// <summary>城/鎮</summary>
        public string City { get; set; }
        /// <summary>地址</summary>
        public string Address { get; set; }
        /// <summary>電郵地址</summary>
        public string Email { get; set; }
        /// <summary>手提電話</summary>
        public string Phone { get; set; }
        /// <summary>狀況 0-新客戶， 1-活動， -1-暫停，-2-已刪除</summary>
        public short? Status { get; set; }
        /// <summary>Show Active Only</summary>
        public bool? ActiveOnly { get; set; }
        /// <summary>Include Deleted Records</summary>
        public bool? IncludeDeleted { get; set; }
        /// <summary>
        /// Order By :      0 - CreateTimeUtc
        ///                 1 - Client Code
        ///                 2 - Client Name
        /// </summary>
        public int? OrderBy { get; set; }
    }

Then we implement the code in ClientService to search record by input parameters

var clients = await _clientRepository.GetAllPagedAsync(query =>
{
     if (command.EntityGuid != Guid.Empty && command.EntityGuid != null)
         query = query.Where(g => g.EntityGuid == command.EntityGuid);
     if (command.MasterClientGuid != Guid.Empty && command.EntityGuid != null)
         query = query.Where(g => g.MasterClientGuid == command.MasterClientGuid);  
     if (String.IsNullOrEmpty(command.SalesCode))
         query = query.Where(s => s.SalesCode == command.SalesCode);
     if (string.IsNullOrEmpty(command.ClientTypeCode))
         query = query.Where(t => t.ClientTypeCode == command.ClientTypeCode);
     if (string.IsNullOrEmpty(command.ClientName))
         query = query.Where(c => c.ClientName.Contains(command.ClientName));
     if (string.IsNullOrEmpty(command.CountryCode))
         query = query.Where(c => c.CountryCode == command.CountryCode);
     if (string.IsNullOrEmpty(command.StateProvinceCode))
         query = query.Where(s => s.StateProvinceCode == command.StateProvinceCode);
     if (string.IsNullOrEmpty(command.CountyCode))
         query = query.Where(c => c.CountyCode == command.CountyCode);
     if (string.IsNullOrEmpty(command.City))
         query = query.Where(c => c.City.Contains(command.City));
     if (string.IsNullOrEmpty(command.Address))
         query = query.Where(a => a.Add1.Contains(command.Address) || a.Add2.Contains(command.Address));
     if (string.IsNullOrEmpty(command.Email))
         query = query.Where(e => e.Email.Contains(command.Email));
     if (string.IsNullOrEmpty(command.Phone))
         query = query.Where(p => p.MobilePhone.Contains(command.Phone) || p.Tel1.Contains(command.Phone)
                    || p.Tel2.Contains(command.Phone) || p.Fax1.Contains(command.Phone) || p.Fax2.Contains(command.Phone));
     if (command.Status.HasValue)
         query = query.Where(s => s.Status == command.Status);
     switch (command.OrderBy)
     {
         case 1:
             query = query.OrderBy(o => o.ClientCode);
             break;
         case 2:
             query = query.OrderBy(o => o.ClientName);
             break;
         default:                    
             query = query.OrderBy(o => o.CreateTimeUtc);
             break;
     }

     return query;
}, activeOnly: (bool)command.ActiveOnly, includeDeleted: (bool)command.IncludeDeleted );

return clients;

(4) Get All Records with Paging (Search Records)

Task<IPagedList<TEntity>> GetAllPagedAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null,
 int pageIndex = 0, int pageSize = int.MaxValue, bool getOnlyTotalCount = false, bool includeDeleted = true, bool activeOnly = false);  // Provided by IRepository

Task<IPagedList<TEntity>> GetAllPagedAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null, int pageIndex = 0, int pageSize = int.MaxValue, bool getOnlyTotalCount = false, bool includeDeleted = true, bool activeOnly = false);  // Provided by IGuidRepository

Task<IPagedList<TEntity>> GetAllPagedAsync(Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null, int pageIndex = 0, int pageSize = int.MaxValue, bool getOnlyTotalCount = false, bool includeDeleted = true, bool activeOnly = false);  // Provided by ICustomKeyRepository

Input Parameters :
Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null	Using a IQueryable expression to query data , see example code
pageIndex	The page requested, 0 – First Page
pageSize	No of records of this page
includeDeleted	true / false – to indicate whether search include deleted record or not (for table interface with ISoftDeletedEntity and IStatusControlEntity)
activeOnly	true / false – to indicate whether search include active Only or all records (for table interface with IActiveFilterEntity and IStatusControlEntity)

var clients = await _clientRepository.GetAllPagedAsync(query =>
{
     if (command.EntityGuid != Guid.Empty && command.EntityGuid != null)
         query = query.Where(g => g.EntityGuid == command.EntityGuid);
     if (command.MasterClientGuid != Guid.Empty && command.EntityGuid != null)
         query = query.Where(g => g.MasterClientGuid == command.MasterClientGuid);  
     if (String.IsNullOrEmpty(command.SalesCode))
         query = query.Where(s => s.SalesCode == command.SalesCode);
     if (string.IsNullOrEmpty(command.ClientTypeCode))
         query = query.Where(t => t.ClientTypeCode == command.ClientTypeCode);
     if (string.IsNullOrEmpty(command.ClientName))
         query = query.Where(c => c.ClientName.Contains(command.ClientName));
     if (string.IsNullOrEmpty(command.CountryCode))
         query = query.Where(c => c.CountryCode == command.CountryCode);
     if (string.IsNullOrEmpty(command.StateProvinceCode))
         query = query.Where(s => s.StateProvinceCode == command.StateProvinceCode);
     if (string.IsNullOrEmpty(command.CountyCode))
         query = query.Where(c => c.CountyCode == command.CountyCode);
     if (string.IsNullOrEmpty(command.City))
         query = query.Where(c => c.City.Contains(command.City));
     if (string.IsNullOrEmpty(command.Address))
         query = query.Where(a => a.Add1.Contains(command.Address) || a.Add2.Contains(command.Address));
     if (string.IsNullOrEmpty(command.Email))
         query = query.Where(e => e.Email.Contains(command.Email));
     if (string.IsNullOrEmpty(command.Phone))
         query = query.Where(p => p.MobilePhone.Contains(command.Phone) || p.Tel1.Contains(command.Phone)
                    || p.Tel2.Contains(command.Phone) || p.Fax1.Contains(command.Phone) || p.Fax2.Contains(command.Phone));
     if (command.Status.HasValue)
         query = query.Where(s => s.Status == command.Status);
     switch (command.OrderBy)
     {
         case 1:
             query = query.OrderBy(o => o.ClientCode);
             break;
         case 2:
             query = query.OrderBy(o => o.ClientName);
             break;
         default:                    
             query = query.OrderBy(o => o.CreateTimeUtc);
             break;
     }

     return query;
}, pageIndex: command.PageIndex, pageSize: command.PageSize, activeOnly: (bool)command.ActiveOnly, includeDeleted: (bool)command.IncludeDeleted );

return clients;

(5) Insert A Record / Insert A List of Records

Task InsertAsync(TEntity entity, bool publishEvent = true);
Task InsertAsync(IList<TEntity> entities, bool publishEvent = true);

Input Parameters :
Entity	The entity record
IList<TEntity>	List of entity records
publishEvent	true / false – to indicate whether publish a event for the system, default = true

private async Task ClientInsertAsync(Client client)
{
    try
    {
        await _clientRepository.InsertAsync(client);
    } catch(Exception E)
    {
        throw new AppException(_context.errorList.Response(ErrorCode.DataInsertError, _detail: E.Message).ToString());
    }
}

(6) Upload A Record / Upload A List of Records

Task UpdateAsync(TEntity entity, bool publishEvent = true);
Task UpdateAsync(IList<TEntity> entities, bool publishEvent = true);

Input Parameters :
Entity	The entity record
IList<TEntity>	List of entity records
publishEvent	true / false – to indicate whether publish a event for the system, default = true

private async Task ClientUpdateAsync(Client client)
{
    try
    {
        await _clientRepository.UpdateAsync(client);
    }catch( Exception E)
    {
        throw new AppException(_context.errorList.Response(ErrorCode.DataUpdateError, _detail: E.Message).ToString());
    }
}

(7) Delete/UnDelete A Record / Delete A List of Records

Task DeleteAsync(TEntity entity, bool publishEvent = true, bool DeletePermanently = false);
Task DeleteAsync(IList<TEntity> entities, bool publishEvent = true, bool DeletePermanently = false);
Task UnDeleteAsync(TEntity entity, bool publishEvent = true);

Input Parameters :
Entity	The entity record
IList<TEntity>	List of entity records
DeletePermanently	true / false – to indicate whether override the softdelete function and delete the record permanently
publishEvent	true / false – to indicate whether publish a event for the system, default = true

private async Task ClientDeleteAsync(Client client, bool HardDelete)
{
    try
    {
        await _clientRepository.DeleteAsync(client, DeletePermanently: HardDelete);
    } catch (Exception E)
    {
        throw new AppException(_context.errorList.Response(ErrorCode.DataDeleteError, _detail: E.Message).ToString());
    }
}

(8) Directly get records of data by SQL statement

Task<IList<TEntity>> EntityFromSqlAsync(string sql, params DataParameter[] parameters);

Input Parameters :
sql	SQL Query in string
DataParameter[] parameters	parameters list of the query

IList<Client> clients = _clientRepository.EntityFromSqlAsync("select * from ClientMaster where ClientName like '%TEST%');

Note : Be sure that the query return the record fields int the TEntity format, or otherwise, exception may occurs.

(9) Check Expression records exist or not

Task<bool> AnyAsync(Expression<Func<TEntity, bool>> predicate, bool includeDeleted = false, bool activeOnly = true);

Input Parameters :
Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null	Using a IQueryable expression to query data , see example code
includeDeleted	true / false – to indicate whether search include deleted record or not (for table interface with ISoftDeletedEntity and IStatusControlEntity)
activeOnly	true / false – to indicate whether search include active Only or all records (for table interface with IActiveFilterEntity and IStatusControlEntity)

var user = 
if (await _userRepository.AnyAsync(u => u.UserGuid == Guid.Empty))
   throw new AppException(_context.errorList.Response(ErrorCode.RecordNotFound, _validationErrors: "User Guid : " + UserGuid.ToString() ).ToString());

(10) Get Single Record by Expression

Task<TEntity> SingleOrDefaultAsync(Expression<Func<TEntity, bool>> predicate, bool includeDeleted = false, bool activeOnly = true);

Input Parameters :
Func<IQueryable<TEntity>, IQueryable<TEntity>> func = null	Using a IQueryable expression to query data , see example code
includeDeleted	true / false – to indicate whether search include deleted record or not (for table interface with ISoftDeletedEntity and IStatusControlEntity)
activeOnly	true / false – to indicate whether search include active Only or all records (for table interface with IActiveFilterEntity and IStatusControlEntity)

var user = await _userRepository.SingleOrDefaultAsync(u => u.UserGuid == UserGuid);
if (user == null)
      throw new AppException(_context.errorList.Response(ErrorCode.RecordNotFound, _validationErrors: "User Guid : " + UserGuid.ToString() ).ToString());

We will add more repository features in future for rapid development.

Overview : Developer Guide for BlueSky .NETCORE API Framework

Previous : Updating DataContent, Creation of Initial Data and Generate Database Migration Script (Ch. 4)

Next : Creating and Registering of Service Classes (Ch.6)