I am upgrading my legacy web API to core. It's harder than I expected 
I am getting this error, checked the entities and database as well but honestly, I couldn't find anything that makes sense. Hope you can see the problem.
Here is the RawSQL:
Here is the sample result set:
Here is the code section:
Here are entities:
I am getting this error, checked the entities and database as well but honestly, I couldn't find anything that makes sense. Hope you can see the problem.Here is the RawSQL:
SQL:
SELECT cf.Id, cf.referenceId, cc.confirmcanceldatetime, cf.productcode, cf.unitprice, cf.totalprice, cc.status
FROM [gameconfirmresponses] cf LEFT JOIN (SELECT *, Row_number() OVER(partition BY referenceId ORDER BY confirmcanceldatetime) AS row_num FROM[confirmcancels])
AS cc ON cf.referenceId = cc.referenceId AND cc.row_num = 1 JOIN[GameRequests] AS gr ON gr.referenceId = cf.referenceId
WHERE cf.purchasestatusdate >= '2022-12-21' AND cf.purchasestatusdate < DATEADD(day,1,'2022-12-21') and cc.status = 1Here is the sample result set:
C#:
Id referenceId confirmcanceldatetime productcode unitprice totalprice status
2393 0ACC70BB-98CA-4F93-AA75-823822C58BF4 2022-12-21 10:53:31.247 000000001570 10 10 1
2394 EF4AE861-6045-404F-A966-8C33F5E9B076 2022-12-21 11:47:53.310 018000007167 20 20 1
2395 21EA224D-BACC-461B-B870-1186D81B0C2F 2022-12-21 11:48:59.323 018000007167 20 20 1
2396 ADFC6576-70F2-4554-83AC-4AD841C2935B 2022-12-21 11:49:05.440 018000007167 20 20 1
2397 2A8C8FC4-879C-400B-ACA2-F778D91AC2F7 2022-12-21 11:49:09.333 018000007167 20 20 1Here is the code section:
C#:
public async Task<HttpResponseMessage> GameReconciliation(ReconciliationDto reconciliationDto)
{
using (_unitOfWork)
{
var recon = _mapper.Map<ReconciliationDto, Reconciliation>(reconciliationDto);
var reconDetails = GetReconciliation(recon);
var reconCounts = 0;
if (reconDetails.Count > 0)
{
reconCounts = reconDetails.Count;
}
var reconResponse = new ReconciliationResponse
{
Status = recon.Status,
ReconciliationResponseDateTime = DateTime.Now,
ReconCount = reconCounts
};
//Add reconciliation request into database
_unitOfWork.ReconciliationRepository.Insert(recon);
//Add reconciliation response into database
_unitOfWork.ReconciliationResponseRepository.Insert(reconResponse);
await _unitOfWork.SaveAsync();
//Details added for response
reconResponse.reconciliationDetail = reconDetails;
var resultResponse = JsonConvert.SerializeObject(reconResponse, Formatting.Indented,
new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
});
var response = new HttpResponseMessage
{
StatusCode = System.Net.HttpStatusCode.OK,
Content = new StringContent(resultResponse, System.Text.Encoding.UTF8, "application/json"),
};
return response;
}
}
private List<ReconciliationDetail> GetReconciliation(Reconciliation recon)
{
//CustomerId parameter should be added! (and gr.customerID = 5)
const string query =
"SELECT cf.Id, cf.referenceId, cc.confirmcanceldatetime, cf.productcode, cf.unitprice, cf.totalprice, cc.status FROM [gameconfirmresponses] cf LEFT JOIN(SELECT *, Row_number() OVER(partition BY referenceId ORDER BY confirmcanceldatetime) AS row_num FROM[confirmcancels]) AS cc ON cf.referenceId = cc.referenceId AND cc.row_num = 1 JOIN[GameRequests] AS gr ON gr.referenceId = cf.referenceId WHERE cf.purchasestatusdate >= @p1 AND cf.purchasestatusdate < DATEADD(day,1,@p1) and cc.status = @p0";
var status = recon.Status;
var reconDate = recon.ReconDateTime.ToString("yyyy-MM-dd");
var result = _unitOfWork.ReconciliationDetailRepository.GetWithRawSql(query, status, reconDate).ToList();
return result;
}Here are entities:
C#:
public class Reconciliation
{
public int id { get; set; }
public DateTime ReconDateTime { get; set; }
public DateTime? ReconciliationDateTime { get; set; } = DateTime.Now;
public int Status { get; set; }
}
public class ReconciliationDetail
{
[Key]
public Guid referenceId { get; set; }
public DateTime confirmCancelDateTime { get; set; }
public string productCode { get; set; }
public double unitPrice { get; set; }
public double totalPrice { get; set; }
}
public class ReconciliationResponse
{
public int Id { get; set; }
public int ReconciliationId { get; set; }
public DateTime ReconciliationResponseDateTime { get; set; }
public int ReconCount { get; set; }
public int Status { get; set; }
public virtual Reconciliation reconciliation { get; set; }
public List<ReconciliationDetail> reconciliationDetail { get; set; }
}
