How to solve C# SqlNull Exception nullable on string?

Question

I get this error:

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
at Microsoft.Data.SqlClient.SqlBuffer.get_String()
at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
at lambda_method11(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at ReportCrewApi.Services.ViewMastereIsAktifServices.GetMastereIsAktifs() in D:\Punya Ricky\Works\Insosys\Local\API for report\ReportCrewAPI\Services\ViewMastereIsAktifServices.cs:line 26

I tried using string? before but when I take a look at this doc, for string, the reference will be nullable, so I didn't use it. But I keep getting the same error like that. I've already take a look at my data on database

Columns in the database table:

       (<NIK, varchar(10),>
       ,<NIK_lama, varchar(10),>
       ,<Nama, varchar(100),>
       ,<KodeOrganisasi, varchar(20),>
       ,<Unit, varchar(255),>
       ,<Jabatan, varchar(50),>
       ,<KodeLokasi, int,>
       ,<LokasiKerja, varchar(100),>
       ,<KodeManajemen, int,>
       ,<ManajemenLevel, varchar(30),>
       ,<TanggalMasuk, datetime,>
       ,<TanggalDiangkat, datetime,>
       ,<KodeStatus, int,>
       ,<StatusHubunganKerja, varchar(100),>
       ,<TanggalLahir, datetime,>
       ,<BulanLahir, int,>
       ,<KodeKelamin, int,>
       ,<JenisKelamin, varchar(20),>
       ,<KodePerkawinan, int,>
       ,<StatusPerkawinan, varchar(100),>
       ,<KodePerumahan, int,>
       ,<StatusPerumahan, varchar(100),>
       ,<Owner, char(1),>
       ,<JumlahAnak, int,>
       ,<Kp, varchar(50),>
       ,<Ko, varchar(20),>
       ,<umur, int,>
       ,<TahunMasaKerja, smallint,>
       ,<BulanMasaKerja, smallint,>
       ,<KodeAgama, int,>
       ,<Agama, varchar(100),>
       ,<TempatLahir, varchar(25),>
       ,<KodeDarah, int,>
       ,<Darah, varchar(100),>
       ,<Alamat, varchar(100),>
       ,<SekolahK, varchar(200),>
       ,<LokasiK, varchar(200),>
       ,<JurusanK, varchar(200),>
       ,<TahunK, int,>
       ,<IPK, float,>
       ,<KodePendidikan, int,>
       ,<Pendidikan, varchar(100),>
       ,<KodeTransportasi, int,>
       ,<Transportasi, varchar(100),>
       ,<CountHobi, int,>
       ,<CountBahasa, int,>
       ,<LokasiAbsen, varchar(4),>
       ,<NomorHP, varchar(25),>
       ,<GolonganHP, varchar(5),>
       ,<NoKTP, varchar(40),>
       ,<NoAstek, varchar(25),>
       ,<NoBPJSKesehatan, varchar(50),>
       ,<Passport, varchar(20),>
       ,<Initial, varchar(15),>
       ,<KepesertaanPensiun, tinyint,>
       ,<BebanKp, varchar(20),>
       ,<BebanKo, char(1),>
       ,<BebanUnit, varchar(255),>
       ,<NamaPasangan, varchar(50),>
       ,<TanggalLahirPasangan, int,>
       ,<UmurPasangan, int,>
       ,<AlamatKTP, varchar(100),>
       ,<TanggalAwalPerjanjian, datetime,>
       ,<TanggalAkhirPerjanjian, datetime,>
       ,<RecentTglAwalPerjanjian, datetime,>
       ,<RecentTglAkhirPerjanjian, datetime,>
       ,<RecentTglMulaiMutasi, datetime,>
       ,<RecentTglSelesaiMutasi, datetime,>
       ,<TanggalPHK, datetime,>
       ,<BulanPHK, int,>
       ,<NoPolisi, varchar(25),>
       ,<Telepon, varchar(25),>
       ,<NPWP, varchar(50),>
       ,<SIM, varchar(20),>
       ,<KodePos, varchar(6),>
       ,<ExtensionTelpon, varchar(25),>
       ,<KodePHK, int,>
       ,<KeteranganPHK, varchar(100),>
       ,<unit_cnn, varchar(300),>
       ,<posisi_cnn, varchar(300),>
       ,<tgl_mulai_cnn, nvarchar(10),>
       ,<tgl_selesai_cnn, nvarchar(10),>
       ,<no_sk_cnn, varchar(100),>
       ,<lokasi_kerja_cnn, varchar(100),>
       ,<NoAkun, int,>
       ,<KodeSukuUsaha, int,>
       ,<NamaSukuUsaha, int,>
       ,<email, varchar(200),>
       ,<UnitShortName, varchar(255),>
       ,<Hobby, varchar(255),>
       ,<rekening_nama, varchar(50),>
       ,<rekening_no, varchar(50),>
       ,<NamaAnak1, varchar(30),>
       ,<TanggalLahirAnak1, datetime,>
       ,<NamaAnak2, varchar(30),>
       ,<TanggalLahirAnak2, datetime,>
       ,<NamaAnak3, varchar(30),>
       ,<TanggalLahirAnak3, datetime,>
       ,<NamaAnak4, varchar(30),>
       ,<TanggalLahirAnak4, datetime,>
       ,<division_code, varchar(50),>
       ,<division_name, varchar(300),>
       ,<department_code, varchar(50),>
       ,<department_name, varchar(300),>
       ,<kode_divisi_statistik, tinyint,>
       ,<nama_divisi_statistik, varchar(30),>
       ,<kode_sts_kry_statistik, smallint,>
       ,<nama_sts_kry_statistik, varchar(50),>
       ,<directorate_code, varchar(50),>
       ,<directorate_name, varchar(300),>)

I tried to compare my data models but nothing is different.

Here is my model class:

public class ViewMastereIsAktif
{
    [Key]
    [StringLength(10)]
    
    public string NIK { get; set; }

    [StringLength(10)]
    public string NIK_lama  {get; set;}
    [StringLength(100)]
    public string Nama { get; set; }
    [StringLength(20)]
    public string KodeOrganisasi { get; set; }
    [StringLength(255)]
    public string Unit { get; set; }
    [StringLength(50)]
    public string Jabatan { get; set; }
    #nullable enable
    public int? KodeLokasi { get; set; }
    [StringLength(100)]
    public string LokasiKerja { get; set; }
    #nullable enable
    public int? KodeManajemen { get; set; }
    [StringLength(30)]
    public string ManajemenLevel { get; set; }
    #nullable enable
    public DateTime? TanggalMasuk { get; set; }
    #nullable enable
    public DateTime? TanggalDiangkat { get; set; }
    #nullable enable
    public int? KodeStatus { get; set; }
    [StringLength(100)]
    public string StatusHubunganKerja { get; set; }
    #nullable enable
    public DateTime? TanggalLahir { get; set; }
    #nullable enable
    public int? BulanLahir { get; set; }
    #nullable enable
    public int? KodeKelamin { get; set; }
    [StringLength(20)]
    public string JenisKelamin { get; set; }
    #nullable enable
    public int? KodePerkawinan { get; set; }
    [StringLength(100)]
    public string StatusPerkawinan { get; set; }
    #nullable enable
    public int? KodePerumahan { get; set; }
    [StringLength(100)]
    public string StatusPerumahan { get; set; }
    #nullable enable
    public char Owner { get; set; }
    #nullable enable
    public int? JumlahAnak { get; set; }
    [StringLength(50)]
    public string Kp { get; set; }
    [StringLength(20)]
    public string Ko { get; set; }
    #nullable enable
    public int? umur { get; set; }
    #nullable enable
    public Int16 TahunMasaKerja { get; set; }
    #nullable enable
    public Int16 BulanMasaKerja { get; set; }
    #nullable enable
    public int? KodeAgama { get; set; }
    [StringLength(100)]
    public string Agama { get; set; }
    [StringLength(25)]
    public string TempatLahir { get; set; }
    #nullable enable
    public int? KodeDarah { get; set; }
    [StringLength(100)]
    public string Darah { get; set; }
    [StringLength(100)]
    public string Alamat { get; set; }
    [StringLength(200)]
    public string SekolahK { get; set; }
    [StringLength(200)]
    public string LokasiK { get; set; }
    [StringLength(200)]
    public string JurusanK { get; set; }
    #nullable enable
    public int? TahunK { get; set; }
    #nullable enable
    public float IPK { get; set; }
    #nullable enable
    public int? KodePendidikan { get; set; }
    [StringLength(100)]
    public string Pendidikan { get; set; }
    #nullable enable
    public int? KodeTransportasi { get; set; }
    [StringLength(100)]
    public string Transportasi { get; set; }
    #nullable enable
    public int? CountHobi { get; set; }
    #nullable enable
    public int? CountBahasa { get; set; }
    [StringLength(4)]
    public string LokasiAbsen { get; set; }
    [StringLength(25)]
    public string NomorHP { get; set; }
    [StringLength(5)]
    public string GolonganHP { get; set; }
    [StringLength(40)]
    public string NoKTP { get; set; }
    [StringLength(25)]
    public string NoAstek { get; set; }
    [StringLength(50)]
    public string NoBPJSKesehatan { get; set; }
    [StringLength(20)]
    public string Passport { get; set; }
    [StringLength(15)]
    public string Initial { get; set; }
    #nullable enable
    public Byte? KepesertaanPensiun { get; set; }
    [StringLength(20)]
    public string BebanKp { get; set; }
    #nullable enable
    public char BebanKo { get; set; }
    [StringLength(255)]
    public string BebanUnit { get; set; }
    [StringLength(50)]
    public string NamaPasangan { get; set; }
    #nullable enable
    public int? TanggalLahirPasangan { get; set; }
    #nullable enable
    public int? UmurPasangan { get; set; }
    [StringLength(100)]
    public string AlamatKTP { get; set; }
    #nullable enable
    public DateTime? TanggalAwalPerjanjian { get; set; }
    #nullable enable
    public DateTime? TanggalAkhirPerjanjian { get; set; }
    #nullable enable
    public DateTime? RecentTglAwalPerjanjian { get; set; }
    #nullable enable
    public DateTime? RecentTglAkhirPerjanjian { get; set; }
    #nullable enable
    public DateTime? RecentTglMulaiMutasi { get; set; }
    #nullable enable
    public DateTime? RecentTglSelesaiMutasi { get; set; }
    #nullable enable
    public DateTime? TanggalPHK { get; set; }
    #nullable enable
    public int? BulanPHK { get; set; }
    [StringLength(25)]
    public string NoPolisi { get; set; }
    [StringLength(25)]
    public string Telepon { get; set; }
    [StringLength(50)]
    public string NPWP { get; set; }
    [StringLength(20)]
    public string SIM { get; set; }
    [StringLength(6)]
    public string KodePos { get; set; }
    [StringLength(25)]
    public string ExtensionTelpon { get; set; }
    #nullable enable
    public int? KodePHK { get; set; }
    [StringLength(100)]
    public string KeteranganPHK { get; set; }
    [StringLength(300)]
    public string unit_cnn { get; set; }
    [StringLength(300)]
    public string posisi_cnn { get; set; }
    [StringLength(10)]
    public string tgl_mulai_cnn { get; set; }
    [StringLength(10)]
    public string tgl_selesai_cnn { get; set; }
    [StringLength(100)]
    public string no_sk_cnn { get; set; }
    [StringLength(100)]
    public string lokasi_kerja_cnn { get; set; }
    #nullable enable
    public int? NoAkun { get; set; }
    #nullable enable
    public int? KodeSukuUsaha { get; set; }
    #nullable enable
    public int? NamaSukuUsaha { get; set; }
    [StringLength(200)]
    public string email { get; set; }
    [StringLength(255)]
    public string UnitShortName { get; set; }
    [StringLength(255)]
    public string Hobby { get; set; }
    [StringLength(50)]
    public string rekening_nama { get; set; }
    [StringLength(50)]
    public string rekening_no { get; set; }
    [StringLength(30)]
    public string NamaAnak1 { get; set; }
    #nullable enable
    public DateTime? TanggalLahirAnak1 { get; set; }
    [StringLength(30)]
    public string NamaAnak2 { get; set; }
    #nullable enable
    public DateTime? TanggalLahirAnak2 { get; set; }
    [StringLength(30)]
    public string NamaAnak3 { get; set; }
    #nullable enable
    public DateTime? TanggalLahirAnak3 { get; set; }
    [StringLength(30)]
    public string NamaAnak4 { get; set; }
    #nullable enable
    public DateTime? TanggalLahirAnak4 { get; set; }
    [StringLength(50)]
    public string division_code { get; set; }
    [StringLength(300)]
    public string division_name { get; set; }
    [StringLength(50)]
    public string department_code { get; set; }
    [StringLength(300)]
    public string department_name { get; set; }
    #nullable enable
    public Byte? kode_divisi_statistik { get; set; }
    [StringLength(30)]
    public string nama_divisi_statistik { get; set; }
    #nullable enable
    public Int16 kode_sts_kry_statistik { get; set; }
    [StringLength(50)]
    public string nama_sts_kry_statistik { get; set; }
    [StringLength(50)]
    public string directorate_code { get; set; }
    [StringLength(300)]
    public string directorate_name { get; set; }
}

Before I check the doc, I've already tried everything using #nullable enable, public string? Property {get; set} = null and I've been googling it in others questions (here actually) but they all say the same that's not using string?, the error pointing at my service when it says my string is null

ViewMastereIsAktifServices.cs

public class ViewMastereIsAktifServices : IViewMastereIsAktifServices
{
    private EFRMContext _efrm_context;
    private INVENTORYContext _inventory_context;

    public ViewMastereIsAktifServices(EFRMContext eFRMContext, INVENTORYContext iNVENTORYContext)
    {
        _inventory_context = iNVENTORYContext;
        _efrm_context = eFRMContext;
    }

    public IEnumerable<ViewMastereIsAktif> GetMastereIsAktifs()
    {
        //here is line 26
        var mstIsaktif = _inventory_context.view_mastereisaktif.OrderByDescending(x => x.NIK).ToList();
        return mstIsaktif;
    }
}

I really doubt that the problem is in the models, because I got the warning on the models, that says

Non-nullable property 'LokasiKerja' must contain a non-null value when exiting constructor. Consider declaring the property as nullable.

and every string after that property give me the same warning (I didn't get why the string get those warning when the property Jabatan isn't), that's why when I tried to run get my data on postman it give me the error (on very top above), I've been stuck at this point for 6 hours. How can I solve that?

Do you mean to return IEnumerable? That's a blocking pattern, depending on your use case. Calling .ToList() will load everything in that table, every time. — Chris Schaller
– Chris Schaller, Commented Mar 16, 2021 at 19:59

Chris Schaller · Accepted Answer · 2021-03-16 03:30:20Z

1

This error occurs when the model expects the column to be Required, even though the error message specifically talks about nullability the two concepts, though related, are in this case different.

Please remove the multiple calls to #nullable enable from the model, this is a compiler directive that is either on or off for the rest of the code, you are continually setting it to ON, but in this case, we are not actually disputing the nullability of the value, strings by default are nullable and will work fine in this context.

The question is, which Model... you have not tagged this as such but it looks like an OData Service Implementation (you mentioned postman as well)

In this case it seems as if the OData Model is the culprit, in that model I suspect your field is marked as Required.

To prove this, locate your EdmModel builder script and either remove any Required declarations for the LokasiKerja field, or you could put this at the end of your builder fluent configuration:

modelBuilder.Entity<ViewMastereIsAktif>()
    .Property(x => x.LokasiKerja).IsRequired(false);

Now when you request the data feed, if it is the Required state that is causing trouble the error message will either be resolved, or will be referring to the next column that is Required but is null.

Showing your fluent configuration for this entity might also help to resolve the issue.

A quick search on SO finds this solution which may also help: https://stackoverflow.com/a/64001223/1690217

FYI The reason the error stack points us towards SQL being the cause is due to the LINQ deferred logic and the projection that OData imposes on the actual SQL that is executed and deserialized.

answered Mar 16, 2021 at 3:30

Chris Schaller

17.1k3 gold badges57 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ricky Reza Muhammad Over a year ago

Thank your for replying, i still got an a null pointing on my line 26 an the same error as above, even i delete nullable enable, and create edmmodel builder. Should i cast every field into IsRequired false on model builder?

Chris Schaller Over a year ago

You could try it, if you have a convention that is making everything required that would work, however it would be good to try and track down the source of those too. I guess do them all to see if it resolves the issue

Collectives™ on Stack Overflow

How to solve C# SqlNull Exception nullable on string?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related