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

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 avoid lot of time 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 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 overriden 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 WorkflowStatu 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, it initializes List properties. This is just basically so code do 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 beging - 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 contructor and tell 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 thing you can do with Fluent-API you can do it 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.

CodeProject