Entity Add/Insert with column equal to identity primary key value

user_6693268 0
Viewed 954

During an insert/add can I make a different column equal to the newly valued primary key that's an identity auto generated value all in one write/save process? I know I can grab after fact and change but that's extra database hits I'm trying to avoid.

public class myDataTableRec
{
    public int Id { get; set; }    //This is an Identity Primary Key
    public string Name { get; set; }
    public int PostId { get; set; }  //Want this the same as Id when it gets generated
}


myrec = new myDataTableRec;
db.myDataTable.Add(myrec);
db.SaveChanges();
entity-framework sql c#
3 Answers

You have to set the property of StoreGeneratedPattern to identity and then you'll be able to achieve this

 myrec = new myDataTableRec;
 db.myDataTable.Add(myrec);
 db.SaveChanges();
 var id = myrec.Id;
user_74942180

From https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbcontext.add?view=efcore-5.0

"Add(TEntity)

Begins tracking the given entity, and any other reachable entities that are not already being tracked, in the Added state such that they will be inserted into the database when SaveChanges() is called.

Use State to set the state of only a single entity."

Since it tracks the entity, you can do the following:

        myrec=new myDataTableRec;
        db.myDataTable.Add(myrec);
        myrec.PostId = myrec.Id
        db.SaveChanges();

Edit: If it creates the Id after the Save, the you can always create your own Id (Guid.NewGuid()) and don't let EF generate it.

user_141068730

In SQL it's not possible to insert an entity and set its generated primary in a foreign key column to itself in one atomic operation. That's only possible if the primary key is not generated, i.e. not an identity column.

However, just as EF is capable of inserting related entities in one operation and setting generated key values in foreign keys on the fly, one might argue that EF could support setting a parent to itself.

Take this entity class (replacing your placeholder name and enhancing it with navigation properties):

class Post
{
    public int ID { get; private set; }
    public string Name { get; set; }
    public int? ParentPostID { get; private set; }

    public Post ParentPost { get; set; }
    public ICollection<Post> ChildPosts { get; private set; }
}

EF could have chosen to support this scenario:

using var db = new MyContext();
var root = new Post { Name = "Root" };
root.ParentPost = root;
db.Set<Post>().Add(root);

But it doesn't. It tries to insert the entity with a ParentPostID equal to the temporary (negative) ID value. Obviously, that's a FK violation.

To do this cleanly you have to add a transaction and set & save the self reference separately:

using var db = new MyContext();
var root = new Post { Name = "Root" };
db.Set<Post>().Add(root);

using var ts = new TransactionScope();
db.SaveChanges();
root.ParentPost = root;
db.SaveChanges();
ts.Complete();

Note that the parent id has to be nullable. Also note that the navigation property allows setting the parent without ever knowing its key value. Some people like to do these things in a DDD style.

user_8617160
Related