CODEWITHSUNDEEP
c#sqlentity-framework
tallman
tallman

Reputation: 201

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

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();

Upvotes: 4

Views: 3329

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109281

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.

Upvotes: 0

Ran Turner
Ran Turner

Reputation: 18156

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;

Upvotes: 2

Related Questions