Dapper.Net - QueryMultiple uso con procedimiento almacenado que devuelve resultados múltiples

.net-core c# dapper sql-server

Pregunta

Soy nuevo en Dapper.Net y estaba intentando escribir mi propio micro ORM pero, después de una revisión, noté que Dapper podía cumplir mis requisitos.

He estado muy cerca de hacerlo bien, pero aún no estaba allí. Creo que mi implementación actual no es tan rápida como Dapper nativo. Por favor ayuda y gracias!

POCOS

public class CustomerCollections
{
    public decimal CifNo { get; set; }
    public string StatusCode { get; set; }
    public decimal TotalPastDue { get; set; }
    public int PaymentsReturned { get; set; }
    public int DaysPastDue { get; set; }
    public List<CollectionPayment> CollectionPayments { get; set; }

}

public class CollectionPayment
{
    public decimal CifNo { get; set; }
    public decimal AcctRefNo { get; set; }
    public decimal PaymentReferenceNumber { get; set; }
    public decimal PaymentAmount { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime NsfDate { get; set; }
    public CollectionPaymentDetail PaymentDetail { get; set; }
}

public class CollectionPaymentDetail
{
    public decimal PaymentReferenceNumber { get; set; }
    public string PaymentMethodType { get; set; }
    public decimal PaymentAmount { get; set; }
    public decimal InterestAmount { get; set; }
    public decimal PrincipalAmount { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime NsfDate { get; set; }
    public string ReturnCode { get; set; }
    public string AgentUser { get; set; }

}

Aquí está mi procedimiento almacenado

ALTER PROCEDURE ULPS.sp_GetCollections
(
    @cifno INT
)
AS
BEGIN

SET NOCOUNT ON;

--Past Due Header Info
SELECT l.cifno AS Cifno
    --, l.acctrefno
    , c.status_code AS StatusCode
    , CASE 
        WHEN LOWER(c.status_code) = 'past due' THEN SUM(l.total_past_due_balance)
        ELSE 0
      END
      AS TotalPastDue
     , SUM(p.num_payments_returned) AS PaymentsReturned
     , CAST(SUM(d.days_past_due) AS INT) AS DaysPastDue
FROM dbo.loanacct l
OUTER APPLY (
    SELECT h.acctrefno, COUNT(tc.transaction_description) AS num_payments_returned  FROM dbo.loanacct_trans_history h
    INNER JOIN dbo.loan_transaction_codes tc
    ON tc.transaction_code = h.transaction_code AND LOWER(tc.transaction_description) = 'nsf fee'
    GROUP BY h.acctrefno
) AS p
LEFT JOIN dbo.loanacct_statuses s
    ON s.acctrefno = l.acctrefno
INNER JOIN dbo.loan_status_codes c
    ON c.status_code_no = s.status_code_no
OUTER APPLY (
    SELECT cifno, acctrefno, days_past_due, open_date FROM dbo.loanacct
) AS d
WHERE p.acctrefno = l.acctrefno
    AND (LOWER(c.status_code) = 'write-off' OR LOWER(c.status_code) = 'past due')
    AND l.status_code_no = 0 --ACTIVE ONLY
    AND l.cifno = @cifno
    AND d.acctrefno = l.acctrefno
    AND d.cifno = @cifno
GROUP BY l.cifno
    , c.status_code

--Past Due Loan List
CREATE TABLE #PastDueLoanList (
    Cifno NUMERIC(10,0)
    , AcctRefNo NUMERIC(10,0)
    , PaymentReferenceNumber NUMERIC(9,0)
    , PaymentAmount NUMERIC(12,2)
    , DueDate DATETIME
    , NsfDate DATETIME
) 
INSERT INTO #PastDueLoanList
SELECT l.cifno AS Cifno 
    , h.acctrefno AS AcctRefNo
    , h.payment_reference_no AS PaymentReferenceNumber
    , SUM(h.payment_amount) AS PaymentAmount
    , h.date_due AS DueDate
    , h.nsf_date AS NsfDate
FROM dbo.loanacct l
INNER JOIN dbo.loanacct_payment_history h
    ON h.acctrefno = l.acctrefno and h.nsf_flag = 1 and h.payment_number <> 0
WHERE l.cifno = @cifno
GROUP BY l.cifno
    , h.acctrefno
    , h.payment_reference_no
    , h.transaction_reference_no
    , h.payment_number
    , h.date_due
    , h.nsf_date 

SELECT * FROM #PastDueLoanList

--Past Due Payment Details
SELECT h.acctrefno AS AcctRefNo
    , h.payment_reference_no AS PaymentReferenceNumber
    , m.payment_method_code AS PaymentMethodType
    , SUM(CASE WHEN h.transaction_code = 0 THEN payment_amount ELSE 0 END) AS PaymentAmount
    , SUM(CASE WHEN h.transaction_code = 206 THEN payment_amount ELSE 0 END) AS InterestAmount
    , SUM(CASE WHEN h.transaction_code = 204 THEN payment_amount ELSE 0 END) AS PrincipalAmount
    , MAX(h.date_due) AS DueDate
    , MAX(h.nsf_date) AS NsfDate
    , 'n/a' AS ReturnCode
    , 'unknown, user' AS AgentUser
FROM dbo.loanacct l (NOLOCK)
INNER JOIN #PastDueLoanList tmp
    ON tmp.AcctRefNo = l.acctrefno
INNER JOIN dbo.loanacct_payment_history h (NOLOCK)
    ON h.acctrefno = l.acctrefno and h.nsf_flag = 1
INNER JOIN dbo.loan_payment_method m (NOLOCK)
    ON m.payment_method_no = h.payment_method_no
GROUP BY h.acctrefno
    , h.payment_reference_no
    , m.payment_method_code
END
GO

Esto funciona, pero no el camino Dapper.

public async Task<CustomerCollections> GetCustomerCollections(decimal cifno)
{
    var collections = new CustomerCollections();
    using (var multi = await DbContext.NativeContext().QueryMultipleAsync("ULPS.sp_GetCollections", new { cifno }, commandType: CommandType.StoredProcedure))
    {
        collections = await multi.ReadFirstOrDefaultAsync<CustomerCollections>();
        collections.CollectionPayments = multi.Read<CollectionPayment>().ToList();
        var details = multi.Read<CollectionPaymentDetail>().ToList();
        collections.CollectionPayments.ForEach(c =>
        {
            details.ForEach(d =>
            {
                if (c.PaymentReferenceNumber == d.PaymentReferenceNumber)
                    c.PaymentDetail = d;
            });
        });
    }
    return collections;
}

Respuesta popular

Devolvería el conjunto de resultados como JSON (si está en una versión de SQL Server que lo admita, como SQL Server 2016 o superior o Azure SQL) y luego utilice la función "Gestión personalizada de tipos" para deserializar JSON en su objeto complejo:

https://medium.com/dapper-net/custom-type-handling-4b447b97c620



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué