Entity Framework interview questions

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.

What is Entity Framework?

Entity Framework is an additional layer between application and database that enables the developers to program against the conceptual application model instead of programming directly against the relational storage schema.

Will there be any issues adding a table without primary keys to a data model?

Every entity must have a key, even in the case where the entity maps to a view. When you use the Entity Designer to create or update a model, the classes that are generated inherit from EntityObject, which requires EntityKey. So, we have to have a primary key in the table to add it to the data model.

How do you truncate a table using entity data model?

Unfortunately Entity Framework doesn’t include anything straight forward to handle this. But we can still call a T-SQL statement using entity framework that will still minimizes the developers work. We can call ExecuteStoreCommand() methond on ObjectContext as shown below.

using (var context = new MyTestDbEntities()) {     context.ExecuteStoreCommand("TRUNCATE table Dummy"); }

How do you query in entity model when the result has a join from from different database other than the entity model? E.g.: SELECT t1.c1, t2.c2 FROM table1 AS t1 JOIN table2 t2 ON t1.c1 = t2.c1

As the entity model doesn’t support querying from any entity other
than the entities defined in Entity Data Model, we have to query aginst the data base using ExecuteStoredQuery of the context.

Following code snippet shows how to query when other databases are joined.

string query = "SELECT t1.c1, t2.c2 FROM table1 AS t1 JOIN table2 t2 ON t1.c1 = t2.c1"; using (var context = new SampleEntities()) {   ObjectResult<DbDataRecord> records = context.ExecuteStoreQuery<DbDataRecord>(query);   foreach (DbDataRecord record in records)   {     //Do whatever you want   } }

What is minimum requirement for Entity Framework applications to run?

The Entity Framework is a component of the .NET Framework so Entity
Framework applications can run on any computer on which the .NET
Framework starting with version 3.5 SP1 is installed. 

What is CSDL?

Conceptual schema definition language (CSDL) is an XML-based language
that describes the entities, relationships, and functions that make up a conceptual model of a data-driven application. This conceptual model can be used by the Entity Framework or WCF Data Services.

The metadata that is described with CSDL is used by the Entity
Framework to map entities and relationships that are defined in a
conceptual model to a data source.
More=> http://msdn.microsoft.com/en-us/library/bb399292.aspx

What is SSDL?

Store schema definition language (SSDL) is an XML-based language that
describes the storage model of an Entity Framework application.

In an Entity Framework application, storage model metadata is loaded from a .ssdl file (written in SSDL) into an instance of the System.Data.Metadata.Edm.StoreItemCollection and is accessible by using methods in the System.Data.Metadata.Edm.MetadataWorkspace class. The Entity Framework uses storage model metadata to translate queries against the conceptual model to store-specific commands.
More=> http://msdn.microsoft.com/en-us/library/bb399559.aspx

What is MSL?

Mapping specification language (MSL) is an XML-based language that describes the mapping between the conceptual model and storage model of an Entity Framework application.

In an Entity Framework application, mapping metadata is loaded from an .msl file (written in MSL) at build time. The Entity Framework uses mapping metadata at runtime to translate queries against the conceptual model to store-specific commands.
More=> http://msdn.microsoft.com/en-us/library/bb399202.aspx

What is Entity Data Model?

The Entity Data Model (EDM) is a set of concepts that describe the structure of data, regardless of its stored form. The EDM borrows from the Entity-Relationship Model described by Peter Chen in 1976, but it also builds on the Entity-Relationship Model and extends its traditional uses.

The EDM addresses the challenges that arise from having data stored in many forms. For example, consider a business that stores data in relational databases, text files, XML files, spreadsheets, and reports. This presents significant challenges in data modeling, application design, and data access. When designing a data-oriented application, the challenge is to write efficient and maintainable code without sacrificing efficient data access, storage, and scalability. When data has a relational structure, data access, storage, and scalability are very efficient, but writing efficient and maintainable code becomes more difficult. When data has an object structure, the trade-offs are reversed: Writing efficient and maintainable code comes at the cost of efficient data access, storage, and scalability. Even if the right balance between these trade-offs can be found, new challenges arise when data is moved from one form to another. The Entity Data Model addresses these challenges by describing the structure of data in terms of entities and relationships that are independent of any storage schema. This makes the stored form of data irrelevant to application design and development. And, because entities and relationships describe the structure of data as it is used in an application (not its stored form), they can evolve as an application evolves.
More=> http://msdn.microsoft.com/en-us/library/ee382825.aspx

Which are the key concepts of Entity Data Model?

The Entity Data Model (EDM) uses three key concepts to describe the structure of data: entity type, association type, and property. These are the most important concepts in describing the structure of data in any implementation of the EDM.

1. Entity Type: The entity type is the fundamental building block for describing the structure of data with the Entity Data Model. In a conceptual model, entity types are constructed from properties and describe the structure of top-level concepts, such as a customers and orders in a business application.

2. Association Type: An association type (also called an association) is the fundamental building block for describing relationships in the Entity Data Model. In a conceptual model, an association represents a relationship between two entity types (such as Customer and Order).

3. Property: Entity types contain properties that define their structure and characteristics. For example, a Customer entity type may have properties such as CustomerId, Name, and Address.
More=> http://msdn.microsoft.com/en-us/library/ee382840.aspx

What is .edmx file and what it contains?

An .edmx file is an XML file that defines a conceptual model, a storage model, and the mapping between these models. An .edmx file also contains information that is used by the ADO.NET Entity Data Model Designer (Entity Designer) to render a model graphically.

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.

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.

What is use of EntityDataSource Control?

The ADO.NET EntityDataSource control supports data binding scenarios in Web applications that use the ADO.NET Entity Framework. Like the Entity Framework, the control is available as part of the .NET Framework 3.5, beginning with SP1. Like the other Web server data source controls, the EntityDataSource control manages create, read, update, and delete operations against a data source on behalf of data-bound controls on the same page. The EntityDataSource works with editable grids, forms with user-controlled sorting and filtering, dually bound drop-down list controls, and master-detail pages.
more=>http://msdn.microsoft.com/en-us/library/cc488502.aspx

What is Model First Approach?

A new Model First approach was supported in Visual Studio 2010, which was released together with the second Entity Framework version (Entity Framework v4). In Model First approach the development starts from scratch. At first, the conceptual model is created with Entity Data Model Designer, entities and relations are added to the model, but mapping is not created.

After this Generate Database Wizard is used to generate storage (SSDL) and mapping (MSL) parts from the conceptual part of the model and save them to the edmx file. Then the wizard generates DDL script for creating database (tables and foreign keys)

If the model was modified, the Generate Database Wizard should be used again to keep the model and the database consistent. In such case, the generated DDL script contains DROP statements for
tables, corresponding to old SSDL from the .edmx file, and CREATE statements for tables, corresponding to new SSDL, generated by the wizard from the conceptual part. In Model First approach developer should not edit storage part or customize mapping, because they will be re-generated each time when Generate Database Wizard is launched.

What is Code First Approach?

Code First allows you to define your model using C# or VB.Net classes, optionally additional configuration can be performed using attributes on your classes and properties or by using a Fluent API. Your model can be used to generate a database schema or to map to an existing database.
More=>http://blogs.msdn.com/b/adonet/archive/2011/03/15/ef-4-1-code-first-walkthrough.aspx

What is Entity SQL?

Entity SQL is a SQL-like storage-independent language, designed to query and manipulate rich object graphs of objects based on the Entity Data Model (EDM).
More=>http://msdn.microsoft.com/en-us/library/bb399560(v=vs.90).aspx

What is LINQ To Entities?

LINQ to Entities provides Language-Integrated Query (LINQ) support for querying entities.

LINQ to Entities enables developers to write queries against the database using one of the supported .NET Framework programming languages such as Visual Basic or Visual C#.
More=>http://msdn.microsoft.com/en-us/library/bb386964(v=vs.90).aspx

What is EntityClient?

System.Data.EntityClient is a storage-independent ADO.NET data provider that contains classes such as EntityConnection, EntityCommand, and EntityDataReader. Works with Entity SQL and connects to storage specific ADO.NET data providers, such as SqlClient.
More=>http://msdn.microsoft.com/en-us/library/bb738561(v=vs.90).aspx

What is Deferred Loading(Lazy Loading)?

When objects are returned by a query, related objects are not loaded at the same time.

Instead they are loaded automatically when the navigation property is accessed. Also known as “lazy loading,”
More=>http://msdn.microsoft.com/en-us/library/dd196179(v=vs.90).aspx

What is Eager Loading?

The process of loading a specific set of related objects along with the objects that were explicitly requested in the query.
More=>http://msdn.microsoft.com/en-us/library/bb896272(v=vs.90).aspx

What is Complex Type?

A .NET Framework class that represents a complex property as defined in the conceptual model. Complex types enable scalar properties to be organized within entities. Complex objects are instances of complex types.
More=>http://msdn.microsoft.com/en-us/library/bb738472(v=vs.90).aspx

What is Conceptual Model?

An implementation of the Entity Data Model (EDM), specific to the Entity Framework, which represents an abstract specification for the data structures that define an entity-relationship representation of data in the domain of an application.
More=>http://msdn.microsoft.com/en-us/library/bb399183(v=vs.90).aspx

What is use of Entity Container?

Specifies entity sets and association sets that will be implemented in a specified namespace.
More=>http://msdn.microsoft.com/en-us/library/bb399557(v=vs.90).aspx

What is Explicit Loading?

When objects are returned by a query, related objects are not loaded at the same time. By default, they are not loaded until explicitly requested using the Load method on a navigation property.
More=>http://msdn.microsoft.com/en-us/library/dd196179(v=vs.90).aspx

What do you mean by Navigation Property?

A property of an entity type that represents a relationship to another entity type, as defined by an association. Navigation properties are used to return related objects as an EntityCollection or an EntityReference, depending on the multiplicity at the other end of the association.
More=>http://msdn.microsoft.com/en-us/library/bb399562(v=vs.90).aspx

What is scalar property?

A property of an entity that maps to a single field in the storage model.

What is split entity?

An entity type that is mapped to two separate types in the storage model. 

What do you mean by table-per-hierarchy?

A method of modeling a type hierarchy in a database that includes the attributes of all the types in the hierarchy in one table.
More=>http://msdn.microsoft.com/en-us/library/bb738443(v=vs.90).aspx

What do you mean by table-per-type?

A method of modeling a type hierarchy in a database that uses multiple tables with one-to-one relationships to model the various types.
More=>http://msdn.microsoft.com/en-us/library/bb738685(v=vs.90).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.