EF Core insert only some columns from entity class

Question

EF Core always include ALL the columns of the class in the INSERT statement. How can I modify the script so that the INSERT statement will only include values for which I have provided a value?

Let's say I have a User class with 3 properties:

public int Id { get; set; }   // this one is linked to a auto increment ID column
public string Name { get; set; }
public int Amount { get; set; }   // linked to a column that cannot be null and has a default value configured in SQL Server

So what I am doing to insert a new record into the User table is:

User newUser = new User();
newUser.Name = "John";
await _dbc.AddAsync(newUser);
await _dbc.SaveChangesAsync();

The INSERT statement will include Name = "JOHN", BUT will also include Amount = null, so the query will fail because null is not accepted for Amount. I want to remove the Amount column from the generated INSERT statement to simply send

INSERT INTO User (NAME) 
VALUES ('John') 

and I want to let the SQL Server insert the default configured value into the Amount column.

It is a simple example, my real situation have many columns for which I have default value for insertion, and I do not want the INSERT statement that is generated to include ALL the columns from the User class, only those for which I provide a value.

Answer 1

For model properties which are not nullable and have a database default, you will want to either annotate or configure the property to make EF aware of the database default.

This article demonstrates both: https://www.learnentityframeworkcore.com/configuration/fluent-api/valuegeneratedonadd-method

Using the fluent configuration, you'll want to add ValueGeneratedOnAdd(). For example:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace YourNamespaceHere
{
    public partial class UserConfiguation : IEntityTypeConfiguration<User>
    {
        public void Configure(EntityTypeBuilder<User> builder)
        {
            // ... other field configurations
 
            builder
                .Property(m => m.Amount)
                .ValueGeneratedOnAdd();


            OnConfigurePartial(builder);
        }

        partial void OnConfigurePartial(EntityTypeBuilder<User> builder);
    }
}

As a data annotation, you'll use DatabaseGeneratedAttribute:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace YourNamespaceHere
{
    [Table(nameof(User))]
    public class User
    {
        // ... other properties

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public int Amount { get; set; }
    }
}

Answer 2

If creating the object doesn't require specifying the amount at that moment, why not make it nullable?

It seems like a design issue. Won't the amount be necessary at another time? If the answer is no, why not remove it from the table?

Otherwise, making it nullable seems like a good option, and later you can edit the record by inserting the amount.

public int Id { get; set; } 
public string Name { get; set; }
//By doing this, when creating the migration, the database understands that this property can accept null values.
public int? Amount { get; set; }