c#asp.net-mvcoracleasp.net-coredatareader
How to solve error "System.InvalidCastException - column contains NULL data"
When I run the application and search I got this error
System.InvalidCastException - column contains NULL data
at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal
This is my code DB repository code :
public List<LabResult> Search(string term)
{
return db.LabResults
.Where(a => a.PatientNo.ToString() == term)
.ToList(); // error on this line
}
This is the view markup:
@model IEnumerable<OracleHIS.Models.LabResult>
@{
ViewData["Title"] = "Index";
}
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.PatientNo)
</th>
<th>
@Html.DisplayNameFor(model => model.LabOrderNo)
</th>
<th>
@Html.DisplayNameFor(model => model.PatientNameE)
</th>
<th>
@Html.DisplayNameFor(model => model.LongForiegnDesc)
</th>
<th>
@Html.DisplayNameFor(model => model.ServNumResult)
</th>
</tr>
</thead>
<tbody>
@if (Model != null)
{
foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.PatientNo)
</td>
<td>
@Html.DisplayFor(modelItem => item.LabOrderNo)
</td>
<td>
@Html.DisplayFor(modelItem => item.PatientNameE)
</td>
<td>
@Html.DisplayFor(modelItem => item.LongForeignDesc)
</td>
<td>
@Html.DisplayFor(modelItem => item.ServNumResult)
</td>
<td>
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
</td>
</tr>
}
}
</tbody>
</table>
And this is the model class:
namespace OracleHIS.Models
{
public partial class LabResult
{
public decimal PatientNo { get; set; }
public decimal LabOrderNo { get; set; }
public string PatientNameE { get; set; } = null!;
public string LongForiegnDesc { get; set; } = null!;
public decimal ServNumResult { get; set; }
}
}
I found this solution
https://stackoverflow.com/questions/26024722/handle-null-values-when-reading-through-oracledatareader
but where I will use the IsDBNull() in my code?
OracleDataReader provides a IsDBNull() method.
this is the Model in DBset context its a VIEW not TABLE include columns from multiple tables :
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("TRNGKAASH")
.UseCollation("USING_NLS_COMP");
modelBuilder.Entity<LabResult>(entity =>
{
entity.HasNoKey();
entity.ToView("LAB_RESULTS");
entity.Property(e => e.AbnormalFlag)
.HasColumnType("NUMBER")
.HasColumnName("ABNORMAL_FLAG");
entity.Property(e => e.ApprovingDateG)
.HasColumnType("DATE")
.HasColumnName("APPROVING_DATE_G");
entity.Property(e => e.CancelBy)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("CANCEL_BY");
entity.Property(e => e.CancelDateG)
.HasColumnType("DATE")
.HasColumnName("CANCEL_DATE_G");
entity.Property(e => e.CancelDateH)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("CANCEL_DATE_H");
entity.Property(e => e.CancelReason)
.HasPrecision(6)
.HasColumnName("CANCEL_REASON");
entity.Property(e => e.DateOfBirth)
.HasPrecision(8)
.HasColumnName("DATE_OF_BIRTH");
entity.Property(e => e.EndResult)
.HasPrecision(6)
.HasColumnName("END_RESULT");
entity.Property(e => e.EventNo)
.HasPrecision(4)
.HasColumnName("EVENT_NO");
entity.Property(e => e.GramStain)
.HasMaxLength(3000)
.IsUnicode(false)
.HasColumnName("GRAM_STAIN");
entity.Property(e => e.GroupNo)
.HasPrecision(6)
.HasColumnName("GROUP_NO");
entity.Property(e => e.HeparinFlag)
.HasPrecision(1)
.HasColumnName("HEPARIN_FLAG");
entity.Property(e => e.HospitalNo)
.HasMaxLength(10)
.IsUnicode(false)
.HasColumnName("HOSPITAL_NO");
entity.Property(e => e.InitDiagnisis)
.HasMaxLength(300)
.IsUnicode(false)
.HasColumnName("INIT_DIAGNISIS");
entity.Property(e => e.LabNo)
.HasPrecision(6)
.HasColumnName("LAB_NO");
entity.Property(e => e.LabOrderNo)
.HasPrecision(12)
.HasColumnName("LAB_ORDER_NO");
entity.Property(e => e.LastUpdateDate)
.HasColumnType("DATE")
.HasColumnName("LAST_UPDATE_DATE");
entity.Property(e => e.LastUpdateTransaction)
.HasMaxLength(1)
.IsUnicode(false)
.HasColumnName("LAST_UPDATE_TRANSACTION");
entity.Property(e => e.LastUpdateUser)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("LAST_UPDATE_USER");
entity.Property(e => e.LongForiegnDesc)
.HasMaxLength(40)
.IsUnicode(false)
.HasColumnName("LONG_FORIEGN_DESC");
entity.Property(e => e.MachineId)
.HasColumnType("NUMBER")
.HasColumnName("MACHINE_ID");
entity.Property(e => e.MedicalCheck)
.HasPrecision(1)
.HasColumnName("MEDICAL_CHECK");
entity.Property(e => e.MrMerge)
.HasPrecision(12)
.HasColumnName("MR_MERGE");
entity.Property(e => e.Nationality)
.HasPrecision(6)
.HasColumnName("NATIONALITY");
entity.Property(e => e.PanicFlag)
.HasColumnType("NUMBER")
.HasColumnName("PANIC_FLAG");
entity.Property(e => e.PatientCategory)
.HasPrecision(6)
.HasColumnName("PATIENT_CATEGORY");
entity.Property(e => e.PatientHospital)
.HasMaxLength(10)
.IsUnicode(false)
.HasColumnName("PATIENT_HOSPITAL");
entity.Property(e => e.PatientNameA)
.HasMaxLength(150)
.IsUnicode(false)
.HasColumnName("PATIENT_NAME_A");
entity.Property(e => e.PatientNameE)
.HasMaxLength(150)
.IsUnicode(false)
.HasColumnName("PATIENT_NAME_E");
entity.Property(e => e.PatientNo)
.HasPrecision(12)
.HasColumnName("PATIENT_NO");
entity.Property(e => e.PatientSourceInd)
.HasPrecision(6)
.HasColumnName("PATIENT_SOURCE_IND");
entity.Property(e => e.PrioFlag)
.HasPrecision(6)
.HasColumnName("PRIO_FLAG");
entity.Property(e => e.ProvidingResource)
.HasPrecision(6)
.HasColumnName("PROVIDING_RESOURCE");
entity.Property(e => e.Reason)
.HasMaxLength(300)
.IsUnicode(false)
.HasColumnName("REASON");
entity.Property(e => e.RefSourceNo)
.HasMaxLength(10)
.IsUnicode(false)
.HasColumnName("REF_SOURCE_NO");
entity.Property(e => e.RefType)
.HasPrecision(6)
.HasColumnName("REF_TYPE");
entity.Property(e => e.ResultNotes)
.IsUnicode(false)
.HasColumnName("RESULT_NOTES");
entity.Property(e => e.SampleCollectedBy)
.HasPrecision(5)
.HasColumnName("SAMPLE_COLLECTED_BY");
entity.Property(e => e.SampleCollectedDateG)
.HasColumnType("DATE")
.HasColumnName("SAMPLE_COLLECTED_DATE_G");
entity.Property(e => e.SampleCollectedDateH)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("SAMPLE_COLLECTED_DATE_H");
entity.Property(e => e.SampleNo)
.HasPrecision(12)
.HasColumnName("SAMPLE_NO");
entity.Property(e => e.SampleNote)
.HasMaxLength(300)
.IsUnicode(false)
.HasColumnName("SAMPLE_NOTE");
entity.Property(e => e.SampleReceivedDateG)
.HasColumnType("DATE")
.HasColumnName("SAMPLE_RECEIVED_DATE_G");
entity.Property(e => e.SampleReceivedDateH)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("SAMPLE_RECEIVED_DATE_H");
entity.Property(e => e.SampleRecievedBy)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("SAMPLE_RECIEVED_BY");
entity.Property(e => e.SampleType)
.HasPrecision(6)
.HasColumnName("SAMPLE_TYPE");
entity.Property(e => e.ServCancelBy)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("SERV_CANCEL_BY");
entity.Property(e => e.ServCancelDateG)
.HasColumnType("DATE")
.HasColumnName("SERV_CANCEL_DATE_G");
entity.Property(e => e.ServCancelDateH)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("SERV_CANCEL_DATE_H");
entity.Property(e => e.ServCancelReason)
.HasColumnType("NUMBER")
.HasColumnName("SERV_CANCEL_REASON");
entity.Property(e => e.ServNo)
.HasPrecision(6)
.HasColumnName("SERV_NO");
entity.Property(e => e.ServNumResult)
.HasColumnType("NUMBER")
.HasColumnName("SERV_NUM_RESULT");
entity.Property(e => e.ServRequestDateG)
.HasColumnType("DATE")
.HasColumnName("SERV_REQUEST_DATE_G");
entity.Property(e => e.ServRequestDateH)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("SERV_REQUEST_DATE_H");
entity.Property(e => e.ServRequestDoctorName)
.HasMaxLength(150)
.IsUnicode(false)
.HasColumnName("SERV_REQUEST_DOCTOR_NAME");
entity.Property(e => e.ServRequestDoctorNo)
.HasPrecision(5)
.HasColumnName("SERV_REQUEST_DOCTOR_NO");
entity.Property(e => e.ServRequestUserId)
.HasMaxLength(8)
.IsUnicode(false)
.HasColumnName("SERV_REQUEST_USER_ID");
entity.Property(e => e.ServTextResult)
.HasMaxLength(500)
.IsUnicode(false)
.HasColumnName("SERV_TEXT_RESULT");
entity.Property(e => e.ServType)
.HasPrecision(6)
.HasColumnName("SERV_TYPE");
entity.Property(e => e.Sex)
.HasPrecision(1)
.HasColumnName("SEX");
entity.Property(e => e.SpecialCase)
.HasPrecision(6)
.HasColumnName("SPECIAL_CASE");
});
And this is the VIEW declaration in SQL :
I opened view error details and this is the details :
at Oracle.ManagedDataAccess.Client.OracleDataReader.GetInt32(Int32 i)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at OracleHIS.Models.Repository.LabDbRepository.Search(String term) in D:\HIS\OracleHIS\OracleHIS\Models\Repository\LabDbRepository.cs:line 59
at OracleHIS.Controllers.LabController.Search(String term) in D:\HIS\OracleHIS\OracleHIS\Controllers\LabController.cs:line 59
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
Solution
Your model should match your table/view types including the nullability.
As the table structure shows - all columns can contain null's so you need to mark all properties that are value types (i.e. decimals, ints, DateTimes, etc.) as nullable value types, as you done with decimal's (note that actual exception has changed the problem datatype after changing decimals to descimal?).
If you have nullable reference types enabled in your project you may want to mark reference type properties too.