What is a model?  A model is a programmatic representation of a concept or physical object that exists in real life.  We represent models in code as Entities in C# and as Tables in the Database.  Since these entities and tables represent the same underlying model, they need to remain synchronized.  In the past, generally the database was created first and then the entities were generated from it.  Recently, this paradigm has changed…

Now, we are using a code-first model creation paradigm in which the programmers write the C# Entities and from those entities the database is generated.  When working in this manner, there are best practices that will make the process more simple.

First, when creating the entities, adhere to the Database Design Standards set by Ty.  Even though this document was written for designing databases, it still applies.  Just substitute “Entity” for “Table” and “Field” for “Column” and you should have no problems.  In addition to this, make sure to make all fields virtual.

public virtual int UserRoleId { get; set; }

Next, for any foreign key field, add another field named and typed the same as the foreign key entity.  For example:

public virtual int UserRoleId { get; set; }
public virtual UserRole UserRole { get; set; }

This gets us most of the way to where we want to be, but sometimes we want certain behavior in table or column creation, in how Entity Framework treats the entity, or in data serialization.  Below are a list of common attributes and what they do:

Class level attributes:

• DataContract – Tells the data serializer that we are using an opt-in policy.  This means that only fields which are decorated with the DataMember attribute will be serialized.  This attribute is highly recommended as it gives you complete control over which fields are transmitted.
• Table – Specifies to the code-first model generator the table name that you wish to map this entity to.  This is useful if you want the table to be named something different than the entity and is not required in most cases.

Field level attributes:

• DataMember – Works with the DataContract attribute.  Used to specify that the field it decorates is meant to be serialized.
• Key – Identifies this field as the primary key for the database table it represents
• DatabaseGenerated – Indicates to the code first model generator and Entity Framework that this field will be updated by the database and not the user.  This can be applied to fields like primary keys and database generated dates (last update date) but use discretion as to when you apply this attribute.
• Required – Tells the Entity Framework validator that this field is required and allows you to specify an error message upon failed validation
• StringLength – Tells the entity validator that this field has a max and min range for string length and allows you to specify these ranges and an error message.
• Display – An intended display name for the field
• ForeignKey – Indicates that the field is a foreign key

Below is an example of a class which uses most of these attributes:

    [DataContract]
    public class User
    {
        /// The identifier for the user.  Also the primary key.
        [Key]
        [DataMember]
        public virtual Guid UserId { get; set; }

        /// Foreign key to the UserRole table/object.
        [Required]
        [DataMember]
        public virtual int UserRoleId { get; set; }

        /// UserRole object associated with this User.
        [ForeignKey( "UserRoleId" )]
        public virtual UserRole UserRole { get; set; }

        /// The login user ID for the user.
        [Required( ErrorMessage = "Login is required." )]
        [StringLength(100, ErrorMessage = "Login cannot be longer than 100 characters.")]
        [DataMember]
        public virtual string Login { get; set; }

        /// The hashed password for the user.
        [Required( ErrorMessage = "Password is required." )]
        [StringLength(40, ErrorMessage = "Password cannot be longer than 40 characters.")]
        [DataMember]
        public virtual string Password { get; set; }

        /// The driver's first name.
        [StringLength( 100, ErrorMessage = "First Name cannot be longer than 100 characters." )]
        [Display(Name = "First Name")]
        [DataMember]
        public virtual string FirstName { get; set; }

        /// The driver's last name
        [Required( ErrorMessage = "Last Name is required." )]
        [StringLength( 100, ErrorMessage = "Last Name cannot be longer than 100 characters." )]
        [Display(Name = "Last Name")]
        [DataMember]
        public virtual string LastName { get; set; }

        [DataMember]
        public virtual Guid BaseUserId { get; set; }

    }

The next step is to create a database context class.  This is how Entity Framework communicates with the database.  You need to add a field which corresponds to each table you want to create in the database from code. Each record in the database context is a line of communication between Entity Framework and the database for the specified table/entity.  After creating your database context, the first thing you should do is remove the convention of cascading deletes.  While this can save time in certain situations, it also allows for the opportunity to delete large amounts of data by accident.  A basic database context class looks like this:

public class DatabaseContext : DbContext
    {
        public  DatabaseContext()
            : base("name=DefaultConnection")
        {
        }

        // tables
        public DbSet Users { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        }

    }

This can optionally be generated by MVC scaffolding.

The last topic I have to discuss is, “How do I work with database views using Entity Framework and code-first?”  Currently, code first cannot create the view for you.  You need to create the view in the database itself, then create an entity that represents it.  You could probably add the creation script for the view to the migration created by code first, but that would be a topic for a different presentation.  To connect a view to an entity, the procedure is pretty much the same as with a table with the following exceptions:

• Create the view in the database.  Specify the view’s base table as the “primary key”.  For example, the primary key for a UsersView should be UserId.  While views technically do not have a primary key, this makes Entity Framework happy and generated code works better.
• Add the “Table” attribute to the entity’s class definition and specify the view name.  Otherwise the code-first model generator will automatically attempt to pluralize your view and it will end up pointing to the wrong object in the database.  Pluralization works well for table creation (Users) but not for views (UsersViews?)
• Use the add-migration command to create the migration script and then comment out the table creation code.  As stated above, you can optionally add code for view creation here at this time.

Below is an example of the class definition and primary key setup for a view:

    [DataContract]
    //This makes the database generator link to "HeatDriverYearsView".  Otherwise   
    //it would attempt to pluralize the class name and create a new table 
    //(HeatDriverYearsViews)
    [Table("HeatDriverYearsView")]
    public class HeatDriverYearsView
    {
        /// The identifier for the heat.  Also the primary key.
        [Key]
        [DataMember]
        //Even though views have no primary keys, this property will act like one.
        //There will only be one HeatDriverYearsView record per HeatId
        public virtual int HeatId { get; set; }

It is worth while to note that using views is one of a few ways to handle retrieving data from multiple tables.  Another way is to add commands to existing entities and use LINQ to perform any joins manually.  This has its own set of pros and cons so use your judgement as to which methodology to use.