How to insert new records using Entity Framework

In my previous post about using ORM tools, I talked about how to add records to table using Linq to Sql. Recently Microsoft released new version 4.2 of Entity Framework. In this post I will discuss how we can add new records in the database using EF 4.2. In this discussion I will also talk about some of the subtle issues that arise when your table names have some pluralization issues.

Code First Approach

In this post I will be using Code First approach. In this approach you will create your model class first. And then we this class will get mapped to table in the database. You can use this approach to create new table in the database as well. Since I already have my tables creates in database, I am going to concentrate on showing insertion of record to that table.

Create Data Model Class

I will start by creating class representing the object(s) that I want to manipulate in my database. I have create Member class that corresponds to Member table in database.

public class Member
{
   public int MemberId { get; set; }
   public string MemberFirstName { get; set; }
   public string MemberLastName { get; set; }
   public string MemberDisplayName { get; set; }
   public DateTime CreateDate { get; set; }
   public bool Active { get; set; }
   public bool Deleted { get; set; }
   public decimal ? BillingRate { get; set; }
   public int ? Availability { get; set; }
   public int CompanyId { get; set; }
}

In subsequent posts I am going to refine this class to add some related objects to show concept of lazy loading. But for this post, this class definition will suffice. Each property in this class corresponds to fields in database table.

Create Data Context

Next I will create the work horse of entity framework, a class that provides data context for all the operations.

using System.Data.Entity;

public class PMDataContext : DbContext
{
   public PMDataContext() : base() { }
   public PMDataContext(string conn) : base(conn) { }
   public DbSet<Member> Members { get; set; }
}

The class is derived from DbContext class. And you will have to add using statement for System.Data.Entity namespace to work with DbContext class. So far so good and it look very much list what we did for use of Linq To Sql. The difference is that these classes look leaner as compared to what Linq to Sql class wizard generated.

Execute Operation To Add New Record

The operation to add new record using Entity Framework is same as to what I did for Linq to Sql. The difference is that method name is slightly different. In EF you will call SaveChanges. Following code snippet is from web application that I am working on at the moment that uses EF. This code adds new Member record in the database.

protected void OnAddMember(object sender, EventArgs e)
{
   if (!ValidateInput())
   {
      ErrorMessage_Label.Visible = true;
      return;
   }

   decimal billRate = 0.0M;
   if (!string.IsNullOrEmpty(BillRate_TextBox.Text.Trim()))
   {
      decimal.TryParse(BillRate_TextBox.Text.Trim(), out billRate);
   }
   var dataContext = new PMDataContext(Global.ConnectionString);
   var member = new Member()
   {
     MemberFirstName = AntiXss.HtmlEncode(FirstName_TextBox.Text.Trim()),
     MemberLastName = AntiXss.HtmlEncode(LastName_TextBox.Text.Trim()),
     MemberDisplayName = AntiXss.HtmlEncode(DisplayName_TextBox.Text.Trim()),
     Active = true,
     Deleted = false,
     CreateDate = DateTime.UtcNow,
     CompanyId = _thisCompany.CompanyId,
     BillingRate = billRate,
     Availability = Convert.ToInt32(Availability_Dropdown.SelectedValue)
   };

   dataContext.Members.Add(member);
   try
   {
      dataContext.SaveChanges();
      Response.Redirect(_returnUrl);
   }
   catch (Exception ex)
   {
     // TODO: Log the exception.
     System.Diagnostics.Debug.WriteLine(ex.Message);
   }
}

As you can see the code is pretty straight forward. But when I executed SaveChanges statement, I got the following exception message thrown at me.

Invalid object name dbo.Members

The message suggests that Entity Framework thinks that I have a table with Members where I am trying to add records. The problem is that EF is designed to match collection property name to table in database. Since my property is Members that contains all records, so EF maps it to table name Members. This is because of Pluralization convention in entity framework. To fix this problem, you will need to remove that convention from your DbContext. So I had to modify the class as shown below.

public class PMDataContext : DbContext
{
 public PMDataContext() : base() { }
 public PMDataContext(string conn) : base(conn) { }
 public DbSet<Member> Members { get; set; }

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
   base.OnModelCreating(modelBuilder);
   modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
 }
}

After this change, SaveChanges executes without any problem. As you can see, use of Entity Framework 4.2 is pretty straight forward to add new records to the database.