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.
