Please navigate to the bottom of the page for Table of Contents

Tuesday, July 19, 2011

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.

image



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:

image


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.

image

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:

image

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:

clip_image001[4]

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.

clip_image001[6]

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:

clip_image001[8]

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:

clip_image001[10]

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

51 comments:

  1. Excellent. To add descriptions to the fields is done in System.ComponentModel.Description? Summary and longDescription?

    ReplyDelete
  2. Nice concise article.

    ReplyDelete
  3. i really appreciate your effort to write these question in such a simple way that every one can quickly understand. great work

    ReplyDelete
  4. Simply superb..!
    Makes it easy with simple text & screen shots.. Gr8 job..

    ReplyDelete
  5. Great work. This is first time i have learn the EF. But I quickly understand the thing.

    ReplyDelete
  6. Nikhil! your geniousity(missing from the dic.) is much appreciated.

    ReplyDelete
  7. Hello Nikhil,

    You are rocking... Keep it good work...

    ReplyDelete
  8. Nikhil,

    Thanks a lot for your effort .Helped me a lot..

    ReplyDelete
  9. Very Good Article to better understand EF...Keep ur work on...

    ReplyDelete
  10. good article bingooo

    ReplyDelete
  11. Very nice article to understand basics for EF development

    ReplyDelete
  12. Samir,

    Nice artical to easly understand Code First Entity Framework Developement

    ReplyDelete
  13. You must start writing an EF book.

    ReplyDelete
  14. Very Good .. thank you.

    ReplyDelete
  15. this very use full for me.
    i really appreciate your effort to write these question in such a simple way that every one can quickly understand. great work

    ReplyDelete
  16. great introduction to the beginers

    ReplyDelete
  17. Its very helpful.Thank you so much.

    ReplyDelete
  18. very nice n easy to understand article...Thanks.

    ReplyDelete
  19. It is very help full.

    And To catch EntityValidationErrors , we should use like this

    try {} // instead of using

    catch (DbEntityValidationException e)
    {
    foreach (var eve in e.EntityValidationErrors)
    {
    Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
    eve.Entry.Entity.GetType().Name, eve.Entry.State);
    foreach (var ve in eve.ValidationErrors)
    {
    Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
    ve.PropertyName, ve.ErrorMessage);
    }
    }

    }

    ReplyDelete
  20. Very easy to understand. Good article

    ReplyDelete
  21. Nice..short and very very helpful

    ReplyDelete
  22. Short and Simple :)..

    ReplyDelete
  23. No word to say Thanks Grt...Dear

    ReplyDelete
  24. Really its a very informative article on Entity Framework, it is very helpful to us. Awesome work.. Thanks...

    ReplyDelete
  25. Nice article for beginners..

    ReplyDelete
  26. "Hello There,


    What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.


    hi i have code which query value from database,the use case is the user can enter value as 1,01,11 in database but when the user enter value in xml file he can only enter 11,01,12 in database there is two columns lets say column test1=1 and test2=2 combination of this is 12,which is the value the use will enter in xml,but sometime the use can enter test1=04 than column test2=00 how can i approch this







    I look forward to see your next updates.


    Merci Beaucoup,
    Irene Hynes
    "

    ReplyDelete
  27. Nice

    Jaganath Rao Niku
    Niku Software
    NikuHosting.com




    ReplyDelete
  28. This comment has been removed by the author.

    ReplyDelete
  29. Thanks for the great post! Your QAs have been added to the www.fullstack.cafe portal and back-linked!

    ReplyDelete
  30. The Schengen Area is a group of 26 European countries that have abolished passport and immigration controls at their common borders and have a common visa policy. It functions almost like a single country for international travel purposes, which, by allowing participating countries to add their own specific provisions regarding types of residence and local visas, ensures that certain types of visas are universal and that the person holding a residence permit is in the same a Schengen country does not require a visa to visit or transit through another Schengen country - in that sense it is very close to the general immigration policy of the European Union (the United Kingdom and Ireland are part of their own separate agreement called (and therefore do not operate there Schengen rules even if they are both part of the European Union (it should also be noted that the Schengen area also includes non-EU countries). https://www.immigration-residency.eu/immigration-to/

    ReplyDelete
  31. The complete blogs are really inconceivable and definitely everyone will share this information.
    design firms Bay Area

    ReplyDelete