More

    Using of IRepository, IGuidRepository, ICustomKeyRepository (Chapter 5)

    This Content Is Only For Subscribers

    Please subscribe to unlock this content. Enter your email to get access.
    Your email address is 100% safe from spam!

    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 : baseEntityIRepository<MyTable> _myTableRepository;
    public class MyTable2 : baseGuidEntityIGuidRepository<MyTable2> _myTable2Repository;
    public class MyTable3 : baseCustomKeyEntityICustomKeyRepository<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? idThe Id field lookup value
    Guid guidThe guid field lookup value
    Expression<Func<TEntity, bool>> predicateFor 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);
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / 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> idsA List if Id field lookup value in IList
    IList<Guid> guidA list of guid field lookup value in IList
    Expression<Func<TEntity, bool>> predicateFor 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);
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / 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 = nullUsing a IQueryable expression to query data , see example code
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / 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 = nullUsing a IQueryable expression to query data , see example code
    pageIndexThe page requested, 0 – First Page
    pageSizeNo of records of this page
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / 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 :
    EntityThe entity record
    IList<TEntity>List of entity records
    publishEventtrue / 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 :
    EntityThe entity record
    IList<TEntity>List of entity records
    publishEventtrue / 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 :
    EntityThe entity record
    IList<TEntity>List of entity records
    DeletePermanentlytrue / false – to indicate whether override the softdelete function and delete the record permanently
    publishEventtrue / 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 :
    sqlSQL Query in string
    DataParameter[] parametersparameters 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 = nullUsing a IQueryable expression to query data , see example code
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / 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 = nullUsing a IQueryable expression to query data , see example code
    includeDeletedtrue / false – to indicate whether search include deleted record or not
    (for table interface with ISoftDeletedEntity and IStatusControlEntity)
    activeOnlytrue / 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)

    (c) 2022, BlueSky Information Technology (Int’l) Co. Ltd, All Rights Reserved.

    Recent Articles

    spot_img

    Related Stories

    Stay on op - Ge the daily news in your inbox