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

Question

I'm trying to run an API project and it gives me an error like this:

Here is my code inside the API Models:

using Microsoft.EntityFrameworkCore.Metadata.Internal;  
using System;  
using System.Collections.Generic;  
using System.ComponentModel.DataAnnotations.Schema;  
using System.ComponentModel.DataAnnotations;  
using System.ComponentModel;  
  
// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.  
// If you have enabled NRTs for your project, then un-comment the following line:  
// #nullable disable  
  
namespace CatalogWatchAPI.Models  
{  
    public partial class Product  
    {  
  
        [Key]  
        public int ProductId { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Item Name")]  
        [Required(ErrorMessage = "This field is required")]  
        public string ItemName { get; set; }  
  
        [StringLength(1000)]  
        [DisplayName("Short Description")]  
        [Required(ErrorMessage = "This field is required")]  
        public string ShortDescription { get; set; }  
  
        [Column(TypeName = "decimal(18, 0)")]  
        [DisplayName("Price/PHP")]  
        [Required(ErrorMessage = "This field is required")]  
        public decimal? Price { get; set; }  
  
        [StringLength(1000)]  
        [DisplayName("Full Description")]  
        [Required(ErrorMessage = "This field is required")]  
        public string FullDescription { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Calibre")]  
        [Required(ErrorMessage = "This field is required")]  
        public string Calibre { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Movement")]  
        [Required(ErrorMessage = "This field is required")]  
        public string Movement { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Weight")]  
        [Required(ErrorMessage = "This field is required")]  
        public string Weight { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Height")]  
        [Required(ErrorMessage = "This field is required")]  
        public string Height { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Diameter")]  
        [Required(ErrorMessage = "This field is required")]  
        public string Diameter { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Thickness")]  
        [Required(ErrorMessage = "This field is required")]  
        public string Thickness { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Jewelries")]  
        [Required(ErrorMessage = "This field is required")]  
        public string Jewel { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Case Material")]  
        [Required(ErrorMessage = "This field is required")]  
        public string CaseMaterial { get; set; }  
  
        [StringLength(50)]  
        [DisplayName("Strap Material")]  
        [Required(ErrorMessage = "This field is required")]  
  
        public string StrapMaterial { get; set; }  
    }  
}  
  

And here is my code in API Controllers:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using CatalogWatchAPI.Models;

namespace CatalogWatchAPI.Controllers  
{  
    [Route("api/[controller]")]  
    [ApiController]  
    public class ProductsController : ControllerBase  
    {  
        private readonly ProductDbContext _context;  
  
        public ProductsController(ProductDbContext context)  
        {  
            _context = context;  
        }  
  
        // GET: api/Products  
        [HttpGet]  
        public async Task<ActionResult<IEnumerable<Product>>> GetProduct()  
        {  
            return await _context.Product.ToListAsync();  
        }  
  
        // GET: api/Products/5  
        [HttpGet("{id}")]  
        public async Task<ActionResult<Product>> GetProduct(int id)  
        {  
            var product = await _context.Product.FindAsync(id);  
  
            if (product == null)  
            {  
                return NotFound();  
            }  
  
            return product;  
        }  
  
        // PUT: api/Products/5  
        // To protect from overposting attacks, enable the specific properties you want to bind to, for  
        // more details, see https://go.microsoft.com/fwlink/?linkid=2123754.  
        [HttpPut("{id}")]  
        public async Task<IActionResult> PutProduct(int id, Product product)  
        {  
            if (id != product.ProductId)  
            {  
                return BadRequest();  
            }  
  
            _context.Entry(product).State = EntityState.Modified;  
  
            try  
            {  
                await _context.SaveChangesAsync();  
            }  
            catch (DbUpdateConcurrencyException)  
            {  
                if (!ProductExists(id))  
                {  
                    return NotFound();  
                }  
                else  
                {  
                    throw;  
                }  
            }  
  
            return NoContent();  
        }  
  
        // POST: api/Products  
        // To protect from overposting attacks, enable the specific properties you want to bind to, for  
        // more details, see https://go.microsoft.com/fwlink/?linkid=2123754.  
        [HttpPost]  
        public async Task<ActionResult<Product>> PostProduct(Product product)  
        {  
            _context.Product.Add(product);  
            await _context.SaveChangesAsync();  
  
            return CreatedAtAction("GetProduct", new { id = product.ProductId }, product);  
        }  
  
        // DELETE: api/Products/5  
        [HttpDelete("{id}")]  
        public async Task<ActionResult<Product>> DeleteProduct(int id)  
        {  
            var product = await _context.Product.FindAsync(id);  
            if (product == null)  
            {  
                return NotFound();  
            }  
  
            _context.Product.Remove(product);  
            await _context.SaveChangesAsync();  
  
            return product;  
        }  
  
        private bool ProductExists(int id)  
        {  
            return _context.Product.Any(e => e.ProductId == id);  
        }  
    }  
}  
  

This is my code inside the appsettings.json:

To add up here is my code inside the launchsettings.json inside the API project.

Answer 1

Hi @Kris Sapin ,

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

I have reproduced the problem on my side, the issue relates the [Required] attribute and the Allow Nulls setting in the Database Product Designer.

If we are using the [Required] attribute, after migration using EF core, the generate columns are not nullable. Then, if we change the Allow Nulls setting (to allow the nullable value) via the Database Product Designer, and then store the null value in the Table, after that, when query the table it will show the above issue, like this:

So, to solve this issue, try to remove the [Required] attribute from the Product class, like this:

public partial class Product  
{   
    [Key]  
    public int ProductId { get; set; }   
    [StringLength(50)]  
    [DisplayName("Item Name")]   
    public string ItemName { get; set; }  

    [StringLength(1000)]  
    [DisplayName("Short Description")]   
    public string ShortDescription { get; set; }  

    [Column(TypeName = "decimal(18, 0)")]  
    [DisplayName("Price/PHP")]   
    public decimal? Price { get; set; }  

    [StringLength(1000)]  
    [DisplayName("Full Description")]   
    public string FullDescription { get; set; }  

    [StringLength(50)]  
    [DisplayName("Calibre")]   
    public string Calibre { get; set; }  

    [StringLength(50)]  
    [DisplayName("Movement")]   
    public string Movement { get; set; }  

    [StringLength(50)]  
    [DisplayName("Weight")]   
    public string Weight { get; set; }  

    [StringLength(50)]  
    [DisplayName("Height")]   
    public string Height { get; set; }  

    [StringLength(50)]  
    [DisplayName("Diameter")]   
    public string Diameter { get; set; }  

    [StringLength(50)]  
    [DisplayName("Thickness")]   
    public string Thickness { get; set; }  

    [StringLength(50)]  
    [DisplayName("Jewelries")]   
    public string Jewel { get; set; }  

    [StringLength(50)]  
    [DisplayName("Case Material")]   
    public string CaseMaterial { get; set; }  

    [StringLength(50)]  
    [DisplayName("Strap Material")]   

    public string StrapMaterial { get; set; }  
}  

Then, you can create a ProductViewModel with the [Required] attribute, then use this view model to Insert/Edit the new product. [Note] by using this method, before insert the new item into the database or query the database, you need to convert the model between Product and ProductViewModel.

Otherwise, if you still using the [Required] attribute in the Project class, you can unchecked the Allow Nulls checkboxes in the Database Product Designer (set the columns not nullable), the update the database. After that view the table data via the SSMS, and find all null value and then set its value. Then, you can query the table and get the data.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 2

This is what helped me resolve this.

If you're using the Scaffold-DbContext to generate the classes, there is a commented section at the top of the classes:

// Code scaffolded by EF Core assumes nullable reference types (NRTs) are not used or disabled.

// If you have enabled NRTs for your project, then un-comment the following line:

// #nullable disable

Uncommenting #nullable disable resolved this.

Answer 3

I think that if the columns are nullable, then the [Required] attribute should not be used.

If the [Required] attribute is used, then the columns should not be nullable.

Answer 4

public partial class Product