How do I retrieve a key field that is a primary key and automatically created in SQL Server

Question

What I am trying to do is create a record in 2 tables, Communities and CommunityTeams. Each of these have a primary key ID which is set as a Identity 1.1 in SQL Server. Now, I would like to capture the key of Communities as a foreign key in CommunityTeams, but I have no way of knowing what that ID is.

Here is my code in ASP.NET MVC and Entity Framework:

if (ModelState.IsValid)
{
    // Community Info
    model.CommunityType = Convert.ToInt32(fc["communityType"]);
    model.ComunityName = fc["communityName"];
    model.CommunityCity = fc["communityCity"];
    model.CommunityState = fc["communityState"];
    model.CommunityCounty = fc["communityCounty"];
    model.Population = Convert.ToInt32(fc["communityPop"]);

    // Save to Database
    model.Active = true;
    model.DateCreated = DateTime.Now;
    model.CreatedBy = User.Identity.Name;
    model.Application_Complete = true;
    model.Application_Date = DateTime.Now;
    model.Payment_Complete = true;
    model.Payment_Date = DateTime.Now;
    model.Renewal = true;
    model.Renewal_Date = DateTime.Now;

    team.TeamLeader = true;
    team.Admin = true;
    var user = User.Identity.Name;
    team.UserName = user.ToString();
    team.CommunityId = 1;

    db.CommunityTeams.Add(team);
    db.Communities.Add(model);    

    db.SaveChanges();

    return RedirectToAction("Index", "Habitats");
}

Can you show the entity models (`Community` and `CommunityTeam`)? At least one of them should have navigation property, so at least tell if `Community` has `ICollection — Ivan Stoev, Mar 01 '17 at 20:49
No, the **property**. Something like `public ICollection Teams { get;set; }` in `Community` class or `public Community Community { get; set; }` in `CommunityTeam` class. — Ivan Stoev, Mar 01 '17 at 20:53
Possible answer here? http://stackoverflow.com/questions/5212751/how-can-i-get-id-of-inserted-entity-in-entity-framework Ladislav Mrnka's answer? — DVT, Mar 01 '17 at 20:53
I do not have anything like either of those ... just to be clear, i am trying to create a community record and a community team record at the same time, but I am trying to populate the Community ID record in Community Team, which is not known until the community ID record is created. — Paul T. Rykiel, Mar 01 '17 at 20:56
Hmm, without any of those, it's not possible to define `one-to-many` FK relationship (at least in EF 6 Code First). — Ivan Stoev, Mar 01 '17 at 21:00
Possible duplicate of [How can I get Id of inserted entity in Entity framework?](http://stackoverflow.com/questions/5212751/how-can-i-get-id-of-inserted-entity-in-entity-framework) — Nick.Mc, Mar 02 '17 at 01:53

dbraillon · Answer 1 · 2017-03-01T20:58:53.237

1

I will admit that you have a navigation property to Community in your CommunityTeam entity.

Replace team.CommunityId = 1; by team.Community = model;. Then simply add the team entity, EF will create both model and team.

db.CommunityTeams.Add(team);
db.SaveChanges();

You can also split the save in two parts by calling db.SaveChanges(); between the two Add call. The first save will create the Community entity, EF will fill your primary key automatically so you can use it in Team entity for the second save.

edited Mar 01 '17 at 20:58

answered Mar 01 '17 at 20:56

dbraillon

1,742
2
22
34

So once I do the initial save and create the Community record ... how do I retrieve the CommunityId field to populate the CommunityTeam – Paul T. Rykiel Mar 01 '17 at 20:58
1

EF will fill the primary key automatically with the newly created ID. – dbraillon Mar 01 '17 at 20:59

How do I retrieve a key field that is a primary key and automatically created in SQL Server

1 Answers1