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.

This sounds like your data models are not configured properly. Can you include the configuration for the User model? — Tieson T.
– Tieson T., Commented Mar 21, 2024 at 21:15
you just said that DB does not accept null. Hence you need to set your model to the default value. See this stackoverflow.com/questions/27038524/… — T.S.
– T.S., Commented Mar 21, 2024 at 21:27
I am using this tool to generate my models: efg.loresoft.com/quickStart so if I modify my models, as soon as I will update them by running "efg generate" with this tool, it will remove any changes made... and I haven't found a way to setup this tool to add required default values from sql server. — Phil
– Phil, Commented Mar 22, 2024 at 2:12
so there is no simple way to simply build INSERT statements FIELD BY FIELD, still using the model to refer to them, but not including all properties of the class (other than writing your own SQL query) ? — Phil
– Phil, Commented Mar 22, 2024 at 14:50
If you write to someone you must include @userName otherwise we dont get notified about your comment — T.S.
– T.S., Commented Mar 26, 2024 at 19:34

Tieson T. · Accepted Answer · 2024-03-21 21:28:23Z

0

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; }
    }
}

answered Mar 21, 2024 at 21:28

Tieson T.

21.2k6 gold badges82 silver badges99 bronze badges

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

6 Comments

Phil Over a year ago

I forgot to mention that I am using this tool to generate my models: efg.loresoft.com/quickStart I do agree that .ValueGeneratedOnAdd() is a solution, but as soon as I will update my models by running "efg generate" with this tool, it will remove it... and I haven't found a way to setup this tool to auto generate .ValueGeneratedOnAdd() for columns having default value in sql server...

Tieson T. Over a year ago

@Phil Can't really help you there. Probably need to either not rely on that tool beyond the initial setup or find a better tool. For example, github.com/ErikEJ/EFCorePowerTools

Phil Over a year ago

thanks for trying to help! I just installed EFCorePowerTools and generated my Models from it. Same behavior than the other tool, it will not add ValueGeneratedOnAdd for columns not allowing null and having default values configured in SQL Server... So I still get CANNOT INSERT NULL INTO X col... And if I add ValueGeneratedOnAdd on some columns manually, then re-run the tool, it will remove them just like the other tool.

Tieson T. Over a year ago

Unless your models are constantly changing, I think you're going to have to decide which is more important. I generally only use things like the Power Tools to generate an initial set of models and configuration values, then rely on manual updates to change things later. At the end of the day, your only options are to a) find a tool that does what you want (not going to be something free, I suspect), b) update your tables to have nullable columns, or c) no rely on the tooling to do all the heavy lifting (in the end, your best bet).

Phil Over a year ago

so there is no simple way to simply build INSERT statements, FIELD BY FIELD, still using the model to refer to them, but not including all properties of the class (other than writing your own SQL query) right ?

|

Gabriel Ribeiro Rossi · Accepted Answer · 2024-03-23 16:36:33Z

0

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; }

answered Mar 23, 2024 at 16:36

Gabriel Ribeiro Rossi

1071 silver badge11 bronze badges

Collectives™ on Stack Overflow

EF Core insert only some columns from entity class

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related