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

raysefo

Well-known member
Joined
Feb 22, 2019
Messages
362
Programming Experience
10+
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.FromSqlQueryingEnumerable`1.BuildIndexMap(IReadOnlyList`1 columnNames, DbDataReader dataReader)
at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b_21_0(DbContext , Enumerator enumerator)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.Enumerator.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at OyunPalasGame.Data.GenericGameRepository`1.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, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>gAwaited|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>gAwaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>gAwaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>gAwaited|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:
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 = 1

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

Here 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; }
         }
 
Your error seems to be about EF Core, not about ASP.NET Core Web APi.
 
Moved.
 
The error seems to be pretty self-explanatory. Your select statement needs more columns for EF to figure out how to rebuild the model based on the raw SQL query that you sent.

C#:
The required column 'ReconciliationResponseId' was not present in the results of a 'FromSql' operation.
 
The error seems to be pretty self-explanatory. Your select statement needs more columns for EF to figure out how to rebuild the model based on the raw SQL query that you sent.

C#:
The required column 'ReconciliationResponseId' was not present in the results of a 'FromSql' operation.
I am not querying reconciliation tables at all, check the query please.
 
It's not about touching the reconciliation tables in the db; EF Core would be happy with you if you did this:

C#:
    //Person table has 3 columns; Id, Name, Age. Person entity hs 3 mapped properties
    context.People.FromSql("SELECT 0 as Id, 'John' as Name, 27 as Age").First();

This query touches no table at all, but it does wholly and completely specify the 3 columns in its resultset that the Person entity expects, so EF will materialize it as a Person. You could even have completely nonsensical data materialised as a person:

C#:
    context.People.FromSql("SELECT MAX(NumberOfLegs) as Id, STRING_AGG(CountryOfOrigin, ',') as Name, AVG(Weight) as Age FROM Kittens").First();

We can't exactly see the code that throws the error, but your SQL selects Id and Status columns that are not needed for materializing a list of ReconciliationResponse so it seems slightly wonky for starts

If you want to use a custom sql like this to provide data to become entities that links to others in a graph, you can compose on top of the custom SQL with Include. You should not attempt to retrieve columns for related entities. For example, do not do this:

C#:
record Person(int Id, string Name, int Age, int CompanyId, Company c);
record Company(int Id, string CoName);

  //do not try to download extra data on the hopes EFC will make a related Company entity and wire it up
  context.People.FromSql("SELECT 0 as Id, 'John' as Name, 27 as Age, 1 as CompanyId, 'Acme Software' as CoName").First();

Instead just include on top of your query to the base object dbset:

C#:
  context.People.FromSql("SELECT 0 as Id, 'John' as Name, 27 as Age, 1 as CompanyId")
  .Include(p => p.Company)
  .First();

The raw query will become a sub query and EF will map and join like it always does:

C#:
    SELECT a.*, c.*
    FROM ( SELECT 0 as Id, 'John' as Name, 27 as Age, 1 as CompanyId --the raw query you put
    ) a
    JOIN Company c ON a.CompanyId = c.CompanyId  --because of Include

(The generated sql is more visually complex than that but I've simplified it to get the point across)
 
Last edited:
Back
Top Bottom