在使用存储过程更新表后，Dapper停止拉入联接的表

Question

因为出现此错误

InnerException＝｛quot；当identity_insert设置为OFF时，无法为表"Loan"中的标识列插入显式值。｝

所以我决定将其更改为这个存储过程。

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} ");

成功插入记录的，插入页面后将重定向到索引页面，并调用另一个方法来拉取列表并显示此适配器查询

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;
}

Loan和customer表之间存在关系。客户Id(CustId(也与存储过程一起插入。我注意到新插入的记录的客户名称没有显示。前面的那些正在显示。我不知道为什么新记录没有进入客户表\

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 1

看起来FirstName_CompanyName没有映射到任何类属性。此映射更改应该可以解决问题。

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();