Asp.Net Core Web Api - System.InvalidOperationException: The required column 'ReconciliationResponseId' was not present in the results of a 'FromSql' operation

Question

Hello,

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.

System.InvalidOperationException: The required column 'ReconciliationResponseId' was not present in the results of a 'FromSql' operation.
at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable1.BuildIndexMap(IReadOnlyList1 columnNames, DbDataReader dataReader)
at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable1.Enumerator.InitializeReader(Enumerator enumerator) at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteTState,TResult
at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at OyunPalasGame.Data.GenericGameRepository1.GetWithRawSql(String query, Object[] parameters) in C:\Users\197199\Desktop\Development\Backup\OyunPalasGame\OyunPalasGame.Data\GenericGameRepository.cs:line 70 at OyunPalasGame.Services.OyunPalasServices.GetReconciliation(Reconciliation recon) in C:\Users\197199\Desktop\Development\Backup\OyunPalasGame\OyunPalasGame.Services\OyunPalasServices.cs:line 725 at OyunPalasGame.Services.OyunPalasServices.GameReconciliation(ReconciliationDto reconciliationDto) in C:\Users\197199\Desktop\Development\Backup\OyunPalasGame\OyunPalasGame.Services\OyunPalasServices.cs:line 675 at OyunPalasAPI.Controllers.OyunPalasController.GameReconciliation(ReconciliationDto reconciliationDto) in C:\Users\197199\Desktop\Development\Backup\OyunPalasGame\OyunPalasGameAPI\Controllers\OyunPalasGameController.cs:line 93 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

Here is the RawSQL:

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 = 1  

Here is the sample result set:

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	1  

Here is the code section:

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:

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

Answer 1

I added [NotMapped] and it worked.

[NotMapped]  
public List<ReconciliationDetail> reconciliationDetail { get; set; }