Implementing .NET MVC applications with Entity Framework Code-First Nice and Easy - Part 1

Introduction

The purpose of the current post is to show a nice and easy way to implement quick solutions using MVC and Entity Framework while keeping the code clean, having database accessibility not tied to objects, and still taking advantages of powerful features of MVC and Entity Framework together.

For this, we will first create a MVC 4 web site, a separate project for POCO classes, and a last but not less important project which will contain the database access related classes.

The best approach I have found when using code-first is to design the database model before you start coding, basically a good database design helps a lot when using code-first, it will save a lot of time spent trying to figure out why weird issues happen, it is not that entity framework will not work with poorly designed databases, but it will certainly be difficult if you are starting with it or you haven't faced these situations in the past.

For the database, we will use a personal Real Estate project database example, it does not have the best of designs, but it is a start.

We will start with the GenericProperty and ResidentialProperty tables.

Most important things to note in the pictures above are the Primary Keys and Foreign Keys. Once we have our database design, we can process to create our classes that will be mapped to the database. The approach I like to use the most is called Table Per Type, where you basically have one class per database table. Let's start with Generic Property.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PTIPortal.DA.Models
{
    [Table("GenericProperty")]
    public partial class GenericProperty
    {
        public GenericProperty()
        {
            this.PropertyPhotoes = new List<PropertyPhoto>();
            this.Amenities = new List<PropertyAmenity>();
        }

        [Key]
        [Display(Name="ID")]
        public long PropertyId { get; set; }
        [Display(Name = "Type")]
        public int PropertyTypeId { get; set; }
        public System.Guid PropertyUniqueId { get; set; }
        [Display(Name = "City")]
        public long CityId { get; set; }
        public double LatitudeDecimal { get; set; }
        public double LongitudeDecimal { get; set; }
        [Display(Name="Size")]
        public double LotSize { get; set; }
        [Display(Name = "Units")]
        public int LotsizeUnitOfMeasureId { get; set; }
        [Display(Name = "Owner")]
        public long OwnerInfoId { get; set; }
        [Display(Name = "Description")]
        public string Description { get; set; }
        [Display(Name = "Status")]
        public int WorkflowStepId { get; set; }
        [Display(Name = "Hidden")]
        public bool IsHidden { get; set; }
        public virtual OwnerInfo OwnerInfo { get; set; }
        public virtual PropertyType PropertyType { get; set; }
        [ForeignKey("LotsizeUnitOfMeasureId")]
        public virtual UnitOfMeasure UnitOfMeasure { get; set; }
        [ForeignKey("WorkflowStepId")]
        public virtual WorkflowStatu WorkflowStatu { get; set; }
        public virtual ICollection<PropertyPhoto> PropertyPhotoes { get; set; }
        [Association("PropertyAmenity", "PropertyId", "AmenityId")]
        public virtual ICollection<PropertyAmenity> Amenities { get; set; }
        [ForeignKey("CityId")]
        public virtual City PropertyCity { get; set; }

        [NotMapped]
        public int[] AmenitiesId { get; set; }

        [NotMapped]
        [Display(Name = "Location")]
        public string Location 
        {
            get
            {
                return string.Format("{0}, {1}, {2}",
                    this.PropertyCity.CityName, 
                this.PropertyCity.Province_State.ProvinceState_Name,
                this.PropertyCity.Province_State.Country1.CountryName);
            }
        }
    }
}

Code-First by default maps properties to database columns by naming convention, this means, it will try to find a column with the same named as the property and map the data, this behavior can and must be overridden in some scenarios. If you have a property with another name and you need to map it to a specific column, you can just decorate the property with [Column("PropertyNameGoesHere")] attribute, and the magic will happen.

The property ID, it has some Data Annotations above it. Key is used to specify properties that will identify an entity, which in most situations should be our primary key(s).

It also has a Display attribute, this is used in MVC indicating that when printing the label name for the property, it should display what is in the string, in this case ID.

You can do something better and retrieve the value from a resource file like this:

[Display(Name="GenericPropertyId", ResourceType=typeof(PTIPortal.BO.Messages))]

and with just one line of code, you will have your property label set up for localization.

Let's see the WorkflowStatus property. It is a navigation property, which allow us to navigate to the related records from the current object. Navigation properties are declared with virtual, if you had a 1 to many or many to many relationship, you would use ICollection<T> instead, the ForeignKey attribute indicates the property the current class to be used in order to do the join with the primary of the related object.

The properties marked with NotMapped are basically additional properties added to the model that do not have any relation to database columns. It is required to mark them as NotMapped, because otherwise, they will go in the auto generated SQL statements, and will be invalid for the database structure.

In this approach, I am using those properties in MVC side.

The constructor initializes List properties. This is just basically so code does not fail with an object reference not set error when you invoke .Add for a photo or amenity related to generic property.

ResidentialProperty - Inheritance

With code-first, you can still use inheritance, however you need to follow some rules such as having primary keys for "parent" and "child" table to have the same name in the database.

Basically, it will look for the key property from the parent class and the generated SQL will do the join with that column name on both sides.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace PTIPortal.DA.Models
{
    [Table("ResidentialProperty")]
    public partial class ResidentialProperty : GenericProperty
    {
        //public long PropertyId { get; set; }
        [Display(Name = "Listing Type")]
        public int ListingTypeId { get; set; }
        [Display(Name = "Price")]
        public decimal Price { get; set; }
        [Display(Name = "currency")]
        public int PriceCurrencyId { get; set; }
        [Display(Name = "Full Baths")]
        public int FullBaths { get; set; }
        [Display(Name = "Half Baths")]
        public int HalfBaths { get; set; }
        [Display(Name = "Bedrooms")]
        public int Bedrooms { get; set; }
        [Display(Name = "Parking Spaces")]
        public int GarageCarCount { get; set; }
        [Display(Name = "Floors/Stories")]
        public int Floors { get; set; }
        [Display(Name = "Residential")]
        public string ResidentialName { get; set; }
        [Display(Name = "Additional Information")]
        public string OtherInfo { get; set; }
        [ForeignKey("PriceCurrencyId")]
        public virtual Currency Currency { get; set; }
        //public virtual GenericProperty GenericProperty { get; set; }
        public virtual ListingType ListingType { get; set; }
    }
}

Let the Fun Begin - Creating the Context

You can see the context as the connection of our objects to the database.

The context will indeed handle things such as database connections, exceptions, sending messages, validating data, among others.

 public partial class PTIDBContext : DbContext
    {
        static PTIDBContext()
        {
            Database.SetInitializer<PTIDBContext>(null);
        }
        public PTIDBContext()
            : base("Name=PTIDBContext")
        {
        }
 public DbSet<GenericProperty> GenericProperties { get; set; }
 public DbSet<ResidentialProperty> ResidentialProperties { get; set; }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
}

The line base("Name=PTIDBContext"), basically invokes base constructor and tells it to find a connectionstring with the name after the =.

Then you create a DbSet for each of the tables you want to query. You can think of it as the table filled with data you can perform queries on.

The method OnModelCreating could be used to configure the model usually with Fluent-API. Most of the things you can do with Fluent-API can be done with Data Annotations, each approach has its advantages and disadvantages but that is out of the scope of this post.

When doing queries, code-first will by default use the DbSet name as the table name in the generated SQL.

So it would do a Select .... FROM GenericProperties and it will fail, we did avoid this when defining the classes, by decorating them with [Table("GenericProperty")] and [Table("ResidentialProperty")].

Once you have your classes, context, and DbSets ready, you can start working with it.

Just create a new instance of the context, access the DbSets and do linq queries on them.

I hope this has been useful for you.

Please feel free to leave a comment.

Thanks for reading!