Handling null decimal values in ASP.NET Core 6 MVC model

Question

I have a model in my ASP.NET Core 6 MVC data context that accepts a certain decimal value, myNullableDecimal. This value needs to be nullable, since "0.00" would be interpreted downstream as a valid, or non-null, response. So, I set the corresponding column in my SQL Server 2019 database table, to which the context will connect, to accept null values in the table designer. In my model, I declare the field like this:

public decimal? myNullableDecimal { get; set; }

This way, if the user chooses to provide a value, it gets stored, and if not, the db takes the null. When I submit new data, the validation goes through with no problem, and input is saved in the database. However, when I run a .ToList() linq query on the table that contains my nullable decimal column:

List<myTableModel> myData = (from t in _context.myTable
                             select t).ToList();

I get an error

This method or property cannot be called on null value.

The error is thrown from Microsoft.Data.SqlClient.SqlBuffer.get_Decimal(). If I update my column to contain a non-null value, the query works, but like I said, any non-null value in this column must be interpreted as valid, so defaulting this column to zero won't work.

I don't want to set the column as a string, since that would require me to convert my value to a decimal later on. I've tried setting a default value for my column in my model,

public decimal? myNullableDecimal { get; set; } = 0

but I still get the error. I understand from this ancient article that a custom model binder might hold the solution, but that same article warns against using them. What else can I do get around this error?

EDIT: The problem seems to lie in the fact that I have the MVC field for myNullableDecimal marked as [Required]. If I take away the requirement, a null value is no problem. But, once again, I can't do that because when the myNullableDecimal input field is presented to the user, the user is expected to enter a value here (it's hidden by default.) So, the full field declaration for myNullableDecimal in my model is:

[Required(ErrorMessage = "You must enter a value")]
public decimal? myNullableDecimal {get; set;} = 0

Apologies for not mentioning this sooner; I didn't realize the requirement mark made a difference in this case.

Tricky one this is.. Can you just exclude the null ones alltogether? `var myDataNotNull = _context.myTable.Where(d => d.myNullableDecimal != null).select(d => d.t).ToList();` — flashsplat, Aug 26 '22 at 16:18
@flashsplat unfortunately, no, because the column itself is optional; a non-null value only needs to go there under certain conditions. It's possible that I'll need to pull lists where myNullableDecimal is null in some rows and not null in others. — bmurrell30, Aug 26 '22 at 16:58

Ruikai Feng · Answer 1 · 2022-08-30T05:46:19.300

I tried as you mentioned,At first,I did not set the [Required]Attribute on

decimal? myNullableDecimal { get; set; }

The column could be null:

Then I set the [Required]Attribute on the property,The column became not null after you add migration and update database:

I checked the document related:https://learn.microsoft.com/en-us/ef/core/modeling/entity-properties?tabs=data-annotations%2Cwithout-nrt#required-and-optional-properties

Although I don't kown the details of your database ,but if you want to validate the property with [required ] Attribute and keep the myNullableDecimal could be null in your database, you could try to create a ViewModel and map it to Entity， add the [Required]attribute on the property of ViewModel,remove it in your Entity

Handling null decimal values in ASP.NET Core 6 MVC model

1 Answers1