How to solve error "System.InvalidCastException - column contains NULL data"

Question

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()

Don't use `.ToString()` to compare numbers with strings. Parse the string into a number instead — Panagiotis Kanavos, Nov 14 '22 at 20:11
@PanagiotisKanavos how to parse can you post the answer please — Ziad Adnan, Nov 14 '22 at 20:19
_"how to parse can you post the answer please"_ - this is covered in my answer to your previous question. Though it is strange that you have `No` columns as decimal, are they decimal in the database? — Guru Stron, Nov 14 '22 at 21:17
As for `null` exception - it means that one of decimal fields is actually nullable in the database, you need to find which one and change corresponding field in the model to nullable `decimal` (for example `public decimal? ServNumResult { get; set; }`). Also as written in the previous comment - are you sure you need decimals? What actual types are the columns in the database? — Guru Stron, Nov 14 '22 at 21:19
@GuruStron about the column ServNumResult its some times will be null before type the result and the column datatype in oracle database was NUMBER(12,3) because it can be 2340.5 — Ziad Adnan, Nov 15 '22 at 05:09
@ZiadAdnan what about other two? Do they also need to be decimals? Can they be null? — Guru Stron, Nov 15 '22 at 05:22
As for `ServNumResult` - have you tried proposed `decimal?` solution? — Guru Stron, Nov 15 '22 at 05:23
@GuruStron no only this column can be null ServNumResult all others have value , I commented this field in the view but still show the error — Ziad Adnan, Nov 15 '22 at 05:24
@GuruStron yes I tried also decimal? in the model but still show the error — Ziad Adnan, Nov 15 '22 at 05:29
@ZiadAdnan what view? Can you please show how `LabResults` is configured in the dbset? And how the corresponding table/view is declared in sql? — Guru Stron, Nov 15 '22 at 05:29
@GuruStron I added the view in DBSET and SQL thank you for your help sir :) — Ziad Adnan, Nov 15 '22 at 06:00
@ZiadAdnan all of the properties are listed as nullable as far as I can see, so change all decimals to be nullable ones, like was suggested for `ServNumResult`. This is valid for other value types like `DateTime`. Also I don't see any number column specifying precision. — Guru Stron, Nov 15 '22 at 06:06
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249610/discussion-between-ziad-adnan-and-guru-stron). — Ziad Adnan, Nov 15 '22 at 06:37

Guru Stron · Accepted Answer · 2022-12-12T08:59:29.863

3

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.

edited Dec 12 '22 at 08:59

answered Nov 15 '22 at 06:49

Guru Stron

102,774
10
95
132

1

I checked the columns in the database and DBCONTEXT and made it as it is in the database any column with the type NUMBER(1) or NUMBER(2) or NUMBER(3) I changed it to int and if the column type NUMBER(4) or greater I changed it to decimal , also changed the type if NULL in the database I changed it to null in the model decimal? , int? , string? , also changed some types read as long? or short? or bool? I changed it as explained before to decimal? int? string? depends on column type in the database , thank you so much :) – Ziad Adnan Nov 16 '22 at 08:29

Mohm Zanaty · Answer 2 · 2022-11-15T08:32:58.813

1

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

edited Nov 15 '22 at 08:32

answered Nov 14 '22 at 21:23

Mohm Zanaty

544
7
27

I tried this solution but still show the same error – Ziad Adnan Nov 15 '22 at 05:21
Change the PatientNo to be decimal? and try – Mohm Zanaty Nov 15 '22 at 08:33

score 0 · Answer 3 · answered Nov 15 '22 at 06:38

0

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

answered Nov 15 '22 at 06:38

Tarik

10,810
2
26
40

yes ServNumResult can be null – Ziad Adnan Nov 16 '22 at 08:31

How to solve error "System.InvalidCastException - column contains NULL data"

3 Answers3

Linked