Entity Framework Foreign Key Inserts with Auto-Id

Question

I have 2 Entities User and User_Profile (one to one relationship). I have linked them as follows:

public class User
{
   [Key]   
   [ForeignKey("user_profile")]
   public int user_id {get;set;}

   public string username {get;set;}
   public string email {get;set;}

   public virtual User_Proile user_profile {get;set;}
}


public class User_Profile
{
   [Key]   
   public int user_id {get;set;}

   public string firstname {get;set;}
   public string lastname {get;set;}
}

user_id is a PK in both SQL Server's User and User_Profile tables. It is also set as an Identity column in the User table.

When I try to insert a new record via the EFDBContext Add/SaveChanges. I get the following error: "user_id cannot be NULL in the User_Profile table" This makes perfect sense as this is a PK column. I was hoping EF would be able to take the Identity user_id from Users and Insert it into User_Profile user_id when saving.

Is this possible and if so how would I implement that?

UPDATE: Please note that I manually created the DB tables and code classes so I dont have access to StoreGeneratedPattern via the .edmx file.

score 7 · Accepted Answer · answered Oct 28 '11 at 21:06

I think it is necessary to configure your one-to-one relationship explicitely using Fluent API:

public class MyContext : DbContext
{
    // ... your DbSets

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasRequired(u => u.user_profile)
            .WithRequiredPrincipal();
    }
}

And the entity classes:

public class User
{
    [Key]   
    public int user_id {get;set;}
    public string username {get;set;}
    public string email {get;set;}
    public virtual User_Profile user_profile {get;set;}
}

public class User_Profile
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int user_id {get;set;}
    public string firstname {get;set;}
    public string lastname {get;set;}
}

It's necessary to switch off DatabaseGeneratedOption from Identity to None in one of the classes. I have swapped principal and dependent as it seems that the User should be the principal. The [ForeignKey(...)] is not necessary because EF will recognize user_id in User_Profile as a FK property to User.

A code like this...

using (var context = new MyContext())
{
    var user = new User();
    var userProfile = new User_Profile();

    user.user_profile = userProfile;

    context.Users.Add(user);
    context.SaveChanges();
}

...should work then as you expect and save both related entities to the database.

Wow! That worked EXACTLY as you said it would. Many Thanks Slauma! — chrisg229, Oct 28 '11 at 21:21

score 0 · Answer 2 · edited May 23 '17 at 12:17

0

You have to set the StoreGeneratedPattern attribute to Identity in your .edmx file to let the framework know the field is generated by the database. This link might help...

Autonumber with Entity Framework

edited May 23 '17 at 12:17

Community

1
1

answered Oct 28 '11 at 20:22

Saad Imran.

4,480
2
23
33

1

I just did a search and there is no .edmx file in my app directory. I am using EF4. Am I missing something? – chrisg229 Oct 28 '11 at 20:40
Umm, it should be there. The .edmx file is basically the model you generated, so look for a file with that name. Or you can just right click the model in your solution explorer and open it with the xml editor. – Saad Imran. Oct 28 '11 at 20:43
I cant find any .edmx file and the closest thing I have to a model are the class.cs files I created. Please note that I did manually create the DB table and classes (making the property/column names the same), then I just instantiated the DBContext and make the proper connection in the web.config. Everything seem to work great ...except the identity issue above. – chrisg229 Oct 28 '11 at 20:53
Ahh I thought you at least used designer to generate the mappings. Unfortunately I don't know of any other way. If you can't seem to figure it out, that's always an option. Generate a model, import your tables and turn off code generation so you can still use your entity/context classes. – Saad Imran. Oct 28 '11 at 20:59
I guess I might have to resort to that. Thanks for your help Saad! – chrisg229 Oct 28 '11 at 21:05

Entity Framework Foreign Key Inserts with Auto-Id

2 Answers2

Linked