Because I was having this error InnerException = {"Cannot insert explicit value for identity column in table 'Loan' when IDENTITY_INSERT is set to OFF."} So I decided to change it to this stored procedure. Which is inserting the record successfully, after inserting the page will redirect to the index page and call another method to pull the list and display

Dapper stopped pulling joined table after updating the table with a stored procedure

Because I was having this error

InnerException = {“Cannot insert explicit value for identity column in table ‘Loan’ when IDENTITY_INSERT is set to OFF.”}

So I decided to change it to this stored procedure.

this._context.Loan.FromSqlInterpolated($"Exec CreateNewLoan @CurrencyCode = {loan.CurrencyCode}, @OperativeAcctNo = {loan.OperativeAcctNo}, @AmountWrittenOff={loan.AmountWrittenOff}, @OriginalLoanAmount={loan.OriginalLoanAmount}, @LoanTrfDate={loan.LoanTrfDate}, @InterestRate={loan.InterestRate}, @LoanAccountNo={loan.LoanAccountNo}, @DRCR={loan.DRCR}, @CustId={loan.CustId} ");

Which is inserting the record successfully, after inserting the page will redirect to the index page and call another method to pull the list and display with this dapper query

public async Task<IEnumerable<Loan>> GetAll(bool includeDeleted, bool showUnapprovedOnly)
{
    IEnumerable<Loan> loans = null;

    try
    {
        using (var conn = new SqlConnection(connectionstring))
        {
            await conn.OpenAsync();
            //
            string sql = "Select l.*, c.FirstName_CompanyName from dbo.Loan l left join Customer c on  l.CustId=c.CustId";
            if (!includeDeleted)
            {
                sql += " and l.Deleted = 0";
            }
            if (showUnapprovedOnly)
            {
                sql += " and l.Approved = 0";
            }

            loans = await conn.QueryAsync<Loan>(sql);
        }
    }
    catch (Exception)
    {

        throw;
    }
    return loans;
}

There is a relationship between the Loan and customer table. The customer Id(CustId) is also inserted with the stored procedure. I noticed the customer names of the newly inserted records are not being displayed. the previous ones are showing. I don’t know why new records are not pulling the customer table

   public class Loan
    {
        public int LoanId { get; set; }

        [Display(Name ="Customer Name")]
        public long CustId { get; set; }

        [NotMapped]
        [Display(Name ="Customer")]
        public string FirstName_CompanyName { get; set; }

        [Display(Name = "Currency Code")]
        [StringLength(10)]
        [Required]
        public string CurrencyCode { get; set; }

        [Display(Name = "Loan Account No.")]
        [Key]
        public string LoanAccountNo { get; set; }

        [Display(Name = "Branch Code")]
        [StringLength(5)]
        public string BranchCode { get; set; }

        [Display(Name = "Amount Written Off")]
        [Required]
        public double? AmountWrittenOff { get; set; }

        [DataType(DataType.Date)]
        [Display(Name = "Loan Tranfer Date")]
        [Required]
        public DateTime? LoanTrfDate { get; set; }

        [Display(Name = "Interest Rate")]
        [Required]
        public double? InterestRate { get; set; }

        [ForeignKey("CustId")]
        public virtual Customer Customer { get; set; }

    }

Answer

Looks like FirstName_CompanyName wasn’t mapped to any class property. This mapping change should fix the problem.

var sql = "Select l.*, c.CustId, c.FirstName_CompanyName as Name from dbo.Loan l left join Customer c on  l.CustId=c.CustId";
var loans = connection.Query<Loan, Customer, Loan>(
        sql,
        (loan, customer) =>
        {
            loan.CustomerName = customer?.Name;
            return loan;
        },
        splitOn: "CustId")
    .Distinct()
    .ToList();

Advertisement

Answer