Using Visual Studio 2022 Net6 to build a view only app of an existing database. I used Oracle Entity Framework to scaffold the model and context. Mostly it's fine, but I have come across this error that I cannot pin down. When I call the API to get my order objects, I get the following error:
InvalidCastException: Specified cast is not valid.
Oracle.ManagedDataAccess.Client.OracleDataReader.GetByte(int i)
lambda_method12(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.MoveNextAsync()
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)
OracleWCON_V2.Server.Controllers.OrdersController.Get() in OrdersController.cs
+
return await _context.Orders.Where(o => o.SoDeliveryDate.Date >= DateTime.Now.Date).ToListAsync();
lambda_method5(Closure , object )
Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
System.Threading.Tasks.ValueTask<TResult>.get_Result()
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask<IActionResult> actionResultValueTask)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Please not that this has nothing to do with the date comparison in the controller, that is just there to restrict the number of records returned. I get the same result if I query for just one record (that fits the bill for whatever column is failing). Even knowing the data from a failing column, I have been unable to get to the bottom of the issue.
Here is the Model object:
using OracleWCON_V2.RODataAccess.Models;
using System;
using System.Collections.Generic;
#nullable disable
namespace OracleWCON_V2.RODataAccess.Context
{
public partial class Order
{
public decimal SoIaInvoiceAccount { get; set; }
public decimal OrderId { get; set; }
public decimal? SoSioOrderGroup { get; set; }
public string SoTown { get; set; }
public string SoAddress1 { get; set; }
public string SoAddress2 { get; set; }
public string SoAddress3 { get; set; }
public string SoCustOrderNo { get; set; }
public decimal? SoCustQuoteNo { get; set; }
public DateTime SoDeliveryDate { get; set; }
public string SoEoShortName { get; set; }
public string SoFocAuthorisation { get; set; }
public decimal? SoLatNumber { get; set; }
public decimal? SoMileageAdj { get; set; }
public decimal? SoOrigInvoiceAccount { get; set; }
public decimal? SoOurOrderNo { get; set; }
public string? SoPrintBreak { get; set; }
public string? SoPurchRefNo { get; set; }
public decimal? SoQuoNumber { get; set; }
public string SoSplInfo { get; set; }
public string SoToBeLinked { get; set; }
public decimal? SoPlSupplierAccNo { get; set; }
public string SoType { get; set; }
public string SoTrainDetails { get; set; }
public string SoQpName { get; set; }
public int? SoRcNumber { get; set; }
public DateTime? SoPurchInvDate { get; set; }
public int? SoFyPurchInvNo { get; set; }
public string SoSalesRecharge { get; set; }
public DateTime? SoWhenPlaced { get; set; }
public int? SoEmpTakenBy { get; set; }
public string SoPlacedBy { get; set; }
public DateTime? SoWhenLastCommitted { get; set; }
public int? SoLastCommittedBy { get; set; }
public byte? SoReadbackSeconds { get; set; }
public string SoTbc { get; set; }
public string SoWhyTbc { get; set; }
public DateTime? SoWhenLinked { get; set; }
public int? SoEmpLinkedBy { get; set; }
public string SoAddress4 { get; set; }
public string SoMultiDrop { get; set; }
public string SoExworks { get; set; }
public string SoWaitingForCash { get; set; }
public string SoAwaitingCreditApproval { get; set; }
public int? SoIoReference { get; set; }
public string SoOrderOffice { get; set; }
public string SoSitePhone { get; set; }
public string SoCancelled { get; set; }
public int? SoSoBasedOn { get; set; }
public string SoTorrExwTown { get; set; }
public string SoTorrExwDestination { get; set; }
public string SoOutOfHours { get; set; }
public decimal? SoTotalDayworkHours { get; set; }
public string SoJobToFinish { get; set; }
public string SoIncomingJourney { get; set; }
public string SoCanTakeToday { get; set; }
public byte? SoRecycleSource { get; set; }
public string SoLccApplies { get; set; }
public DateTime? SoWhenLastChecked { get; set; }
public int? SoLastCheckedBy { get; set; }
public string SoOwContract { get; set; }
public string SoApproved { get; set; }
public DateTime? SoDateApproved { get; set; }
public string SoReceived { get; set; }
public DateTime? SoDateReceived { get; set; }
public string SoQuery { get; set; }
public byte? SoApprovedBy { get; set; }
public string SoSiteContact { get; set; }
public string SoPlacedByPhone { get; set; }
public string SoOrdNotificationCt1 { get; set; }
public string SoOrdNotificationCt2 { get; set; }
public string SoDespNotificationCt1 { get; set; }
public string SoDespNotificationCt2 { get; set; }
public decimal? SoCt1CtId { get; set; }
public decimal? SoCt2CtId { get; set; }
public string SoPlacedByPhone2 { get; set; }
public string SoSitePhone2 { get; set; }
public string SoPlanned { get; set; }
public string SoPostcode1 { get; set; }
public string SoPostcode2 { get; set; }
public decimal? SoSiteLat { get; set; }
public decimal? SoSiteLong { get; set; }
public string SoTmsTntLink { get; set; }
public string SoCancelledContact { get; set; }
public decimal? SoCt3CtId { get; set; }
public string SoDelInstructions { get; set; }
public string SoCashCustAcc { get; set; }
public decimal? SoMovedIntOrderRef { get; set; }
public decimal? SoMovedOurRef { get; set; }
public DateTime? SoMovedTo { get; set; }
}
}
And here is the controller:
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using OracleWCON_V2.RODataAccess.Context;
using OracleWCON_V2.RODataAccess.Models;
namespace OracleWCON_V2.Server.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class OrdersController : ControllerBase
{
private readonly OrderContext _context;
public OrdersController(OrderContext context)
{
_context = context;
}
// GET: api/<Orders>
[HttpGet]
public async Task<IEnumerable<Order>> Get()
{
return await _context.Orders.Where(o => o.SoDeliveryDate.Date >= DateTime.Now.Date).ToListAsync();
//return await _context.Orders.Include(e => e.OrderLines).ToListAsync();
}
// GET api/<Orders>/5
[HttpGet("{id}")]
public async Task<IEnumerable<Order>> GetOneOrderAsync(int id)
{
return await _context.Orders.Where(o => o.OrderId == id).ToListAsync();
}
//GET api/<Orders>/cust/1
[HttpGet("cust/{id}")]
public async Task<IEnumerable<Order>> GetCustomerOrders(int id)
{
return await _context.Orders.Where(o => o.SoIaInvoiceAccount == id).ToListAsync();
}
// POST api/<Orders>
[HttpPost]
public void Post([FromBody] string value)
{
}
// PUT api/<Orders>/5
[HttpPut("{id}")]
public void Put(int id, [FromBody] string value)
{
}
// DELETE api/<Orders>/5
[HttpDelete("{id}")]
public void Delete(int id)
{
}
}
}
And finally the context:
using System;
using System.Configuration;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using OracleWCON_V2.RODataAccess.Models;
#nullable disable
namespace OracleWCON_V2.RODataAccess.Context
{
public partial class OrderContext : DbContext
{
public OrderContext()
{
}
public OrderContext(DbContextOptions<OrderContext> options)
: base(options)
{
}
public virtual DbSet<Order> Orders { get; set; }
public virtual DbSet<OrderLine> OrderLines { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
String connStr = ConfigurationManager.ConnectionStrings["wcon_ro"].ConnectionString;
optionsBuilder.UseOracle(connStr);
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("WCON_RO");
modelBuilder.Entity<OrderLine>(entity =>
{
entity.HasKey(e => new { e.OrderId, e.OlLineNo })
.HasName("OL_PK");
entity.ToTable("INV_ORDER_LINES", "LIVE");
entity.Property(e => e.OrderId)
.HasColumnType("NUMBER(22)")
.HasColumnName("OL_SO_INT_ORDER_REF");
entity.Property(e => e.OlLineNo)
.HasColumnType("NUMBER(22)")
.HasColumnName("OL_LINE_NO");
entity.Property(e => e.OlAppReference)
.HasPrecision(6)
.HasColumnName("OL_APP_REFERENCE");
entity.Property(e => e.OlBestEndeavours)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_BEST_ENDEAVOURS");
entity.Property(e => e.OlCanTakeMore)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_CAN_TAKE_MORE");
entity.Property(e => e.OlCashSaleUnitPrice)
.HasColumnType("NUMBER(6,2)")
.HasColumnName("OL_CASH_SALE_UNIT_PRICE");
entity.Property(e => e.OlDeliveryRateType)
.HasMaxLength(2)
.IsUnicode(false)
.HasColumnName("OL_DELIVERY_RATE_TYPE");
entity.Property(e => e.OlDepotRequested)
.HasPrecision(6)
.HasColumnName("OL_DEPOT_REQUESTED");
entity.Property(e => e.OlEmpBeOverrider)
.HasPrecision(6)
.HasColumnName("OL_EMP_BE_OVERRIDER");
entity.Property(e => e.OlEmpLastCommittedBy)
.HasPrecision(6)
.HasColumnName("OL_EMP_LAST_COMMITTED_BY");
entity.Property(e => e.OlLastCommitted)
.HasColumnType("DATE")
.HasColumnName("OL_LAST_COMMITTED");
entity.Property(e => e.OlLorriesOnTurnaround)
.HasPrecision(3)
.HasColumnName("OL_LORRIES_ON_TURNAROUND");
entity.Property(e => e.OlMinsBetweenLoads)
.HasPrecision(4)
.HasColumnName("OL_MINS_BETWEEN_LOADS");
entity.Property(e => e.OlNotes)
.HasMaxLength(60)
.IsUnicode(false)
.HasColumnName("OL_NOTES");
entity.Property(e => e.OlOlLineDependantOn)
.HasPrecision(8)
.HasColumnName("OL_OL_LINE_DEPENDANT_ON");
entity.Property(e => e.OlOoh)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_OOH");
entity.Property(e => e.OlPermissionToMix)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_PERMISSION_TO_MIX");
entity.Property(e => e.OlPrefPlant)
.HasMaxLength(3)
.IsUnicode(false)
.HasColumnName("OL_PREF_PLANT");
entity.Property(e => e.OlPrice)
.HasColumnType("NUMBER(22,2)")
.HasColumnName("OL_PRICE");
entity.Property(e => e.OlProductCode)
.IsRequired()
.HasMaxLength(6)
.IsUnicode(false)
.HasColumnName("OL_PRODUCT_CODE");
entity.Property(e => e.OlProductDesc)
.HasMaxLength(50)
.IsUnicode(false)
.HasColumnName("OL_PRODUCT_DESC");
entity.Property(e => e.OlQuantity)
.HasColumnType("NUMBER(22,2)")
.HasColumnName("OL_QUANTITY");
entity.Property(e => e.OlQuantityBeforeStop)
.HasColumnType("NUMBER(22,2)")
.HasColumnName("OL_QUANTITY_BEFORE_STOP");
entity.Property(e => e.OlRequestedBy)
.HasMaxLength(30)
.IsUnicode(false)
.HasColumnName("OL_REQUESTED_BY");
entity.Property(e => e.OlResumptionOfLine)
.HasColumnType("NUMBER(22)")
.HasColumnName("OL_RESUMPTION_OF_LINE");
entity.Property(e => e.OlSplHaulInd)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_SPL_HAUL_IND");
entity.Property(e => e.OlSplHaulRate)
.HasColumnType("NUMBER(22,2)")
.HasColumnName("OL_SPL_HAUL_RATE");
entity.Property(e => e.OlSplitLoad)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_SPLIT_LOAD");
entity.Property(e => e.OlStartDate)
.HasColumnType("DATE")
.HasColumnName("OL_START_DATE");
entity.Property(e => e.OlSwb)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_SWB");
entity.Property(e => e.OlTbc)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_TBC");
entity.Property(e => e.OlTime1)
.HasMaxLength(5)
.IsUnicode(false)
.HasColumnName("OL_TIME_1");
entity.Property(e => e.OlTime2)
.HasMaxLength(5)
.IsUnicode(false)
.HasColumnName("OL_TIME_2");
entity.Property(e => e.OlTimeCritical)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_TIME_CRITICAL");
entity.Property(e => e.OlTimeCriticalReason)
.HasMaxLength(60)
.IsUnicode(false)
.HasColumnName("OL_TIME_CRITICAL_REASON");
entity.Property(e => e.OlTonnesPerHour)
.HasColumnType("NUMBER(8,2)")
.HasColumnName("OL_TONNES_PER_HOUR");
entity.Property(e => e.OlVehicleRestriction)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("OL_VEHICLE_RESTRICTION");
entity.Property(e => e.OlWhyTbc)
.HasMaxLength(60)
.IsUnicode(false)
.HasColumnName("OL_WHY_TBC");
});
modelBuilder.Entity<Order>(entity =>
{
entity.HasKey(e => new { e.OrderId })
.HasName("ORDERS_PK");
entity.ToTable("INV_STONE_ORDERS", "LIVE");
entity.HasIndex(e => new { e.SoIaInvoiceAccount, e.SoDeliveryDate }, "SO_CUST_DATE");
entity.HasIndex(e => e.SoCustOrderNo, "SO_CUST_ORD");
entity.HasIndex(e => e.SoDeliveryDate, "SO_DELIVERY_DATE_IDX");
entity.HasIndex(e => e.OrderId, "SO_INT_ORDER_REF_IDX")
.IsUnique();
entity.HasIndex(e => new { e.OrderId, e.SoIaInvoiceAccount }, "SO_ORD_REF_I_A");
entity.HasIndex(e => new { e.SoOurOrderNo, e.SoDeliveryDate }, "SO_OUR_ORD");
entity.HasIndex(e => e.SoOwContract, "SO_OW_CONT");
entity.HasIndex(e => new { e.SoPostcode1, e.SoPostcode2 }, "SO_POST_CODE");
entity.HasIndex(e => e.SoRcNumber, "SO_RC");
entity.HasIndex(e => e.SoToBeLinked, "SO_TO_BE_LINKED");
entity.Property(e => e.SoAddress1)
.HasMaxLength(40)
.IsUnicode(false)
.HasColumnName("SO_ADDRESS_1");
entity.Property(e => e.SoAddress2)
.HasMaxLength(35)
.IsUnicode(false)
.HasColumnName("SO_ADDRESS_2");
entity.Property(e => e.SoAddress3)
.HasMaxLength(35)
.IsUnicode(false)
.HasColumnName("SO_ADDRESS_3");
entity.Property(e => e.SoAddress4)
.HasMaxLength(35)
.IsUnicode(false)
.HasColumnName("SO_ADDRESS_4");
entity.Property(e => e.SoApproved)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("SO_APPROVED");
entity.Property(e => e.SoApprovedBy)
.HasPrecision(4)
.HasColumnName("SO_APPROVED_BY");
entity.Property(e => e.SoAwaitingCreditApproval)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("SO_AWAITING_CREDIT_APPROVAL");
entity.Property(e => e.SoCanTakeToday)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("SO_CAN_TAKE_TODAY");
entity.Property(e => e.SoCancelled)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("SO_CANCELLED");
entity.Property(e => e.SoCancelledContact)
.HasMaxLength(30)
.IsUnicode(false)
.HasColumnName("SO_CANCELLED_CONTACT");
entity.Property(e => e.SoCashCustAcc)
.HasMaxLength(100)
.IsUnicode(false)
.HasColumnName("SO_CASH_CUST_ACC");
entity.Property(e => e.SoCt1CtId)
.HasColumnType("NUMBER")
.HasColumnName("SO_CT1_CT_ID");
entity.Property(e => e.SoCt2CtId)
.HasColumnType("NUMBER")
.HasColumnName("SO_CT2_CT_ID");
entity.Property(e => e.SoCt3CtId)
.HasColumnType("NUMBER")
.HasColumnName("SO_CT3_CT_ID");
entity.Property(e => e.SoCustOrderNo)
.HasMaxLength(30)
.IsUnicode(false)
.HasColumnName("SO_CUST_ORDER_NO");
entity.Property(e => e.SoCustQuoteNo)
.HasColumnType("NUMBER(22)")
.HasColumnName("SO_CUST_QUOTE_NO");
entity.Property(e => e.SoDateApproved)
.HasColumnType("DATE")
.HasColumnName("SO_DATE_APPROVED");
entity.Property(e => e.SoDateReceived)
.HasColumnType("DATE")
.HasColumnName("SO_DATE_RECEIVED");
entity.Property(e => e.SoDelInstructions)
.HasMaxLength(50)
.IsUnicode(false)
.HasColumnName("SO_DEL_INSTRUCTIONS");
entity.Property(e => e.SoDeliveryDate)
.HasColumnType("DATE")
.HasColumnName("SO_DELIVERY_DATE");
entity.Property(e => e.SoDespNotificationCt1)
.HasMaxLength(20)
.IsUnicode(false)
.HasColumnName("SO_DESP_NOTIFICATION_CT1");
entity.Property(e => e.SoDespNotificationCt2)
.HasMaxLength(20)
.IsUnicode(false)
.HasColumnName("SO_DESP_NOTIFICATION_CT2");
entity.Property(e => e.SoEmpLinkedBy)
.HasPrecision(8)
.HasColumnName("SO_EMP_LINKED_BY");
entity.Property(e => e.SoEmpTakenBy)
.HasPrecision(6)
.HasColumnName("SO_EMP_TAKEN_BY");
entity.Property(e => e.SoEoShortName)
.HasMaxLength(30)
.IsUnicode(false)
.HasColumnName("SO_EO_SHORT_NAME");
entity.Property(e => e.SoExworks)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("SO_EXWORKS");
entity.Property(e => e.SoFocAuthorisation)
.HasMaxLength(20)
.IsUnicode(false)
.HasColumnName("SO_FOC_AUTHORISATION");
entity.Property(e => e.SoFyPurchInvNo)
.HasPrecision(7)
.HasColumnName("SO_FY_PURCH_INV_NO");
entity.Property(e => e.SoIaInvoiceAccount)
.HasColumnType("NUMBER(22)")
.HasColumnName("SO_IA_INVOICE_ACCOUNT");
entity.Property(e => e.SoIncomingJourney)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("SO_INCOMING_JOURNEY");
entity.Property(e => e.SoPlacedBy)
truncated for posting
modelBuilder.HasSequence("WWN_NUMBER_SEQ", "LIVE");
modelBuilder.HasSequence("ZONE_MAP_SEQUENCE", "LIVE");
modelBuilder.HasSequence("ZPG_SEQUENCE", "LIVE");
OnModelCreatingPartial(modelBuilder);
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
}
As I said, the vast majority of that was generated by scaffolding from the database. Other than commenting out all but one of the properties and putting them back one by one until I find the offending one, is there a simpler way of debugging this? Or have I missed a vital pointer in the error?
