galaf galaf - 2 months ago 11
C# Question

One-to-one Entity Framework relationship

In my data model (Entity Framework 6.1.3, ASP.NET MVC 5, Code First with an existing database) there a two tables, “Person” and “User” that share a one-to-one relationship. “User” table has as PK the PersonID column which is in turn the “Person” table's PK. I’d like whenever a new User record is created, a Person record be (first) automatically created and then the value of PersonID in Person table get inserted into the new User record.

This is the model code for Person table:

[Table("Person")]
public partial class Person
{
public int PersonID { get; set; }

public virtual User User { get; set; }
}


This is the model code for User table:

[Table("User")]
public partial class User
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int PersonID { get; set; }

[Required]
[StringLength(20)]
public string Name { get; set; }

public virtual Person Person { get; set; }
}


The UserController.cs code includes:

// POST: User/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "PersonID,Name")] User user)
{
if (ModelState.IsValid)
{
db.Users.Add(user);
db.SaveChanges();
return RedirectToAction("Index");
}

ViewBag.PersonID = new SelectList(db.People, "PersonID", "PersonID", user.PersonID);
return View(user);
}


The SQL for the Person table:

CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
CONSTRAINT [PK_Person_PersonID] PRIMARY KEY CLUSTERED)


The SQL for the User table:

CREATE TABLE [dbo].[User](
[PersonID] [int] NOT NULL,
[Name] [nvarchar](20) NOT NULL
CONSTRAINT [PK_User_PersonID] PRIMARY KEY CLUSTERED)

ALTER TABLE [dbo].[User] WITH CHECK ADD CONSTRAINT [FK_User_Person_PersonID] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Person_PersonID]
GO


Thanks in advance, galaf.

Answer

Your database structure should look like this

  1. Table User: ( PersonID int (PK, FK), Name nvarchar(20))
  2. Table Person (PersonID int (PK , identity), ...)

To translate the above db structure to entity framework configuration

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // Configure PersonID as PK for User 
    modelBuilder.Entity<User>()
        .HasKey(e => e.PersonID);

    // Configure PersonID as FK for User
    modelBuilder.Entity<Person>()
                .HasRequired(p => p.User) 
                .WithRequired(u => u.Person); 

}

the above configuration (1-1 relationship) will through an error if you try to save Person alone without User and vice versa.

Note: I recommend to build a (1-0..1 relationship) since logically the User is strictly depending on the person ( weak entity) while Person can exists alone without having a user. the configuration of 1:0..1 is very similar to the above configuration, the only difference is by making .HasOptional(p=>p.User) instead of .HasRequired(p=>p.User)

Now when you create the User entity, your code should be like this

var user = new User(){
   Name = "UserName",
   Person  = new Person(){
       FirstName="FirstName",
       LastName ="LastName",
       ...
   }
};

db.Users.Add(user);
db.SaveChanges();

The above code will enforce the creation of the person object whenever you create the user object.

UPDATED : for your case:

I recommend that you create a DTO object to manage the CRUD for both objects ( User and Person) as follows

public class UserDTO 
{
    public int Id { get; set;}
    public string UserName { get;set;}
    public string Password { get;set;}
    public string FirstName { get; set;}
    public string LastName { get; set;}
    // ... any other required properties goes here ... //
}

your UserController.cs code can handle both the create and edit in one action as follows, which will allow you to code less as long as both view are the same.

[HttpGet]
public ActionResult CreateOrEdit(int? id)
{
     UserDTO user = null;
     if( id!=null)
         user = db.Users.Select(t=> new UserDTO {
             UserName = t.UserName,
             FirstName = t.Person.FirstName,
             LastName = t.Person.LastName,
             Id = t.Id
             // ... //
         }).FirstOrDefault();
     else
         user = new UserDTO();
     return View(user);
}

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult CreateOrEdit(UserDTO model)
{
      if(model!=null && ModelState.IsValid)
      {
           if(model.Id>0) // Edit
           {
               User  user = db.Users.Include(p=>p.Person).Single(t=>t.Id == model.Id);
               user.UserName = model.UserName;
               user.Person.FirstName = model.FirstName;
               user.Person.LastName = model.LastName;
           }
           else // Add
           {
               User user = new User
               {
                   UserName = model.UserName,
                   Password = model.Password, // should be encrypted first
                   Person = new Person{
                      FirstName = model.FirstName,
                      LastName = model.LastName
                   }
               };
               db.Users.Add(user);
           }
           db.SaveChanges();
           return RedirectoToAction("TargetActionGoesHere");
      }
      return View(model);
}

your View CreateOrEdit.cshtml should look like , I will not add html code and labels just for making it simple

@model UserDTO

@using(Html.BeginForm())
{
       @Html.AntiForgeryToken()
       @Html.ValidationSummary(true)
       @* To store the value of Id in hidden field*@
       @Html.HiddenFor(m=>m.Id) 
       @Html.EditorFor(m=>m.FirstName)
       @Html.EditorFor(m=>m.LastName)
       @Html.EditorFor(m=>m.UserName)
       @if(Model.Id ==0 ) 
       { 
           // only in the add case you need to get the password value
           @Html.EditorFor(m=>m.Password)
       } 
}

Hope this will help you