Entity Framework, MVC 3, Technical

Entity Framework – Plural and Singular Table names

By default, the Entity Framework will assume that all of the names of your tables in your database are either pluralised, or in the case of code first, you would like them to be pluralised when created.

E.g. you have a table called “Product” and not “Products”, or you want your table to be called “Product” and not “Products”

This is the problem that I had. My MVC application consisted of one web page that just dumped out the contents of the “Product” table onto the page. When I browsed to the page, I got an “Invalid object name ‘dbo.Products’.” yellow screen of death runtime error.

The Solutions

1. Rename the table to “Products”. I didn’t want to do this as I’m from the school of singular table names. I was also curious about situations where the tables couldn’t be renamed.

2. Make use of Entity Framework’s fantastic Conventions, that allow you to specify how you have or want your database to be setup.

To tell Entity Framework not to pluralise database table names, simply add the following code into your DbContext class:

public class EfDbContext : DbContext
 {
  public DbSet<Product> Products { get; set; }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
  }
 }

This code will remove the Pluralising convention that is by default attached to all model builders. You will then be able to access database tables with Singular names.

Links

Table Naming Dilemma: Singular vs. Plural Names (StackOverflow)

PluralizingTableNameConvention Class (MSDN)

MVC 3, Technical

Using ASP.net MVC remote validation in the real world – AdditionalFields

Implementing remove validation in MVC 3 is very trivial. Typical examples found on the web after a quick search will usually demonstrate the remove validation required when creating a new user. The typical “Is my user name available” check.

However, on putting together a real world MVC 3 application that made full use of models that could be re-used and partials, I hit a pretty obvious issue. In this application, we were using the same model for creating new users and updating existing users. Why wouldn’t we? The model represents the same entity, and will need the same validation. This was a perfect fit and was ideal until we decided to put some remote validation in against the email address field.

Our email address property in our model, after initially adding remote validation, looked something like this:

        [DisplayName("Email Address")]
        [Required]
        [RegularExpression("[\\w\\.-]+@[\\w-]+\\.(\\w{3}|\\w{2}\\.\\w{2})")]
        [Remote("IsUserNameAvailable", "UserManagement")]
        public string ContactEmailPrimary { get; set; }

This worked fantastically on the create user page. When the user entered an email address that was already registered, the client made an Ajax request to our following controller action, that checked if the email address was not already in the database:

[HttpGet]
        public virtual JsonResult IsUserNameAvailable(string contactEmailPrimary)
        {
                int results = 0;
                var searchResults = _userServiceGateway.SearchUserLogin(contactEmailPrimary);

                if (searchResults.Count > 0)
                    return Json("This Email Address has already been registered", JsonRequestBehavior.AllowGet);

                return Json(result, JsonRequestBehavior.AllowGet);
        }

Which if it was registered, would return false.

“Great” we thought. Feature complete – and it went off to the testers. Only for another bug to have come up. We now couldn’t update the user information of an existing user. This was because the remote validation was firing and checking that the email address of the existing users hadn’t already been registered – which, it obviously had. This resulted in our page failing client side validation, and the client being unable to save any changes to the entity.

So we needed a solution. We first explored the possibility of disabling the Remote Validation somehow for this one page. This proved fruitless. We then looked at possibly splitting the models used, and having one model for the User Create, and one model for the User Update. We resisted this idea for obvious reasons.

We then looked at the possibility of passing additional parameters to the Remote Validation action. Sure enough, we discovered the AdditionalFields attribute of the Remote validation object.

The AdditionalFields attribute allows you to specify the name of another form element that will appear within the same form. ASP.NET MVC3 remote validation will then pass the value of this additional attribute to your controller action that is performing the validation. So to fix our issue, we just altered the code to pass the AdditionalField of “UserAccountId”, which we knew would be greater than zero if the user entity was being edited and not created.

Here’s how our finished working code looked, making use of the AdditionalFields attribute:

Model:

        [DisplayName("Email Address")]
        [Required]
        [RegularExpression("[\\w\\.-]+@[\\w-]+\\.(\\w{3}|\\w{2}\\.\\w{2})")]
        [Remote("IsUserNameAvailable", "UserManagement", AdditionalFields = "UserAccountId")]
        public string ContactEmailPrimary { get; set; }
        public int UserAccountId { get; set; }

View:

</pre>
<div class="columns">
 @Html.LabelFor(model => model.ContactEmailPrimary)
 <span class="relative">
 @Html.TextBoxFor(model => model.ContactEmailPrimary)
 @Html.HiddenFor(model => model.UserAccountId)

 </span></div>
<pre>

And finally our controller, which now gets an AdditionalField of “UserAccountId”:

[HttpGet]
        public virtual JsonResult IsUserNameAvailable(string contactEmailPrimary, int userAccountId)
        {
            if (userAccountId == 0)
            {
                int results = 0;
                var searchResults = _userServiceGateway.SearchUserLogin(contactEmailPrimary);
                bool result = true;

                //We need to check for an exact match here as the search only does a "like"
                foreach (var userAccountDto in searchResults)
                {
                    if (userAccountDto.UserLogin == contactEmailPrimary)
                    {
                        result = false;
                        break;
                    }
                }
                if (!result)
                    return Json("This Email Address has already been registered", JsonRequestBehavior.AllowGet);

                return Json(result, JsonRequestBehavior.AllowGet);
            }
            return Json(true, JsonRequestBehavior.AllowGet);
}

We were now back to create and update functionality working securely.

Recommended Reading:

Pro ASP.NET MVC 3 Framework