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 >
<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()
CodePudding user response：
It is clear that one of the rows have a null for one of the decimal values. To confirm, execute the following sql statement:
Select * from labresults where PatientNo is null or LabOrderNo is null or ServNumResult is null
CodePudding user response：
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 to.
CodePudding user response：
Change the PatientNo to be:
public decimal? PatientNo { get; set; }
also Don't use .ToString() in where clause
public List<LabResult> Search(string term)
{
var decm = Convert.ToDecimal(term)
return db.LabResults
.Where(a => a.PatientNo == decm)
.ToList();
}