Entity Framework .NET Core returns 'Data is Null. This method or property cannot be called on Null values.' but not in Debugger

Question

The problem:

on C#, MVC API with Angular 6 front end on IIS deployed solution.

A simple HTML table with 8 filtering fields on specific columns runs queries on the back-end through an API HttpGet call. The function itself works ok, but NOT for specific strings. On the field 'Description' for example, the back-end returns 'Data is Null. This method or property cannot be called on Null values' when below strings are used. Values are correctly passed between front and back-end and the API call mostly returns the appropriate data.

However strings such as app and longer than app such as application, cert and longer such as certificate will return the 'Data is Null' error.

If I use f as the input for transmittal I get the 'Data is Null' error. If however I use crc on document and THEN use f as the input for transmittal the function returns normal data??

This only happens in the deployed state, the problem does not occur when debugging the code. When debugging, the code returns the expected results and always produces the correct response.

the service call using a set of 'chained' where statements to build an IQueryable which gets called to return a .ToListAsync() at the end of the sequence.

---

Using:

• IIS, version 10, running on Virtual Machine
• Operating System IIS virtual machine: Windows Server 2016
• .Net Core: 2.1
• EntityFrameworkCore: 2.1.11
• SQL Server 2012 (running on same VM)
• VS 2019
• SQL Server Management Studio 2018

---

the Controller code

[HttpGet("[action]")] public async Task<IActionResult> Search(string TR, string DA, string DO, string RE, string TY, string DD, string DI, string OR, string DE) { return Ok(await _archiveService.Search(TR, DA, DO, RE, TY, DD, DI, OR, DE)); } 

---

the ArchiveService code

public async Task<IEnumerable<object>> Search( string transmittal, string date, string document, string revision, string type, string description, string discipline, string origin, string destination) { var result = _db.TblCorrespondenceLog.AsQueryable(); if (!string.IsNullOrEmpty(transmittal)) result = result.Where(f => f.TransmittalNo.Contains(transmittal)); if (!string.IsNullOrEmpty(date)) { } if (!string.IsNullOrEmpty(document)) result = result.Where(f => f.CompanyDocumentNo.ToLower().Contains(document)); if (!string.IsNullOrEmpty(type)) result = result.Where(f => f.DocumentType.ToLower() == type); if (!string.IsNullOrEmpty(description)) result = result.Where(f => f.DocumentDescription.ToLower().Contains(description)); if (!string.IsNullOrEmpty(discipline)) result = result.Where(f => f.Discipline.ToLower() == discipline); if (!string.IsNullOrEmpty(origin)) result = result.Where(f => f.OriginatorCode.ToLower() == origin); if (!string.IsNullOrEmpty(destination)) result = result.Where(f => f.SupplierCode.ToLower() == destination); return await result.ToListAsync(); } 

The fields that give the errors are the fields that have the .Contains() clause

When I use SQL writer to obtain the query that get's fired to the server I get the below query, which results in the 'Data is Null' error when the .ToListAsync() gets called:

SELECT [f].[uID], [f].[Action_Required], [f].[Approved_Responded], [f].[Area_Code], [f].[Company_Document_No], [f].[Discipline], [f].[Document_Description], [f].[Document_Type], [f].[fileUID], [f].[Link], [f].[Originator_Code], [f].[Reference_Document], [f].[ResponsibelEngineer], [f].[ResubDate], [f].[ResubTransmittal], [f].[Rev], [f].[Sequential_Number], [f].[Sheet_Number], [f].[Supplier_Code], [f].[TDate], [f].[Transmittal_Date], [f].[Transmittal_No], [f].[Transmittal_Type], [f].[TRN_Originator], [f].[TurnAroundDate], [f].[Unit_Code], [f].[TransmitUID] FROM [tblCorrespondenceLog] AS [f] WHERE CHARINDEX(N'f', [f].[Transmittal_No]) > 0 

Which, when fired from SMSS (SQL Server Management Studio 2018) works perfectly fine?

If I use crc as input for document I get data returned as normal but the query is only marginally different:

SELECT [f].[uID], [f].[Action_Required], [f].[Approved_Responded], [f].[Area_Code], [f].[Company_Document_No], [f].[Discipline], [f].[Document_Description], [f].[Document_Type], [f].[fileUID], [f].[Link], [f].[Originator_Code], [f].[Reference_Document], [f].[ResponsibelEngineer], [f].[ResubDate], [f].[ResubTransmittal], [f].[Rev], [f].[Sequential_Number], [f].[Sheet_Number], [f].[Supplier_Code], [f].[TDate], [f].[Transmittal_Date], [f].[Transmittal_No], [f].[Transmittal_Type], [f].[TRN_Originator], [f].[TurnAroundDate], [f].[Unit_Code], [f].[TransmitUID] FROM [tblCorrespondenceLog] AS [f] WHERE (CHARINDEX(N'f', [f].[Transmittal_No]) > 0) AND (CHARINDEX(N'crc', LOWER([f].[Company_Document_No])) > 0) 

So... Why does entity framework give me a 'Data is Null' error only on specific strings and in specific combinations. Where the function would otherwise return proper values even if filtering on the 'problem' fields (using different strings of course)?

Answer 1

It is caused by a disconnect between the .NET Core Entity Class and the database table;

The class Configiration was set as:

 builder.Property(e => e.DocumentDescription) .IsRequired() .HasColumnName("Document_Description") .HasMaxLength(150); 

but the database table had this field as allow null therefore sometimes returning null where entity framework did not expect to see a null.

removing the .IsRequired() bit did the job.

 builder.Property(e => e.DocumentDescription) //.IsRequired() .HasColumnName("Document_Description") .HasMaxLength(150); 

Funny enough though, this didn't produce errors in the development environment but DID cause problems when deployed. I haven't been able to figure out why there is a difference, but at least it works now!