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:
![clip_image001[4]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0kZZJa1yzZVLo21p_oxWlKB096sh2kh7-rI1PYwAd9B5SQzBpBpAnqXT7mApYloUDKJnkHXN-7JJxIbpZIcmHFHtLGDALkZ0jgC5FrN_ySyZUfqObjpiT2b1sOkAuEYwmau-g0Wse71e3/?imgmax=800 "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]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8M84JlGhgULLfzKe8lWpfF7_IJUXX8V618msLfoROa6_CubMHu6dw5XWXbzCWmX_GupZP7CSth0Rs6rE_fiZSb-jh_a-wJH2-meS1EUUalpEko5usQackzHqgrsmrZjKw8L4YPav26Om4/?imgmax=800 "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]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8s1v2n9QEKhICV91805RPy0V62gibPJSmjvURistnMC2LWGPdewwRIgm5p6rwZFCws8pNg49dgWusopldIFtT_KhWm0yrTiIkNERLBEvcWh3HJRR_cZm8sMIlSX6LXl36Wh9lKy64yhzh/?imgmax=800 "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]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhV9TyhEG6plHy0YAld14kemKZvhtaDl_RIBH3GzpBItOiAz_DnTGWeKFEPcKZOQMvqOefLEnPLT2NTvLi1wtrRWmBFu7FithXrY4rLxnyzpHVsA71HeAwhgzfoWUkK5MWganVGNcGBayx2/?imgmax=800 "clip_image001[10]")
I hope that post has been helpful to you to understand a few of the data annotations provided by Entity framework.
![clip_image001[10]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfGk8eLCasOPnpEiPi05HNVOjTivpLJQHnEdOp_fjSeuno7lydWj_dTEkBqNGUk2FlUrcVSwGG2QHfOz5_bxFcUt6VX2wKPGXkLuG98ITf7Iemm4gbAaJXZKyifS2Rzzlw79qjVs4MeEHe/s1600-h/clip_image001%25255B10%25255D%25255B3%25255D.png "clip_image001[10]")