Entity Framework interview questions

Entity Framework is Microsoft’s latest and recommended way for data access for new applications. EF 4.1 enhances the offering by adding support for Code First model and DbContext API’s. The post assumes basic knowledge of Entity Framework. If you are not familiar with EF, I would recommend that you review the basics @ http://msdn.microsoft.com/en-us/data/ef. If you are interested in Code First approach, a very good tutorial that I would recommend is http://blogs.msdn.com/b/adonet/archive/2011/03/15/ef-4-1-code-first-walkthrough.aspx.

In this post we will explore 10 interview questions on Code First Data Annotations.

Question 1: Design a code first data model which has a Project class that can contain a bunch of tasks.

For our discussion, we will assume that we are using the Code First model and that our model is made up of the following 2 classes:

using System;
using System.Collections.Generic;

namespace EFDemo.Model
{
    // Code first relies on a programming pattern 
    // referred to as convention over configuration. 
    // What this means is that if you want to use code first, 
    // your classes need to follow the certain conventions 
    // while defining the schema. 
    // This allows EF to infer the schema that it needs to 
    // create to get the job done.
    public class Project
    {
        // Code First infers this as the primary key column
        public int Id { get; set; }
        // this becomes a nullable column
        public string Name { get; set; }
        public string Description { get; set; }

        // list of tasks for a project
        public virtual List<Task> Tasks { get; set; }
    }

    public class Task
    {
        // Code First infers this as the primary key column
        public int TaskId { get; set; }
        public string Name { get; set; }

        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }

        // this is inferred as Foreign key to project table
        public int ProjectId { get; set; }
    }
     
}

This model produces the following database. I have highlighted the relevant items that I would like you to understand before we proceed further.

Now let’s review a few simple entity framework interview questions.

Question 2: Using Code First model, how can I mark a field/property as the primary key if it does not follow the code first convention?

In our case above, EF looks for the word “ID” with a combination with the entity name (e.g. Project) to determine both the EntityKey and the primary key. If we rename the “Id” to say “UniqueProjectIdentifier”, we will need to decorate that property with the KeyAttribute ([Key]) to make it all work.

In the code below, we redefined our primary key but did not provide any data annotations.

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a nullable column
    public string Name { get; set; }
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }
}

This produces the following error:

The fix is simple. Just add the [Key] attribute as shown below.

 

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a nullable column
    public string Name { get; set; }
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }
}

Question 3: When you have a annotate a property as Primary key in a table, how do you enable foreign key relationship from another table?

Although this “fix” solved the primary key issue for the Project class, it failed to infer our Foreign Key relationship in the Task class. It actually created a new FK and ignored our ProjectId key.

Now that we have a custom primary key, we also have to annotate a foreign key for the Task table. The solution is to define a navigation property for Task and annotate it to mark the ProjectId property as the FK.

public class Task
{
    // Code First infers this as the primary key column
    public int TaskId { get; set; }
    public string Name { get; set; }

    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    // this is inferred as Foreign key to project table
    public int ProjectId { get; set; }

    // explicitly define the FK
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }
}

Question 4: How do you mark a property as required? For example, For a Project, the Name is a required field.

You use the [Required] attribute to mark a property as required.

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a non-nullable column
    [Required]
    public string Name { get; set; }
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }
}

Question 5: How do you enforce a field to have a minimum and maximum number of characters? For example, the Description on a Project should be a minimu of 10 and a maximum of 500?

EF provides us with convenient property annotations of MinLength and maxLength.

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a non-nullable column
    [Required]
    public string Name { get; set; }
    [MaxLength(500, ErrorMessage="Maximum of 500 characters please")]
    [MinLength(10, ErrorMessage="Minimum of 10 characters required")]
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }
}

After the 2 changes described above, our database looks like:

Question 6: Define a property in project class named ProjectCode that is not mapped to the database. ProjectCode is internally calculated as a combination of project ID and Title.

Normally, in code first convention, all properties are mapped to the database. If we want to exclude a specific property (generally a computed property), we can annotate it with [NotMapped] attribute.

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a non-nullable column
    [Required]
    public string Name { get; set; }
    [MaxLength(500, ErrorMessage="Maximum of 500 characters please")]
    [MinLength(10, ErrorMessage="Minimum of 10 characters required")]
    public string Description { get; set; }

    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }

    [NotMapped]
    public string ProjectCode
    {
        get
        {
            return UniqueProjectIdentifier + Name;
        }
    }
}

Question 7: If your domain entities are defined using a set of classes, how can you combine them in EF to form one complete entity?

Let us assume that our Project class has another class called ProjectDetails which has date created and the description field. Using normal EF code first data model, EF will create 3 tables. But we want to tell EF to create only 2 tables (Project and task). To achieve this we will use the [ComplexType] annotation on the Project Details as shown below:

public class Project
{
    // Code First has to be told that 
    // this as the primary key column
    [Key]
    public int UniqueProjectIdentifier { get; set; }
    // this becomes a non-nullable column
    [Required]
    public string Name { get; set; }
    // references the complex type as part of the 
    // project object in the database
    public ProjectDetails Details { get; set; }
    // list of tasks for a project
    public virtual List<Task> Tasks { get; set; }

    [NotMapped]
    public string ProjectCode
    {
        get { return UniqueProjectIdentifier + Name;}
    }
}

[ComplexType]
public class ProjectDetails
{
    public DateTime? DateCreated { get; set; }
    [MaxLength(500, ErrorMessage = "Maximum of 500 characters please")]
    [MinLength(10, ErrorMessage = "Minimum of 10 characters required")]
    public string Description { get; set; }
}

This results in the following database schema:

The calling code also needs to be changed:

class Program
{
    static void Main(string[] args)
    {

        Database.SetInitializer<EFDemoContext>(new DropCreateDatabaseIfModelChanges<EFDemoContext>());

        using (var ctx = new EFDemoContext())
        {
            Project p = new Project()
            {
                Name = "Project 1",
                Details = new ProjectDetails()
                {
                    DateCreated = DateTime.Now,
                    Description = "some project description"
                }
            };

            ctx.Projects.Add(p);
            ctx.SaveChanges();
        }
    }
}

Question 8: How can you tell EF to have a different table or column name than that defined for the class?

By convention, EF defines the table and column names based on your class and property names. You can use the [Table] and [Column] annotations to tell EF to use different names.

[Table("ProjectItems")]
public class Task
{
    // Code First infers this as the primary key column
    public int TaskId { get; set; }
    public string Name { get; set; }

    [Column("CreationDate")]
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    // this is inferred as Foreign key to project table
    public int ProjectId { get; set; }

    // explicitly define the FK
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }
}

This causes EF to create a data model where the Tasks table is represented by Projectitems and the StartDate column to be named as CreationDate.

Question 9: For a datetime property, how can you tell EF to automatically compute and insert the current date time when the row is created?

In our Project tasks, we want to automatically set the creation date when a new row is inserted. We can achieve this by telling EF that this property is a [DatabaseGenerated] property and that it is computed.

[Table("ProjectItems")]
public class Task
{
    // Code First infers this as the primary key column
    public int TaskId { get; set; }
    public string Name { get; set; }

    [Column("CreationDate")]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    // this is inferred as Foreign key to project table
    public int ProjectId { get; set; }

    // explicitly define the FK
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }
}

This code will throw an exception. Why? Because code first won't be able to determine the formula for the computed column. To solve this, you should only use this when pointing to existing databases OR use the [TimeStamp] column.

Another use of this attribute is when you do NOT want your primary key to be an auto incremented.

Question 10: When two tables have multiple relationships (for example, a task is created by employee 1 and updated by employee 2), who do you indicate which relationships go with which property?

Entity Framework provides us with [InverseProperty] attribute to indicate multiple relationships between two tables. Consider the following code first model where the Task class now has 2 pointers to Employee for CreatedBy and UpdatedBy. Also we have added an Employee class which has a list of tasks created and updated. NOTE that we have not (yet) added any data annotations to signify any inverse relationships. The goal is to show you how EF will not be able to recognize this.

[Table("ProjectItems")]
public class Task
{
    // Code First infers this as the primary key column
    public int TaskId { get; set; }
    public string Name { get; set; }

    [Column("CreationDate")]
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    // this is inferred as Foreign key to project table
    public int ProjectId { get; set; }

    // explicitly define the FK
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }

    public Employee CreatedBy { get; set; }
    public Employee UpdatedBy { get; set; }
}

public class Employee
{
    public int Id { get; set; }
    public string name { get; set; }

    public List<Task> TasksCreated { get; set; }
    public List<Task> TasksUpdated { get; set; }
}

The database generated on running this model is shown below:

As you can see above, the Tasks (ProjectItems) table is not what you really expected. Let’s fix this by using the [Inverseproprty] attribute.

public class Employee
{
    public int Id { get; set; }
    public string name { get; set; }

    [InverseProperty("CreatedBy")]
    public List<Task> TasksCreated { get; set; }
    [InverseProperty("UpdatedBy")]
    public List<Task> TasksUpdated { get; set; }
}

Now that we let EF now the relationships, it is able to figure it out and generates the following database for us:

I hope that post has been helpful to you to understand a few of the data annotations provided by Entity framework.