Data Services

Overview

The data services layer in the AR Payment Reversal dashboard provides comprehensive data access functionality through Entity Framework, Dapper for performance-critical queries, and custom repository patterns. This document details the DbContext implementation, CRUD operations, transaction handling, complex query patterns, and data caching strategies.

Key Concepts

• Entity Framework DbContext: ORM for database interaction
• Dapper Integration: Micro-ORM for performance-critical queries
• Repository Pattern: Abstraction over data access
• Unit of Work: Transaction management across operations
• Query Optimization: Performance tuning for complex queries
• Data Validation: Service-level data integrity checks

Implementation Details

DbContext Configuration

PluginSysproDataContext Implementation

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/PluginSysproDataContext.cs
namespace MepApps.Dash.Ar.Maint.PaymentReversal.Db
{
    public class PluginSysproDataContext : DbContext
    {
        public PluginSysproDataContext() : base("name=SysproEntities")
        {
            // Disable lazy loading for predictable query behavior
            this.Configuration.LazyLoadingEnabled = false;
            this.Configuration.ProxyCreationEnabled = false;
            
            // Disable automatic detection for performance
            this.Configuration.AutoDetectChangesEnabled = false;
            
            // Set command timeout for long-running queries
            this.Database.CommandTimeout = 60;
        }
        
        // Core SYSPRO Tables
        public DbSet<ArCustomer> ArCustomers { get; set; }
        public DbSet<ArInvoice> ArInvoices { get; set; }
        public DbSet<ArInvoicePay> ArInvoicePays { get; set; }
        public DbSet<ArPayHistory> ArPayHistories { get; set; }
        public DbSet<ArCshJnlCtl> ArCshJnlCtls { get; set; }
        public DbSet<ArCshJnlDet> ArCshJnlDets { get; set; }
        public DbSet<ArCshJnlPay> ArCshJnlPays { get; set; }
        public DbSet<ApBank> ApBanks { get; set; }
        
        // Custom Tables
        public DbSet<CG_ArReversePaymentQueueHeader> C_ArReversePaymentQueueHeader { get; set; }
        public DbSet<CG_ArReversePaymentPostCompletionHistory> C_ArReversePaymentPostCompletionHistory { get; set; }
        
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Configure custom table mappings
            modelBuilder.Entity<CG_ArReversePaymentQueueHeader>()
                .ToTable("CG_ArReversePaymentQueueHeader");
                
            modelBuilder.Entity<CG_ArReversePaymentPostCompletionHistory>()
                .ToTable("CG_ArReversePaymentPostCompletionHistory");
            
            // Configure composite keys
            modelBuilder.Entity<ArInvoicePay>()
                .HasKey(e => new { e.Customer, e.Invoice, e.TrnYear, e.TrnMonth, e.Journal });
            
            base.OnModelCreating(modelBuilder);
        }
    }
}

CRUD Operations Implementation

Create Operations

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/ArReversePaymentService.cs (Lines 421-473)
public async Task AddToPaymentsQueueHeaders(string customer, string checkNumber, 
    decimal checkValue, DateTime paymentDate, string bank, 
    decimal trnYear, decimal trnMonth, decimal journal)
{
    try
    {
        using (PluginSysproDataContext pluginSysproDataContext = 
            MainView.MepPluginServiceProvider.GetService<PluginSysproDataContext>())
        {
            // Check for duplicates before adding
            if (!pluginSysproDataContext.C_ArReversePaymentQueueHeader.Where(x =>
                x.Customer == customer
                && x.CheckNumber == checkNumber
                && x.CheckValue == checkValue
                && x.Bank == bank
                && x.TrnYear == trnYear
                && x.TrnMonth == trnMonth
                && x.Journal == journal).Any())
            {
                var queueHeader = new CG_ArReversePaymentQueueHeader
                {
                    Customer = customer,
                    CheckNumber = checkNumber,
                    CheckValue = checkValue,
                    PaymentDate = paymentDate,
                    Bank = bank,
                    TrnYear = trnYear,
                    TrnMonth = trnMonth,
                    Journal = journal
                };
                
                pluginSysproDataContext.C_ArReversePaymentQueueHeader.Add(queueHeader);
                
                // Enable change detection for this operation
                pluginSysproDataContext.Configuration.AutoDetectChangesEnabled = true;
                await pluginSysproDataContext.SaveChangesAsync().ConfigureAwait(true);
                
                _logger.LogDebug("Added payment to queue. {@QueueAddContext}", 
                    new { customer, checkNumber, checkValue, bank });
            }
        }
    }
    catch (DbEntityValidationException ex)
    {
        var validationErrors = ex.EntityValidationErrors
            .SelectMany(eve => eve.ValidationErrors)
            .Select(ve => $"Property: {ve.PropertyName}, Error: {ve.ErrorMessage}");
            
        _logger.LogError(ex, "Validation error in AddToPaymentsQueueHeaders. Errors: {ValidationErrors}", 
            string.Join("; ", validationErrors));
        throw;
    }
}

Read Operations

Complex query with multiple joins:

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/ArReversePaymentService.cs (Lines 38-73)
public async Task<IEnumerable<ArReversePaymentHeader>> GetQueuedPaymentHeaders()
{
    IEnumerable<ArReversePaymentHeader> headers = null;
    try
    {
        using (PluginSysproDataContext pluginSysproDataContext = 
            MainView.MepPluginServiceProvider.GetService<PluginSysproDataContext>())
        {
            headers = await (from h in pluginSysproDataContext.C_ArReversePaymentQueueHeader
                            join c in pluginSysproDataContext.ArCustomers
                            on h.Customer equals c.Customer
                            
                            join b in pluginSysproDataContext.ApBanks
                            on h.Bank equals b.Bank
                            
                            select new ArReversePaymentHeader
                            {
                                Customer = h.Customer,
                                CustomerName = c.Name,
                                CheckNumber = h.CheckNumber,
                                CheckValue = h.CheckValue,
                                PaymentDate = h.PaymentDate,
                                Bank = h.Bank,
                                BankDescription = h.Bank + " - " + b.Description,
                                TrnYear = h.TrnYear.Value,
                                TrnMonth = h.TrnMonth.Value,
                                Journal = h.Journal.Value
                            }).ToListAsync().ConfigureAwait(true);
                            
            if (_logger.IsEnabled(LogLevel.Trace))
            {
                _logger.LogTrace("Retrieved queued payment headers. {@QueueQueryContext}", 
                    new { count = headers?.Count() ?? 0 });
            }
        }
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error in GetQueuedPaymentHeaders");
        throw;
    }
    return headers;
}

Update Operations

Batch update pattern:

public async Task UpdatePaymentStatuses(IEnumerable<int> paymentIds, string newStatus)
{
    using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
    {
        var payments = await context.C_ArReversePaymentQueueHeader
            .Where(p => paymentIds.Contains(p.Id))
            .ToListAsync();
        
        foreach (var payment in payments)
        {
            payment.Status = newStatus;
            payment.ModifiedDate = DateTime.Now;
        }
        
        context.Configuration.AutoDetectChangesEnabled = true;
        await context.SaveChangesAsync();
        
        _logger.LogInformation("Updated {Count} payment statuses to {Status}", 
            payments.Count, newStatus);
    }
}

Delete Operations

Bulk delete with Dapper:

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/ArReversePaymentService.cs (Lines 103-128)
public async Task DeletePaymentsFromQueue(IEnumerable<ArReversePaymentHeader> paymentHeaders)
{
    string sSql = null;
    try
    {
        sSql = "DELETE FROM CG_ArReversePaymentQueueHeader WHERE \r\n";
        string whereClause = string.Empty;
        
        foreach (var paymentHeader in paymentHeaders)
        {
            if (!string.IsNullOrWhiteSpace(whereClause))
                whereClause += " OR ";
                
            whereClause += $"(Customer='{paymentHeader.Customer}' " +
                          $"AND CheckNumber = '{paymentHeader.CheckNumber}' " +
                          $"AND TrnYear = {paymentHeader.TrnYear} " +
                          $"AND TrnMonth = {paymentHeader.TrnMonth} " +
                          $"AND Journal = {paymentHeader.Journal}) \r\n";
        }
        sSql += whereClause;

        using (PluginSysproDataContext pluginSysproDataContext = 
            MainView.MepPluginServiceProvider.GetService<PluginSysproDataContext>())
        {
            await pluginSysproDataContext.Database.Connection
                .ExecuteAsync(sSql).ConfigureAwait(true);
        }
        
        _logger.LogDebug("Deleted payments from queue. Count: {Count}", paymentHeaders.Count());
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error in DeletePaymentsFromQueue. SQL={SQL}", sSql);
        throw;
    }
}

Transaction Handling Patterns

Unit of Work Implementation

public class UnitOfWork : IUnitOfWork, IDisposable
{
    private readonly PluginSysproDataContext _context;
    private DbContextTransaction _transaction;
    private readonly ILogger<UnitOfWork> _logger;
    
    public UnitOfWork(PluginSysproDataContext context, ILogger<UnitOfWork> logger)
    {
        _context = context;
        _logger = logger;
    }
    
    public async Task BeginTransactionAsync()
    {
        _transaction = _context.Database.BeginTransaction();
        _logger.LogDebug("Transaction started");
    }
    
    public async Task CommitAsync()
    {
        try
        {
            await _context.SaveChangesAsync();
            _transaction?.Commit();
            _logger.LogDebug("Transaction committed");
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error committing transaction");
            await RollbackAsync();
            throw;
        }
    }
    
    public async Task RollbackAsync()
    {
        _transaction?.Rollback();
        _logger.LogDebug("Transaction rolled back");
    }
    
    public void Dispose()
    {
        _transaction?.Dispose();
        _context?.Dispose();
    }
}

Complex Query Patterns

Multi-Level Joins with Grouping

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/ArReversePaymentService.cs (Lines 244-341)
public async Task<IEnumerable<ArReversePaymentHeader>> QueryCustomerPaymentsAsync(string customer)
{
    IEnumerable<ArReversePaymentHeader> payments = null;
    try
    {
        using (PluginSysproDataContext pluginSysproDataContext = 
            MainView.MepPluginServiceProvider.GetService<PluginSysproDataContext>())
        {
            // Complex multi-step query with CTEs
            var ref1 = (from p in pluginSysproDataContext.ArInvoicePays
                       join i in pluginSysproDataContext.ArInvoices
                       on new { p.Customer, p.Invoice } equals new { i.Customer, i.Invoice }
                       
                       join d in pluginSysproDataContext.ArCshJnlDets
                       on new { p.TrnYear, p.TrnMonth, p.Journal, p.Reference } 
                       equals new { d.TrnYear, d.TrnMonth, d.Journal, d.Reference }
                       
                       where i.InvoiceBal1 != i.CurrencyValue && p.TrnValue != 0
                       
                       select new
                       {
                           p.Customer,
                           p.Reference,
                           p.Invoice,
                           p.TrnValue
                       }
                       into temp
                       group temp by new { temp.Customer, temp.Reference, temp.Invoice }
                       into grp
                       where grp.Sum(x => x.TrnValue) != 0
                       select new
                       {
                           grp.Key.Customer,
                           grp.Key.Reference,
                           grp.Key.Invoice,
                           SumTrnValue = grp.Sum(x => x.TrnValue)
                       }).Where(x => x.Customer == customer);

            var ref2 = (from p in pluginSysproDataContext.ArInvoicePays
                       join r in ref1
                       on new { p.Customer, p.Reference, p.Invoice } 
                       equals new { r.Customer, r.Reference, r.Invoice }
                       
                       select new
                       {
                           p.Customer,
                           p.Reference,
                           p.TrnYear,
                           p.TrnMonth,
                           p.Journal,
                           p.TrnValue,
                           p.DiscValue
                       } into x
                       
                       group x by new { x.Customer, x.Reference, x.TrnYear, x.TrnMonth, x.Journal } 
                       into y
                       select new
                       {
                           y.Key.Customer,
                           y.Key.Reference,
                           y.Key.TrnYear,
                           y.Key.TrnMonth,
                           y.Key.Journal,
                           SumTrnValue = y.Sum(z => z.TrnValue),
                           SumDiscValue = y.Sum(z => z.DiscValue)
                       }).Distinct();

            payments = await (from r in ref2
                             join c in pluginSysproDataContext.ArCshJnlCtls
                             on new { r.TrnYear, r.TrnMonth, r.Journal } 
                             equals new { c.TrnYear, c.TrnMonth, c.Journal }
                             
                             join b in pluginSysproDataContext.ApBanks
                             on c.CashAccBank equals b.Bank
                             
                             join h in pluginSysproDataContext.ArPayHistories
                             on new { r.Customer, r.TrnYear, r.TrnMonth, r.Journal } 
                             equals new { h.Customer, TrnYear = h.PaymYear, 
                                         TrnMonth = h.PaymMonth, Journal = h.CashJournal }
                             
                             select new ArReversePaymentHeader
                             {
                                 Customer = r.Customer,
                                 CheckNumber = r.Reference,
                                 CheckValue = (-1 * r.SumTrnValue) - r.SumDiscValue,
                                 PaymentDate = h.PayDate,
                                 Bank = c.CashAccBank,
                                 BankDescription = c.CashAccBank + " - " + b.Description,
                                 TrnYear = r.TrnYear,
                                 TrnMonth = r.TrnMonth,
                                 Journal = r.Journal
                             }).Distinct()
                             .OrderByDescending(x => x.PaymentDate)
                             .ToListAsync()
                             .ConfigureAwait(true);
        }
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error in QueryCustomerPayments");
        throw;
    }
    return payments;
}

Dapper Integration for Performance

Raw SQL Queries with Dapper

// MepApps.Dash.Ar.Maint.PaymentReversal/Services/ArReversePaymentService.cs (Lines 678-762)
public async Task<IEnumerable<SelectionItem>> QueryPostingPeriods()
{
    IEnumerable<SelectionItem> postingPeriods = null;
    string sSql = @"SELECT 
        'C' AS Value,
        'Current (' + CAST(cur.CurrentPeriod as VARCHAR(2)) + '/' + 
        CAST(cur.CurrentYear AS VARCHAR(4)) + ')'	as Description
    FROM ArControl 
    JOIN
    (SELECT 
        CASE
            WHEN [CurYrMthStat13] = 'O' THEN FinYear
            WHEN [CurYrMthStat12] = 'O' THEN FinYear
            -- ... more CASE statements
        end AS CurrentYear,
        CASE
            WHEN [CurYrMthStat13] = 'O' THEN 13
            WHEN [CurYrMthStat12] = 'O' THEN 12
            -- ... more CASE statements
        end AS CurrentPeriod
        FROM ArControl ) AS cur
        ON (1=1)
    UNION
    SELECT 'P' as Value, 'Previous' as Description
    UNION 
    SELECT 'B' as Value, 'Before previous' as Description";
    
    try
    {
        using (PluginSysproDataContext pluginSysproDataContext = 
            MainView.MepPluginServiceProvider.GetService<PluginSysproDataContext>())
        {
            postingPeriods = await pluginSysproDataContext.Database.Connection
                .QueryAsync<SelectionItem>(sSql).ConfigureAwait(true);
        }            
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "Error in QueryPostingPeriods. SQL={SQL}", sSql);
        throw;
    }
    return postingPeriods;
}

Data Caching Strategies

In-Memory Caching

public class CachedDataService
{
    private readonly IMemoryCache _cache;
    private readonly IArReversePaymentService _dataService;
    private readonly ILogger<CachedDataService> _logger;
    
    public CachedDataService(IMemoryCache cache, IArReversePaymentService dataService, 
        ILogger<CachedDataService> logger)
    {
        _cache = cache;
        _dataService = dataService;
        _logger = logger;
    }
    
    public async Task<IEnumerable<ArReversePaymentHeader>> GetQueuedPaymentsAsync()
    {
        const string cacheKey = "queued-payments";
        
        if (_cache.TryGetValue(cacheKey, out IEnumerable<ArReversePaymentHeader> cached))
        {
            _logger.LogDebug("Returning cached queued payments");
            return cached;
        }
        
        var data = await _dataService.GetQueuedPaymentHeaders();
        
        var cacheOptions = new MemoryCacheEntryOptions()
            .SetSlidingExpiration(TimeSpan.FromMinutes(5))
            .SetAbsoluteExpiration(TimeSpan.FromMinutes(30))
            .RegisterPostEvictionCallback((key, value, reason, state) =>
            {
                _logger.LogDebug("Cache entry {Key} evicted due to {Reason}", key, reason);
            });
        
        _cache.Set(cacheKey, data, cacheOptions);
        
        return data;
    }
    
    public void InvalidateCache(string key = null)
    {
        if (string.IsNullOrEmpty(key))
        {
            // Clear all cache
            _cache.Clear();
        }
        else
        {
            _cache.Remove(key);
        }
    }
}

Data Validation at Service Level

public class DataValidationService
{
    private readonly ILogger<DataValidationService> _logger;
    
    public ValidationResult ValidatePaymentHeader(ArReversePaymentHeader header)
    {
        var errors = new List<string>();
        
        if (string.IsNullOrWhiteSpace(header.Customer))
            errors.Add("Customer is required");
            
        if (string.IsNullOrWhiteSpace(header.CheckNumber))
            errors.Add("Check number is required");
            
        if (header.CheckValue <= 0)
            errors.Add("Check value must be greater than zero");
            
        if (header.PaymentDate > DateTime.Now)
            errors.Add("Payment date cannot be in the future");
            
        if (errors.Any())
        {
            _logger.LogWarning("Validation failed for payment header. Errors: {Errors}", 
                string.Join(", ", errors));
                
            return new ValidationResult
            {
                IsValid = false,
                Errors = errors
            };
        }
        
        return new ValidationResult { IsValid = true };
    }
}

Query Optimization Techniques

Projection for Performance

// Only select required fields
var lightweightCustomers = await context.ArCustomers
    .Where(c => c.Active == "Y")
    .Select(c => new 
    {
        c.Customer,
        c.Name,
        c.CreditLimit
    })
    .ToListAsync();

Compiled Queries

private static readonly Func<PluginSysproDataContext, string, Task<CustomerItem>> 
    GetCustomerByIdQuery = EF.CompileAsyncQuery(
        (PluginSysproDataContext context, string customerId) =>
            context.ArCustomers
                .Where(c => c.Customer == customerId)
                .Select(c => new CustomerItem
                {
                    Customer = c.Customer,
                    Name = c.Name
                })
                .FirstOrDefault()
    );

public async Task<CustomerItem> GetCustomerAsync(string customerId)
{
    using (var context = _serviceProvider.GetService<PluginSysproDataContext>())
    {
        return await GetCustomerByIdQuery(context, customerId);
    }
}

Best Practices

1. Always dispose DbContext properly using using statements
2. Use async methods for all database operations
3. Implement retry logic for transient failures
4. Log all data operations with structured logging
5. Validate data before persistence
6. Use transactions for multi-step operations
7. Optimize queries with proper indexing and projections

Common Pitfalls

1. N+1 query problems - Use Include() or explicit loading
2. Large result sets without pagination
3. Missing indexes on frequently queried columns
4. Synchronous database calls blocking UI
5. Not handling concurrency conflicts

Related Documentation

• Service Architecture - Overall service design
• Business Logic Services - Business operations
• Integration Services - External integrations
• Utility Services - Helper services

Summary

The data services layer provides robust, performant, and maintainable data access through a combination of Entity Framework for complex object mapping and Dapper for performance-critical queries. Proper transaction handling, caching strategies, and validation ensure data integrity while maintaining optimal performance.