Entity Framework Notes

www.enosislearning.com
Contents
Introduction to Entity Framework...............................................................................................................4
Why Entity Framework?..........................................................................................................................4
Conceptual Model...................................................................................................................................5
Features...................................................................................................................................................6
Entity Client.............................................................................................................................................6
Benefits...................................................................................................................................................7
Advantage of Entity Framework..........................................................................................................7
Entity Framework - Database Setup............................................................................................................8
One-to-Many Relationship......................................................................................................................8
Many-to-Many Relationship....................................................................................................................9
One-to-One Relationship.........................................................................................................................9
Entity Lifecycle.......................................................................................................................................10
State Changes in the Entity Lifecycle.....................................................................................................11
Unchanged State...............................................................................................................................11
Detached State..................................................................................................................................11
Added State.......................................................................................................................................11
Modified State...................................................................................................................................12
Deleted State.....................................................................................................................................12
Difference between LINQ to SQL and EF is given below....................................................................13
Steps for Creating an Entity Framework Model.........................................................................................13
Step1: Creating the database............................................................................................................14
Step2: Adding the Entity Model to the Website................................................................................14
Step3 : Doing Insert operation...........................................................................................................16
Step4: Reading all the records...........................................................................................................16
Step5: Selecting a specific record......................................................................................................16
Step6: Updating the record...............................................................................................................16
ENTITY FRAMEWORK TYPES OF QUERY.....................................................................................................17
1) LINQ to Entities..............................................................................................................................17
2) Entity SQL:.....................................................................................................................................17
3) Native SQL.....................................................................................................................................19
ENTITY FRAMEWORKS SYNTAXES..............................................................................................................19
SELECTION USING ENTITY FRAMEWORK..........................................................................................21
LINQ Method syntax:........................................................................................................................21
LINQ QUERY SYNTAX........................................................................................................................21
COMMANDS OF ENTITY FRAMEWORK..............................................................................................21
Approaches to using Entity Framework (EF) –...........................................................................................22
Database First.......................................................................................................................................23
Why Code First?....................................................................................................................................24
Example of a Code First Approach.....................................................................................................25
Understand Database Initializers.......................................................................................................27
Seeding Data..........................................................................................................................................28
Creating a Custom Database Initializer..............................................................................................30
Specifying Database Initializer in Configuration File..........................................................................31
DataAnnotations in Code-First:.................................................................................................................32
Model First............................................................................................................................................33
Example of Model First......................................................................................................................40
Walk through – Creation of Model First...........................................................................................40
Comparing the Model First, Database First and Code First approaches............................................47
Model First:.......................................................................................................................................47
Database First:..................................................................................................................................47
Code First:.........................................................................................................................................48
Stored Procedure Example using Entity Framework..................................................................................48
Importing Stored Procedure as Entity Function in Entity Framework................................................49
Call Stored Procedure using ExecuteStoreQuery<T> function...........................................................52
Call Stored Procedure using DbDataReader......................................................................................52
EXAMPLE: TO DO CRUD OPERARIONS IN ENTITIY FRAMEWORK BY USING SP..........................................53
Stored Procedures using Entity- Framework.............................................................................................53
Insert Stored Procedure.....................................................................................................................55
Delete Stored Procedure...................................................................................................................55
Select Stored Procedure....................................................................................................................56
Update Stored Procedure..................................................................................................................56
Basic GROUP BY Clause..........................................................................................................................62
Composite GROUP BY Clause.............................................................................................................64
GROUP BYManufacturer,Name.............................................................................................................64
Combination of WHERE and GROUP BYClause......................................................................................64

Introduction to Entity Framework

Entity Framework is an Object Relational Mapper (ORM). ADO.NET entity is an ORM (object
relational mapping) which creates a higher abstract object model over ADO.NET components.

It basically generates objects and entities according to the database tables and provides the
mechanism for:

1. Performing basic CRUD (Create, Read, Update, Delete) operations.

2. Easily managing "1 to 1", "1 to many", and "many to many" relationships.
3. Ability to have inheritance relationships between entities.

Basically it creates the Data Model or DAL for the Applications.

High Level Architecture of Entity Framework

Why Entity Framework?

Entity Framework is an ORM and ORMs are aimed to increase the developer’s productivity by
reducing the redundant task of persisting the data used in the applications.
 Entity Framework can generate the necessary database commands for reading or
writing data in the database and execute them for you.
 If you're querying, you can express your queries against your domain objects using LINQ
to entities.
 Entity Framework will execute the relevant query in the database and then materialize
results into instances of your domain objects for you to work within your app.
Entity Framework has a more granular mapping layer so you can customize mappings,
for example, by mapping the single entity to multiple database tables or even multiple
entities to a single table.

 Entity Framework is Microsoft's recommended data access technology for new applications.
 ADO.NET seems to refer directly to the technology for data sets and data tables.
 Entity Framework is where all of the forward moving investment is being made, which has been
the case for a number of years already.
 Microsoft recommends that you use Entity Framework over ADO.NET or LINQ to SQL for all new
development.

Conceptual Model
For developers who are used to database focused development, the biggest shift with Entity
Framework is that it lets you focus on your business domain. What it is that you want your application
to do without being limited by what the database is able to do?
 With Entity Framework, the focal point is referred to as a conceptual model. It's a model of the
objects in your application, not a model of the database you use to persist your application
data.
 Your conceptual model may happen to align with your database schema or it may be quite
different.
 You can use a Visual Designer to define your conceptual model, which can then generate the
classes you will ultimately use in your application.
 You can just define your classes and use a feature of Entity Framework called Code First. And
then Entity Framework will comprehend the conceptual model.

Either way, Entity Framework works out how to move from your conceptual model to your
database. So, you can query against your conceptual model objects and work directly with
them.

Features
Following are the basic features of Entity Framework. This list is created based on the most
notable features and also from frequently asked questions about Entity Framework.

 Entity Framework is a Microsoft tool.

 Entity Framework is being developed as an Open Source product.
 Entity Framework is no longer tied or dependent to the .NET release cycle.
 Works with any relational database with valid Entity Framework provider.
 SQL command generation from LINQ to Entities.
 Entity Framework will create parameterized queries.
 Tracks changes to in-memory objects.
 Allows to insert, update and delete command generation.
 Works with a visual model or with your own classes.
 Entity Framework has stored Procedure Support.

Entity Client
This layer exposes the entity layer to the upper layer. Entity client provides the ability for
developers to work against entities in the form of rows and columns using entity SQL queries
without the need to generate classes to represent conceptual schema. Entity Client shows the
entity framework layers, which are the core functionality. These layers are called as Entity Data
Model.
 The Storage Layer contains the entire database schema in XML format.
 The Entity Layer which is also an XML file defines the entities and relationships.
 The Mapping layer is an XML file that maps the entities and relationships defined at
conceptual layer with actual relationships and tables defined at logical layer.
 The Metadata services which is also represented in Entity Client provides centralized
API to access metadata stored Entity, Mapping and Storage layers.

Benefits

The main and the only benefit of EF is it auto-generates code for the Model (middle layer), Data Access
Layer, and mapping code, thus reducing a lot of development time.

Advantage of Entity Framework

 EF reduce code by creating Model instead of create class to access data.
 Easy and fast Functionality for select, Insert, update, delete and other CRUD operation.
 Easy to manage relationship between tables.
 Faster Development approach then ADO.NET.
 Code is also usually much neater and more maintainable
 Conceptual model can be represented in a better way.
Entity Framework - Database Setup
In this tutorial, we will be using a simple University database. A University database can be
much more complex as a whole but for demo and learning purpose, we are using the simplest
form of this database. The following diagram contains three tables.

 Student
 Course
 Enrollment

Whenever a term database is used one thing comes directly to our mind and that is different
kind of tables which has some sort of relationship. There are three types of relationships
between tables and the relationship between different tables depends on how the related
columns are defined.

 One-to-Many Relationship
 Many-to-Many Relationship
 One-to-One Relationship

One-to-Many Relationship
One-to-many relationship is the most common type of relationship. In this type of relationship,
a row in table A can have many matching rows in table B, but a row in table B can have only
one matching row in table A. For example, in the above diagram, Student and Enrollment table
have one-to-many relationship, each student may have many enrollments, but each
enrollment belongs to only one student.
public class Student {
public int ID { get; set; }
public string LastName { get; set; }
public string FirstMidName { get; set; }
public DateTime EnrollmentDate { get; set; }
public virtual ICollection<Enrollment> Enrollments { get; set; }
}

public class Enrollment {

public int EnrollmentID { get; set; }

public int CourseID { get; set; }
public int StudentID { get; set; }

public Grade? Grade { get; set; }

public virtual Course Course { get; set; }
public virtual Student Student { get; set; }
}

Many-to-Many Relationship
In a many-to-many relationship, a row in table A can have many matching rows in table B, and
vice versa. You create such a relationship by defining a third table, called a junction table,
whose primary key consists of the foreign keys from both table A and table B. For example,
Student and Course table have many-to-many relationship that is defined by a one-to-many
relationship from each of these tables to the Enrollment table.
public class Course {
[DatabaseGenerated(DatabaseGeneratedOption.None)]

public int CourseID { get; set; }

public string Title { get; set; }
public int Credits { get; set; }
public virtual ICollection<Enrollment> Enrollments { get; set; }
}

One-to-One Relationship
In one-to-one relationship, a row in table A can have no more than one matching row in table
B, and vice versa. A one-to-one relationship is created if both of the related columns are
primary keys or have unique constraints.
This type of relationship is not common because most information related in this way would
be all-in-one table. You might use a one-to-one relationship to −

 Divide a table with many columns.

 Isolate part of a table for security reasons.
 Store data that is short-lived and could be easily deleted by simply deleting the table.
 Store information that applies only to a subset of the main table.
 public class Student {
 public int ID { get; set; }
 public string LastName { get; set; }
 public string FirstMidName { get; set; }
 public DateTime EnrollmentDate { get; set; }

 public virtual ICollection<Enrollment> Enrollments { get;
set; }
 public virtual StudentProfile StudentProfile { get; set; }
 }

 public class StudentProfile {

 public StudentProfile() {}
 public int ID { get; set; }
 public string Email { get; set; }
 public string Password { get; set; }

 public virtual Student Student { get; set; }
 }

Entity Lifecycle

The Entity Lifecycle describes the process in which an Entity is created, added, modified, deleted, etc.
Entities have many states during its lifetime. Before looking at how to retrieve entity state, let’s take a
look at what is entity state. The state is an enum of type System.Data.EntityState that declares the
following values −
 Added: The entity is marked as added.
 Deleted: The entity is marked as deleted.
 Modified: The entity has been modified.
 Unchanged: The entity hasn’t been modified.
 Detached: The entity isn’t tracked.

State Changes in the Entity Lifecycle

Sometimes state of entities are set automatically by the context, but it can also be modified manually
by the developer. Even though all the combinations of switches from one state to another are possible,
but some of them are meaningless. For example, Added entity to the Deleted state, or vice versa.
Let’s discuss about different states.

Unchanged State
 When an entity is Unchanged, it’s bound to the context but it hasn’t been modified.
 By default, an entity retrieved from the database is in this state.
 When an entity is attached to the context (with the Attach method), it similarly is in the
Unchanged state.
 The context can’t track changes to objects that it doesn’t reference, so when they’re attached it
assumes they’re Unchanged.

Detached State
 Detached is the default state of a newly created entity because the context can’t track the
creation of any object in your code.
 This is true even if you instantiate the entity inside a using block of the context.
 Detached is even the state of entities retrieved from the database when tracking is disabled.
 When an entity is detached, it isn’t bound to the context, so its state isn’t tracked.
 It can be disposed of, modified, used in combination with other classes, or used in any other
way you might need.
 Because there is no context tracking it, it has no meaning to Entity Framework.

Added State
 When an entity is in the Added state, you have few options. In fact, you can only detach it from
the context.
 Naturally, even if you modify some property, the state remains Added, because moving it to
Modified, Unchanged, or Deleted makes no sense.
 It’s a new entity and has no correspondence with a row in the database.
 This is a fundamental prerequisite for being in one of those states (but this rule isn’t enforced
by the context).
Modified State
 When an entity is modified, that means it was in Unchanged state and then some property was
changed.
 After an entity enters the Modified state, it can move to the Detached or Deleted state, but it
can’t roll back to the Unchanged state even if you manually restore the original values.
 It can’t even be changed to Added, unless you detach and add the entity to the context,
because a row with this ID already exists in the database, and you would get a runtime
exception when persisting it.

Deleted State
 An entity enters the Deleted state because it was Unchanged or Modified and then the
DeleteObject method was used.
 This is the most restrictive state, because it’s pointless changing from this state to any other
value but Detached.

Difference between LINQ to SQL and EF is given below.

LINQ to SQL Entity Framework
It only works with SQL Server Database. It can works with various databases like Oracle,
DB2, MYSQL, SQL Server etc.

It generates a .dbml to maintain the relation It generates an .edmx files initially. The relation is
maintained using 3 different files .csdl, .msl
and .ssdl
It has not support for complex type. It has support for complex type.

It cannot generate database from model. It can generate database from model.

It allows only one to one mapping between the It allows one-to-one, one-to-many & many-to-many
entity classes and the relational tables /views. mappings between the Entity classes and the
relational tables /views
It allows you to query data using DataContext. It allows you to query data using EntitySQL,
ObjectContext, DbContext.
It provides a tightly coupled approach. It provides a loosely coupled approach. Since its
code first approach allows you to use Dependency
Injection pattern which make it loosely coupled.

It can be used for rapid application development It can be used for rapid application development
only with SQL Server. with RDBMS like SQL Server, Oracle, DB2 and
MySQL etc.

Steps for Creating an Entity Framework Model

Step1: Creating the database

Let's have a simple database with one table. Let's create a simple table for Contacts and we will
perform CRUD operations on this table.
Step2: Adding the Entity Model to the Website

Once we have the database ready, we can add the entity model to our website. We can do this
by adding an ADO.NET Entity Data Model to the website.

Once we select to add this data model to our website, we will have to select the approach we
want to take for our Model's contents.
What this selection means is that we can either choose to generate the entity model from an
existing database schema or we can design the entity model here and then later hook it up to
the database. Since we already have the database ready, we will use the first option. Once the
Model is generated, the Entity for each table is generated. The generated entity for our contact
table is:

Also, the classes for performing database operations are also created. We just need to know
how to use these classes to perform database operations.
Step3 : Doing Insert operation

Contact con = new Contact();

con.fname = TextBox1.Text;
con.lname = TextBox2.Text;
con.phone = TextBox3.Text;

ContactsDb db = new ContactsDb();

db.Contacts.Add(con);
db.SaveChanges();

Step4: Reading all the records

We can retrieve the collection of Entities using the Model object to achieve this. The code
snippet below will show how that can be done.

ContactsDb db = new ContactsDb();

Repeater1.DataSource = db.Contacts;
Repeater1.DataBind();

Step5: Selecting a specific record

Let's say we want the functionality of updating/deleting a record on a single page then we will
first have to select the record based on the ID, then update/delete the selected record.

Selection of any particular record (Contact) based on ID can be done as:

int idToupdate = Convert.ToInt32(Request.QueryString["id"].ToString());

ContactsDb db = new ContactsDb();
Contact con = db.Contacts.SingleOrDefault(p => p.id == idToupdate);

Once this code is executed, the Contact object will contain the required values.

Step6: Updating the record

If we want to update a record, then a simple update operation can be performed as:
int idToupdate = Convert.ToInt32(Request.QueryString["id"].ToString());
ContactsDb db = new ContactsDb();
Contact con = db.Contacts.SingleOrDefault(p => p.id == idToupdate);

con.phone = TextBox1.Text;
db.SaveChanges();

ENTITY FRAMEWORK TYPES OF QUERY

You can query EDM mainly by three ways,
1) LINQ to Entities
2) Entity SQL
3) Native SQL

1) LINQ to Entities
L2E query syntax is easier to learn than Entity SQL. You can use your LINQ skills for querying
with EDM. These are LINQ Method Syntax with Lamda expression and LINQ query syntax.
1.1 LINQ Method syntax:
//Querying with LINQ to Entities
using (var context = newSchoolDBEntities())
{
var L2EQuery = context.Students.where(s =>s.StudentName == “Bill”);
}

1.2 LINQ Query syntax:

using (var context = newSchoolDBEntities())
{
var L2EQuery = from st in context.Students
var studs = from st in db.students where st.StudentName == "Bill" select st;
}
First, you have to create an object of context class, which is SchoolDBEntities. You should
initialize it in using() so that once it goes out of scope then it will automatically call Dispose()
method of DbContext. In both the syntax above, context returns IQueryable.

2) Entity SQL:
Entity SQL is another way to create a query. It is processed by the Entity Framework’s Object
Services directly. It returns ObjectQuery instead of IQueryable.
You need ObjectContext to create a query using Entity SQL.

//Querying with Object Services and Entity SQL

String sqlString = "SELECT VALUE st FROM SchoolDBEntities.Students AS st WHERE
st.StudentName == 'Bill'";

Var objctx = (ctx as IObjectContextAdapter).ObjectContext;

ObjectQuery<Student> student = objctx.CreateQuery<Student>(sqlString);

Student newStudent = student.First<Student>();

You can also use EntityConnection and EntityCommand to execute Entity SQL as shown below:

using (var con = new EntityConnection("name=SchoolDBEntities"))

{
con.Open();
EntityCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT VALUE st FROM SchoolDBEntities.Students as st where
st.StudentName='Bill'";

Dictionary<int, string> dict = newDictionary<int, string>();

using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess |
CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
int a = rdr.GetInt32(0);
var b = rdr.GetString(1);
dict.Add(a, b);
}
}
}

EntityDataReader doesn't return ObjectQuery, instead it returns the data in rows & columns.

using (var con = new EntityConnection("name=EF_USERSEntities"))

{

con.Open();
EntityCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT VALUE st FROM
EF_USERSEntities.USERDETAILS as st where st.USERID=1";
Dictionary<int, string> dict = new Dictionary<int, string>();
using (EntityDataReader rdr =
cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess |
System.Data.CommandBehavior.CloseConnection))
{
while (rdr.Read())
{
int a = rdr.GetInt32(0);
var b = rdr.GetString(1);
dict.Add(a, b);
}
}

3) Native SQL
You can execute native SQL queries for a relational database as shown below:
using (varctx = newSchoolDBEntities())
{
varstudentName = ctx.Students.SqlQuery("Select studentid, studentname, standardId from
Student where studentname='Bill'").FirstOrDefault<Student>();
}

ENTITY FRAMEWORKS SYNTAXES

COMMAND DESCRIPTION SYNTAX

CREATE OBJECT To Create a object of entity
ContactsDb db = new ContactsDb();
framework context class

Add / Insert To add a new record into the

db.Contacts.AddObject(con);
table
db.SaveChanges();

Select/View To select the records from the

Repeater1.DataSource = db.Contacts;
table
Repeater1.DataBind();

Delete To delete the records from the USER u = db.USERS.Find(id);

table db.USERS.Remove(u);
db.SaveChanges();
Update To update the records from the db.Entry(u).State = EntityState.Modified;
table db.SaveChanges();
Single It returns a single specific //try to get single element 10 if exist
element from a collection of db.Contacts.Single(d=>d.CustId ==1);
elements if element match var student = (from st in
found. An exception is thrown, context.Students where
if none or more than one match st.StudentID == 1
found for that element in the select st).Single<Student>();
collection.

SingleorDefault It returns a single specific db.Contacts.SingleOrDefault(d=>d.CustId

element from a collection of ==1);
elements if element match
found. An exception is thrown, var student = (from st in
if more than one match found context.Students where
for that element in the st.StudentID == 1
collection. A default value is select st).SingleOrDefault<Student>();
returned, if no match is found
for that element in the
collection.
First It returns first specific element
from a collection of elements if
one or more than one match
found for that element. An
exception is thrown, if no match
is found for that element in the
collection.
FirstorDefault It returns first specific element var student = (from st in ctx.Students
from a collection of elements if where st.StudentName == "Student1"
one or more than one match select st).FirstOrDefault<Student>();
found for that element. A
default value is returned, if no
match is found for that element
in the collection.

When to use Single, SingleOrDefault, First and FirstOrDefault

You should take care of following points while choosing Single, SingleOrDefault, First and
FirstOrDefault:

1. When you want an exception to be thrown if the result set contains many records, use
Single or SingleOrDefault.
2. When you want a default value is returned if the result set contains no record, use
SingleOrDefault.
3. When you always want one record no matter what the result set contains, use First or
FirstOrDefault.
4. When you want a default value if the result set contains no record, use FirstOrDefault.

SELECTION USING ENTITY FRAMEWORK

LINQ Method syntax:

var L2EQuery = context.Students.where(s =>s.StudentName == “Bill”);

LINQ QUERY SYNTAX

var studs = from st in db.students where st.StudentName == "Bill" select st;

//Querying with Object Services and Entity SQL
String sqlString = "SELECT VALUE st FROM SchoolDBEntities.Students " +
"AS st WHERE st.StudentName == 'Bill'";

Var objctx = (ctx as IObjectContextAdapter).ObjectContext;

ObjectQuery<Student> student = objctx.CreateQuery<Student>(sqlString);

Student newStudent = student.First<Student>();

COMMANDS OF ENTITY FRAMEWORK

COMMAND DESCRIPTION SYNTAX

ExecuteStoreCommand This var ietsParameterID = new SqlParameter("@ID",
command is System.Data.SqlDbType.Int);
used to ietsParameterID.Value =
execute a Convert.ToInt16(txtEmpID.Text);
stored
procedure. var ietsParameterEmpName = new
SqlParameter("@EmpName",
txtEmployeeName.Text);
var ietsParameterEmpAddress = new
SqlParameter("@EmpAddress", txtAddress.Text);

entities.ExecuteStoreCommand("InsertEmployee
@ID,@EmpName,@EmpAddress",
ietsParameterID, ietsParameterEmpName,
ietsParameterEmpAddress);

db.Database.ExecuteSqlCommand("sp_addcomp
@name",new SqlParameter("@name","yash"));

db.SaveChanges();

ExecuteStoreQuery Execute a var selectData =

query and entities.ExecuteStoreQuery<Employee>("Select *
returns the from Employee").ToList();
result grdEmployess.DataSource = selectData;
grdEmployess.DataBind();

SELECT To select particular var query = context.Products

columns from the list .Select(product =>new
{
ProductId = product.ProductID,
ProductName = product.Name
});
Console.WriteLine("Product Info:");
foreach (varproductInfoin query)
{
Console.WriteLine("Product Id: {0} Product
name: {1} ",
productInfo.ProductId,
productInfo.ProductName);
}
WHERE To filter records Example
Var onlineOrders = context.SalesOrderHeaders
.Where(order =>order.OnlineOrderFlag == true)

Order by To sort the records in var student1 = from s in db.Students order by

asc or desc s.StudentName ascending select s;

JOIN To Join Two Tables var result = from emp in db.EMPLOYEEs join de
in db.DEPARTMENTs on emp.DEPTID equals
de.DEPTID
select new
{
EmployeeId = emp.EMPID,
EmployeeName = emp.NAME,
Department = de.DEPTNAME,
Location = de.LOCATION
};

Approaches to using Entity Framework (EF) –

Database First Code First Model First

The following Diagrams explains the EF Approaches

Database First
In the database first approach, we are creating the entity framework from an existing database.
We use all other functionality, such as the model/database sync and the code generation, in the
same way we used them in the Model First approach.

Code First
In the code first approach, we will create the Model and Context class based upon that

Entity Framework has a modeling workflow referred to as Code First.

 Code First modeling workflow targets a database that doesn’t exist and Code First will create it.
 It can also be used if you have an empty database and then Code First will add new tables too.
 Code First allows you to define your model using C# or VB.Net classes.
 Additional configuration can optionally be performed using attributes on your classes and
properties or by using a fluent API.

Why Code First?

 Code First is really made up of a set of puzzle pieces. First are your domain classes.
 The domain classes have nothing to do with Entity Framework. They're just the items of your
business domain.
 Entity Framework, then, has a context that manages the interaction between those classes and
your database.
 The context is not specific to Code First. It's an Entity Framework feature.
 Code First adds a model builder that inspects your classes that the context is managing, and
then uses a set of rules or conventions to determine how those classes and the relationships
describe a model, and how that model should map to your database.
 All of this happens at runtime. You'll never see this model, it's just in memory.
 Code First has the ability to use that model to create a database if required.

Example of a Code First Approach

Create empty MVC project.

2. As per we describe in our previous article[^] add Entity Framework reference to project.
3. Add Student.cs, Department.cs and StudentDbContext.cs in model folder.

using System.Data.Entity;
Student.cs

public class Student

{

public Student()
{
}
[Key]
Public int Id { get; set; }
public string Name { get; set; }

[DataType(DataType.Date),DisplayFormat(DataFormatString = "{0:dd/MM/yy}", ApplyFormatInEditMode = true)]

Public DateTime? DateOfBirth{ get; set; }

public string EmailId { get; set; }

public string Address { get; set; }
public string City { get; set; }

public Department Department { get; set; } // Navigation Property

}
Department.cs

public class Department

{

[Key]
Public int Id { get; set; }
public string DepartmentName { get; set; }
public List<Student> students { get; set; } // Navigation property

}
StudentDbContext.cs

public class StudentDbContext : DbContext

{
Public StudentDbContext () : base("StudentDbContext")
{
}
Public DbSet<Student> Students { get; set; }
Public DbSet<Department> Departments { get; set; }
}
We add base("StudentDbContext") that will indicate your connectionString name.
4. Add connectionstring to web.config file.(Note: It will automatically create Database if you will not add any

connectionstring in project.)
<connectionStrings>
<add name="StudentDbContext" connectionString="server=Your Server Name;
database=MVCEFcodeFirst; integrated security=true;"
providerName="System.Data.SqlClient"/>
</connectionStrings>

5. Add HomeController.cs to Controllers folder.

public class HomeController : Controller

{
Public ActionResult Index()
{
using(var context= new StudentDbContext())
{
var student = new Student()
{
Name = "Test",
EmailId = "test@gmail.com",
Address = "Test Address",
City = "Test City"
};
context.Students.Add(student);
context.SaveChanges();
}
return View();
}
}
Database
Now, it's time to run project. EF code first creates database with name "MVCEFcodeFirst" as we declared in
connectionstring.
Understand Database Initializers
Entity Framework code first approach create database automatically based on POCO classes. By default
Database Initializers will handle this behavior. Entity Framework has basic four Database Initializers (1)
CreateDatabaseIfNotExists, (2) DropCreateDatabaseWhenModelChanges, (3)
DropCreateDatabaseAlways and (4) MigrateDatabaseToLatestVersion. MigrateDatabaseToLatestVersion
is newly added in EF 6.0 version.

CreateDatabaseIfNotExists
- Default Database Initializer class unless you declare another class. As the name suggests it will create
new Database if it doesn't exists.
DropCreateDatabaseWhenModelChanges
- This Database Initializer class will drop and recreate the whole database whenever any data model
changes found. This class is useful during starting phase of development when you don't have any
concern with existing database records.

DropCreateDatabaseAlways
- This class will always delete your existing database and re-create new database every time application
runs. This Initializer useful when you want fresh database every time.

MigrateDatabaseToLatestVersion
- This Database initializers added in Entity Framework version 6.0. This initializer is used to update the
database to the latest version. This is very useful initializer to update your database scheme without
deleting your database records.

To use a particular database initializer add the following piece of code at the beginning of the
Main() method:

Database.SetInitializer(new DropCreateDatabaseAlways<BlogContext>());

The SetInitializer() method takes an instance of database initializer class and sets it as a
database initializer for the current application domain. When you set a database initializer, it
won't be called immediately. It will be called when the context (BlogContext) is used for the
first time. In the preceding example, the actual database creation will occur only when you add
a new Category and BlogPost and not when a new instance of BlogContext is created.

static void Main(string[] args)

{
Database.SetInitializer(new DropCreateDatabaseAlways<BlogContext>());
using (vardb = new BlogContext()) //initializer won't be called here
{
...
db.Categories.Add(cat); //initializer will be called here
db.BlogPosts.Add(post);
...
}
Console.ReadLine();
}

Seeding Data
During the testing phase you often need to populate database tables with sample data. At
times you also need to populate some application data at the time of database creation. For
example, while creating our sample database you may want to populate the Categories table
with some predefined categories. Such seed data can be added to the database being created
by overriding the Seed() method of the database initializer class. Consider the following piece of
code :

Public class BlogContextSeedInitializer:DropCreateDatabaseAlways<BlogContext>

{
protectedoverridevoidSeed(BlogContext context)
{
Category cat1 =newCategory{Id=Guid.NewGuid(),Name=".NET Framework"};
Category cat2 =newCategory{Id=Guid.NewGuid(),Name="SQL Server"};
Category cat3 =newCategory{Id=Guid.NewGuid(),Name="jQuery"};
context.Categories.Add(cat1);
context.Categories.Add(cat2);
context.Categories.Add(cat3);
context.SaveChanges();
}
}

Here, you created a custom database initializer by inheriting DropCreateDatabaseAlways class.

Further, you need to override the Seed() method. The Seed() method receives the context
object as a parameter. You then create three categories and add them to the context. Finally
SaveChanges() method saves the data to the database that was created during the initialization
process.

To see the Seed() method in action, you need to use BlogContextSeedInitializer in the Main()
method. Adding the following line of code will do that job:

Database.SetInitializer(newBlogContextSeedInitializer());

If you run the application again and check the Categories table, you should see sample data
added to it.

Figure 3: Sample data added

Creating a Custom Database Initializer

In the preceding examples you used inbuilt database initializers. You can also create a custom
database initializer by implementing the IDatabaseInitializer interface. You need to implement
the InitializeDatabase() method of IDatabaseInitializer interface and write your own logic of
database creation. The following code shows a sample implementation of the
InitializeDatabase() method:
Public class BlogContextCustomInitializer:IDatabaseInitializer<BlogContext>
{
publicvoidInitializeDatabase(BlogContext context)
{
if(context.Database.Exists())
{
if(!context.Database.CompatibleWithModel(true))
{
context.Database.Delete();
}
}
context.Database.Create();
context.Database.ExecuteSqlCommand("CREATE TABLE GLOBAL_DATA([KEY] VARCHAR(50), [VALUE]
VARCHAR(250))");
}
}

The InitializeDatabase() method receives an instance of a content class. You can then use the
Exists() method to determine whether a database is already present. The
CompatibleWithModel() method tells you (true / false) whether the database schema is
compatible with the model. If the database is not compatible you delete and recreate it using
Delete() and Create() methods respectively. If no database exists then you create a new one
using the Create() method. Notice how the code is using the ExecuteSqlCommand() method to
create the GLOBAL_DATA table that is not part of the model. Though we don't use that table in
our example it illustrates how custom initializers can be used to perform custom tasks.

Now, set the BlogContextCustomInitializer class as the initializer using the SetInitializer()
method and run the application. You will find that in addition to model tables, the
GLOBAL_DATA table is also created.

Figure 4: The GLOBAL_DATA table is created

Specifying Database Initializer in Configuration File

In all of the preceding examples you specified a database initializer in the code itself. You can also
specify it in the application configuration file by adding a key in the <appSettings> section. This
technique can be useful if you are switching between database initializers often. The <appSettings> key
and value must be in a specific format as shown in the following markup:
<addkey="DatabaseInitializerForType
CodeFirstDbInitializerDemo.BlogContext,CodeFirstDbInitializerDemo"
value="CodeFirstDbInitializerDemo.BlogContextCustomInitializer, CodeFirstDbInitializerDemo"/>

Notice the key carefully. It must begin with a predefined value DatabaseInitializerForType followed by a
white space and then followed by the assembly qualified name of the context class. The first part of the
assembly qualified name is of the form MyNamespace.MyContextClass and the second part is the name
of the assembly that contains the context class (CodeFirstDbInitializerDemo). The value is an assembly
qualified name of custom database initializer class.

To see the above setting in action, comment out the SetInitializer() call from the Main() method and run
the application again. You will find that the application picks up the database initializer details from the
configuration file and creates the database as per the logic specified therein.

Database.SetInitializer<StudentDbContext>(newCreateDatabaseIfNotExists<StudentDbContext>());
//Default one

Code-First Approach

Advantages

1. No need to look at the database for any changes in the tables as we can do those in our domain
models and these will be migrated to the database.
2. Provides complete control on each table level of the set, for example, lazy
loading, serialization etc.
3. All changes will be tracked in the database such that we can roll back to any version as needed.
4. We don’t need any heavy .edmx files and T4 script executions.
5. Can do changes in the database with no data loss (this feature is available since EF 4.3).

Disadvantages

1. If there are some 100’s of tables, we need to manually create the domain models.
2. Writing database objects like Stored Procedures, triggers are complex.
3. If there is any modification done in the database, it won’t reflect on the entities in the
application.
4. Should have the good C# knowledge to write code while Migrations.
5. Not preferred for data-intensive applications.

DataAnnotations in Code-First:

EF Code-First provides a set of DataAnnotation attributes, which you can apply on your domain classes
and properties.
Note: DataAnnotations only give you subset of configuration options. Fluent API provides full set of
configuration options available in Code First.

System.ComponentModel.DataAnnotations Attributes:

Attribute Description
Key Mark property as EntityKey which will be mapped to PK of related table.
Timestamp Mark the property as a non-nullable timestamp column in the database.
ConcurrencyCheck ConcurrencyCheck annotation allows you to flag one or more properties to be
used for concurrency checking in the database when a user edits or deletes an
entity.
Required The Required annotation will force EF (and MVC) to ensure that property has
data in it.
Timestamp Mark the property as a non-nullable timestamp column in the database.
ConcurrencyCheck ConcurrencyCheck annotation allows you to flag one or more properties to be
used for concurrency checking in the database when a user edits or deletes an
entity.
Required The Required annotation will force EF (and MVC) to ensure that property has
data in it.
MinLength MinLength annotation validates property whether it has minimum length of
array or string.
MaxLength MaxLength annotation maximum length of property which in turn sets the
maximum length of column in the database
StringLength Specifies the minimum and maximum length of characters that are allowed in a
data field.

System.ComponentModel.DataAnnotations.Schema Attributes:

Table Specify name of the DB table which will be mapped with the class
Column Specify column name and datatype which will be mapped with the property
Index Create an Index for specified column. (EF 6.1 onwards only)
ForeignKey Specify Foreign key property for Navigation property
NotMapped Specify that property will not be mapped with database
DatabaseGenerated DatabaseGenerated attribute specifies that property will be mapped to Computed
column of the database table. So the property will be read-only property. It can
also be used to map the property to identity column (auto incremental column).

Model First
In the Model First approach, the database model is created first using the ORM designer in Visual Studio. Once the model
consisting of entities and relationships has been designed, the physical database will be generated from the model.
let us learn how to create an entity data model in the designer using the workflow
referred to as Model First.
 Model First is great for when you're starting a new project where the database
doesn't even exist yet.
 The model is stored in an EDMX file and can be viewed and edited in the Entity
Framework Designer.
 In Model First, you define your model in an Entity Framework designer then
generate SQL, which will create database schema to match your model and
then you execute the SQL to create the schema in your database.
 The classes that you interact with in your application are automatically generated
from the EDMX file.
Following is a simple example of creating a new console project using Model First
approach.

Step 1 − Open Visual Studio and select File → New → Project

Step 2 − Select Installed → Templates → Visual C# → Windows from left pane and
then in middle pane, select Console Application.
Step 3 − Enter EFModelFirstDemo in the Name field.
Step 4 − To create model, first right-click on your console project in solution explorer
and select Add → New Items…
The following dialog will open.

Step 5 − Select ADO.NET Entity Data Model from middle pane and enter name
ModelFirstDemoDB in the Name field.
Step 6 − Click Add button which will launch the Entity Data Model Wizard dialog.
Step 7 − Select Empty EF Designer model and click Next button. The Entity Framework
Designer opens with a blank model. Now we can start adding entities, properties and
associations to the model.
Step 8 − Right-click on the design surface and select Properties. In the Properties window,
change the Entity Container Name to ModelFirstDemoDBContext.

Step 9 − Right-click on the design surface and select Add New → Entity…
Add Entity dialog will open as shown in the following image.

Step 10 − Enter Student as entity name and Student Id as property name and click Ok.
Step 11 − Right-click on the new entity on the design surface and select Add New →
Scalar Property, enter Name as the name of the property.
Step 12 − Enter FirstName and then add another two scalar properties such as LastName and
EnrollmentDate.

Step 13 − Add two more Entities Course and Enrollment by following all the steps
mentioned above and also add some Scalar properties as shown in the following steps.

Step 14 − We have three entities in Visual Designer, let’s add some association or
relationship between them.
Step 15 − Right-click on the design surface and select Add New → Association…
Step 16 − Make one end of the relationship point to Student with a multiplicity of one and the
other end point to Enrollment with a multiplicity of many.

Step 17 − This means that a Student has many Enrollments and Enrollment belongs to one
Student.
Step 18 − Ensure the Add foreign key properties to 'Post' Entity box is checked and click OK.
Step 19 − Similarly, add one more association between Course and Enrollment.

Step 20 − Your data model will look like the following screen after adding associations
between entities.

Example of Model First

Walk through – Creation of Model First

In order to create the model, you should create an empty ASP.Net project in Visual Studio and add a new ADO.Net Entity
Data Model, which for this example we’ll call ModelSample.
Figure 1: Add ADO.Net Entity Data Model

This will open the Entity Data Model Wizard. Select the Empty Model from the Model Contents selection.

Figure 2: Choose Model Contents

This loads the entity data model designer. Add Entities from the toolbox to our entity data model designer. Link the entities
using the ‘Association from’ Toolbox to complete the model design.

Figure 3: Sample designer with entities

NEW FEATURE: Entity Color. As you can see in the above diagram, we can color the entities appropriately for better
understanding and grouping. In our sample, leave-related entities are colored with orange, organization details in purple
and the employee details in blue.

Once the entity model design is completed, generate the database from the model using the ‘Generate Database from
Model’ context menu. Right-click on the designer to invoke the Context menu, then select the ‘Generate Database from
Model’option.
Figure 4:Generate Database from Model

Select an existing database connection or create a new connection to create the sample database from the Model. For this
sample, I have used SQLExpress with the SampleDB database connection.

Figure 5: Choose Data Connection

This will generate the DDL statements, and the generated script will be added to the solution as a script file.

Figure 6: Specify DDL file name

The script file generated in the previous step will be opened in Visual Studio. Click ‘Execute’in order to create the database
using the generated script.
Figure 7: Execute Script

Now, the database with all tables and relationships is created in the database server. For working with the new database,
generate the code using the Code Generation Strategy; set this value as “default”.

Figure 8: code Generation Strategy

This will generate the entity framework code and database context classes corresponding to the defined model. We can use
these new classes from the business layer to perform various database operations. Verify the code generated under the
Modelname.Designer.cs file.

Figure 9: Designer Code

We can sync the Model with the database either way using the context menu options – ‘Generate Database from Model’ or
‘Update Model from Database’. You can modify the model and then invoke the ‘Generate database from Model’ context
menu option to update the database schema. Any modification in database schema can get updated to the model using the
‘Update Model from Database’ context menu option.

NEW FEATURE: Multiple-diagrams per single Model.

Another new feature introduced in EF 5.0 is to allow the use of multiple diagrams for a single model. We can move selected
entities to another diagram in order to reduce the complexity of the main diagram. Select the entities by holding shift key
and select the “Move to new diagram” option from the context menu.

Figure 10: Move to new diagram

Let us move the entities related to leave to another diagram. This will create a new diagram with selected entities; in our
case Leave and Leavedetail.
Figure 11: Multiple diagram for singl0e Model

As you can see, the relationship between the LeaveDetail entity and the Employee entity has been removed. We can use
the new ORM feature to include the related entities in the second diagram by keeping one copy of the same in the first
diagram for better readability. We can include the related entities in the second diagram using “Include Related” option.
This will create a copy of all the related entities in the second diagram. Select theLeavedetail entity, right-click and select
the option “Include Related” to include the employee entities in the second diagram.

Figure 12: Include Related

Figure 13: Multi-diagram with Include Related

Comparing the Model First, Database First and Code First approaches

Model First:

 Good support with EDMX designer

 We can visually create the database model
 EF generates the Code and database script
 Extensible through partial classes
 We can modify the model and update the generated database.

Database First:
 An existing database can be used
 Code can be auto-generated.
 Extensible using partial classes
 The developer can update the database manually
 There is a very good designer, which sync with the underlining database

Code First:

 There is full control of the model from the Code; no EDMX/designer

 No manual intervention to DB is required
 The database is used for data only

Hardcode coders love to code the model using the Code First approach. In this approach, the database is used for storing
the data only. The database structure is defined by various classes. One of the advantages of the Code First approach is the
same class is used for defining the database table structure and business object. Any changes in the class affect both the
business object and the database table.

The Model First approach is preferable when creating the model using the ORM designer. Powerful features of the ORM
designer help in creating a model very easily. The clear representation of the model in visual form is more understandable
to all stakeholders involved in product development.

If we have an existing database, like in the case of a maintenance project, we can use the Database First approach to create
the Entity Framework objects out of the existing database. Any modification to the Model using the ORM designer requires
syncing back to the database. Remember any changes to the model result in the deletion of the entire database so it can be
re-created – all existing data will be lost.

Stored Procedure Example using Entity Framework

Create a Table & Stored Procedure

CREATE TABLE [dbo].[DepartmentMaster](
[DepartmentId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[DepartmentName] [varchar](50) NULL,
[Status] [tinyint] NULL,
)

CREATE TABLE [dbo].[EmployeeMaster](

[EmployeeID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[EmployeeName] [varchar](100) NULL,
[DepartmentID] [int] NULL,
[Status] [tinyint] NULL
)
CREATE PROCEDURE [dbo].[GetEmployeeData]
AS
BEGIN
SELECT EmployeeID,EmployeeName,DepartmentName FROM EmployeeMaster E
INNER JOIN DepartmentMaster D ON E.DepartmentID = D.DepartmentId
END
--

Inserting some Dummy Data.

SET IDENTITY_INSERT [dbo].[DepartmentMaster] ON

INSERT [dbo].[DepartmentMaster] ([DepartmentId], [DepartmentName], [Status]) VALUES (1, N
'Maths', 0)
INSERT [dbo].[DepartmentMaster] ([DepartmentId], [DepartmentName], [Status]) VALUES (2, N
'English', 0)
INSERT [dbo].[DepartmentMaster] ([DepartmentId], [DepartmentName], [Status]) VALUES (3, N
'Physics', 0)
SET IDENTITY_INSERT [dbo].[DepartmentMaster] OFF
SET IDENTITY_INSERT [dbo].[EmployeeMaster] ON
INSERT [dbo].[EmployeeMaster] ([EmployeeID], [EmployeeName], [DepartmentID], [Status]) VA
LUES (1,N'Tejas', 1, 0)
INSERT [dbo].[EmployeeMaster] ([EmployeeID], [EmployeeName], [DepartmentID], [Status]) VA
LUES (2,N'Rakesh', 1, 0)
INSERT [dbo].[EmployeeMaster] ([EmployeeID], [EmployeeName], [DepartmentID], [Status]) VA
LUES (3,N'Jignesh', 2, 0)
INSERT [dbo].[EmployeeMaster] ([EmployeeID], [EmployeeName], [DepartmentID], [Status]) VA
LUES (4,N'Kunal', 3, 0)
SET IDENTITY_INSERT [dbo].[EmployeeMaster] OFF

Importing Stored Procedure as Entity Function in Entity Framework

The Entity Framework has the capability of importing a Stored Procedure as a function. We can
also map the result of the function back to any entity type or complex type.
The following is the procedure to import and use a Stored Procedure in Entity Framework.

Step 1: Import Stored Procedure

When we finish this process, the selected Stored Procedure is added to the model browser
under the Stored Procedure Node.

Step 2: Right-click Stored Procedure and select "Add Function Import".

Step 3: Map the Stored Procedure

Here, we can map a returned object of our Stored Procedure. The return type may be a scalar
value or a collection of Model Entities or a collection of Complex (Custom) Entity. From this
screen we can create a Complex Entity as well.
Step 4: Call the Stored Procedure

Now, we can call the Stored Procedure as an entity function using the following code. The
entity function returns a complex type called "EmployeeDetails".

using (Entities context = new Entities())

{
IEnumerable<EmployeeDetails>empDetails = context.GetEmployeeData();
}
Call Stored Procedure using ExecuteStoreQuery<T> function
"ExecuteStoreQuery<T>" should be used to query data. This method only works if T has a
Default Constructor and also a Property name is the same as the returned column names. "T"
can be any generic class or any data type and it might not be a part of an EF generated entity.

The following is the procedure to retrieve data using the "ExecuteStoreQuery<T>" method from
a Stored Procedure.

Step 1: The method "T" can be anything, it may be an EF Generated entity or it may be a
Custom Entity, so first I am creating a Custom Entity "EmployeeDetail". Here the
EmployeeDetail properties name must be the same as the returned column of the select
statement of the Stored Procedure.

// Creating Custom class to hold result of Stored Procedure

public class EmployeeDetail
{
public int EmployeeID { get; set; }
public string EmployeeName { get; set; }
public string DepartmentName { get; set; }
}

using (Entities context = new Entities())

{
IEnumerable<EmployeeDetails>empDetails =
context.ExecuteStoreQuery<EmployeeDetails>
("exec GetEmployeeData").ToList();
}

// using DBContext (EF 4.1 and above)

using (Entities context = new Entities())
{

IEnumerable<EmployeeDetails>empDetails = context. Database.SqlQuery

< EmployeeDetails >("exec GetEmployeeData ", null).ToList();
}

Call Stored Procedure using DbDataReader

We can also retrieve data or call a Stored Procedure using a SQL Connection Command and
DbDataReader. The Object Context has a translate method that translates the entity data from
DbDataReader into the requested type object. This method enables us to execute a standard
ADO.Net query against a data source and return data rows into entity objects. Using the
following code we can call a Stored Procedure and retrieve data in entity form.

using (Entities context = new Entities())

{
string ConnectionString =
(context.Connection as EntityConnection).StoreConnection.ConnectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.ConnectTimeout = 2500;
SqlConnection con = new SqlConnection(builder.ConnectionString);
System.Data.Common.DbDataReader sqlReader;
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "GetEmployeeData";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;

sqlReader = (System.Data.Common.DbDataReader)cmd.ExecuteReader();
IEnumerable<EmployeeDetail>empDetails =
context.Translate<EmployeeDetail>(sqlReader).ToList();
}
}

EXAMPLE: TO DO CRUD OPERARIONS IN ENTITIY FRAMEWORK BY USING SP

Stored Procedures using Entity- Framework
1. By calling Stored Procedures using ExecuteStoreCommand and ExecuteStoreQuery, without
mapping to the Model.
2. By mapping Stored Procedures to the Model.

This example will show how we can map Stored Procedures to the Model to implement CRUD
operations.Create an empty web application from VS, see:
First let us create a sample table. Since I do not have SQL Server installed in my machine, I am
adding a SQL Server database as in the following:

Here you can use the name Database1.mdf depending on your naming convention. Now in
Server Explorer, you will see your database, we will add a table and some Stored Procedures as
follows:
The most important aspect before you start working on Entity Framework is to have a Primary
Key in your table.

Now my table looks as in the following, on which we are going to perform CRUD operations.

OK now let's create Stored Procedures for Insert, Update, Delete, and Select operations.

Insert Stored Procedure

Create PROCEDURE dbo.InsertEmployee

(
@ID int,
@EmpNamevarchar(50),
@EmpAddressvarchar(50)
)
AS Begin
insertinto Employee(EmpID,Emp_Name,Emp_Address)values(@ID,@EmpName,@EmpAddress)
END

Delete Stored Procedure

Create PROCEDURE dbo.deleteEmp

(
@ID int
)
As Begin
Delete from Employee whereEmpID=@ID
End
Select Stored Procedure

Create PROCEDURE dbo.SelectEmployee

As Begin
select * from Employee
End

Update Stored Procedure

Create PROCEDURE dbo.UpdateEmployee

(
@ID int,
@EmpNamevarchar(50),
@EmpAddressvarchar(50)
)
As
Begin
update Employee setEmp_Name=@EmpName,Emp_Address=@EmpAddresswhereEmpID=@ID
End

We are finished with our database. Now let us create a sample page and add an Entity Model to
our application.

Adding an Entity Model to your application:

After adding a Model you will immediately have this Entity Data Model Wizard where you have
to select Generate from the database and click on Next:
Select New Connection from Choose your data:

Here on the Data Source you will have various sources which you will see by clicking on Change,
as I have created my database in my application I will use Microsoft SQL Server Database File
(SqlClient), if anyone is using SQL Server you can change that to SQL Server from the options
available.

Since I am using a Microsoft SQL Server Database File (SqlClient) I will browse for my Database
file and click on "OK".
Here you will see my Database file and also the connection settings in Web.Config will be saved
with the name EntitySampleEntities. Click Next where you will find all your tables and Stored
Procedures that you have created. Select the required one. Since I created only one table and 4
Stored Procedures I will select them.

Initial Window

Click on "Finish" after you are finished, then you will see your model with the tables you added
and if there are any relations it will also map them. As of now I created just one table that will
be shown as follows:
Now we are finished with creating the database and adding it to an Entity Model. Now we will
see how to perform CRUD operations without mapping the Stored Procedures to the model.

Public partial class crud : System.Web.UI.Page

{
EntitySampleEntities entities = new EntitySampleEntities();
protectedvoid Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
checkMax();
loadGrid();
bindDDL();
}
}
Protected void btnInsert_Click(object sender, EventArgs e)
{
var ietsParameterID = new SqlParameter("@ID", System.Data.SqlDbType.Int);
ietsParameterID.Value = Convert.ToInt16(txtEmpID.Text);
var ietsParameterEmpName = new SqlParameter("@EmpName", txtEmployeeName.Text);
var ietsParameterEmpAddress = new SqlParameter("@EmpAddress", txtAddress.Text);

entities.ExecuteStoreCommand("InsertEmployee @ID,@EmpName,@EmpAddress",
ietsParameterID, ietsParameterEmpName, ietsParameterEmpAddress);
loadGrid();
checkMax();
bindDDL();
txtAddress.Text = string.Empty;
txtEmployeeName.Text = string.Empty;

}
Public void checkMax()
{
int? maxEmpID = entities.Employees.Max(q => (int?)q.EmpID);
if (maxEmpID != null)
{
maxEmpID = maxEmpID + 1;
txtEmpID.Text = maxEmpID.ToString();
}
else
{
maxEmpID = 1000;
txtEmpID.Text = maxEmpID.ToString();
}
}
Public void loadGrid()
{
var selectData = entities.ExecuteStoreQuery<Employee>("SelectEmployee").ToList();
grdEmployess.DataSource = selectData;
grdEmployess.DataBind();
}
Public void bindDDL()
{
var display = from e in entities.Employees select new { e.EmpID };
ddlEmpID.DataSource = display.ToList();
ddlEmpID.DataTextField = "EmpID";
ddlEmpID.DataValueField = "EmpID";
ddlEmpID.DataBind();
ddlEmpID.Items.Insert(0, "--Select--");

ddleditEmpID.DataSource = display.ToList();
ddleditEmpID.DataTextField = "EmpID";
ddleditEmpID.DataValueField = "EmpID";
ddleditEmpID.DataBind();
ddleditEmpID.Items.Insert(0, "--Select--");
}
Protected void btnDelete_Click(object sender, EventArgs e)
{
if (ddlEmpID.SelectedItem.Text != "--Select--")
{
var ietsParameterID = new SqlParameter("@ID", ddlEmpID.SelectedItem.Text);
entities.ExecuteStoreCommand("deleteEmp @ID", ietsParameterID);
loadGrid();
checkMax();
bindDDL();
}
}
Protected void btnUpdate_Click(object sender, EventArgs e)
{
if (ddleditEmpID.SelectedItem.Text != "--Select--")
{
var ietsParameterID = new SqlParameter("@ID", System.Data.SqlDbType.Int);
ietsParameterID.Value = Convert.ToInt16(ddleditEmpID.SelectedItem.Text);
var ietsParameterEmpName = new SqlParameter("@EmpName", txtedtEmployeeName.Text);
var ietsParameterEmpAddress = new SqlParameter("@EmpAddress", txtedtEmpAddress.Text);

entities.ExecuteStoreCommand("UpdateEmployee
@ID,@EmpName,@EmpAddress",ietsParameterID, ietsParameterEmpName,
ietsParameterEmpAddress);
loadGrid();
}
}
Protected void ddleditEmpID_SelectedIndexChanged(object sender, EventArgs e)
{
if (ddleditEmpID.SelectedItem.Text != "--Select--")
{
int id = Convert.ToInt16(ddleditEmpID.SelectedValue.ToString());
var display = from e1 in entities.Employees
where e1.EmpID.Equals(id)
select new { e1.Emp_Name, e1.Emp_Address };

foreach (var v in display)

{
txtedtEmployeeName.Text = v.Emp_Name;
txtedtEmpAddress.Text = v.Emp_Address;
}
}
}
}
Sample screenshots When you first run the application:
Framework without mapping Stored Procedures to the Model.

var count = db.Invoices.Count();

var total = db.Invoices.Sum(i => i.Paid);
var average = db.Invoices.Average(i => i.Paid);

CREATE TABLE [dbo].[Cars] (

[CarId] [int] IDENTITY(1,1) PRIMARY KEY,
[Name] [nvarchar](50) NOT NULL,
[Manufacturer] [nvarchar](50) NOT NULL,
[Year] [int] NULL,
[Price][ int] NULL
) ON [PRIMARY]

SET IDENTITY_INSERT[dbo].[Cars] ON
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (1,N'Lantra',N'Hyundai', 2011, 100)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (2,N'Lantra',N'Hyundai', 2012, 100)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (3,N'Lantra',N'Hyundai', 2013, 101)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (4,N'Lantra',N'Hyundai', 2014, 110)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (5,N'Golf',N'VW', 2011,NULL)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (6,N'Golf',N'VW', 2012, 110)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (7,N'Golf',N'VW', 2013, 115)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (8,N'Golf',N'VW', 2014, 120)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (9,N'118',N'BMW', 2013, 110)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (10,N'118',N'BMW', 2013, 115)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (11,N'118',N'BMW', 2014, 115)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (12,N'118',N'BMW', 2014, 116)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (13,N'Yaris',N'Toyota', 2012, 105)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (14,N'Yaris',N'Toyota', 2013, 106)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (15,N'Yaris',N'Toyota', 2013, 107)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (16,N'Yaris',N'Toyota', 2014,NULL)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (17,N'Yaris',N'Toyota', 2014, 108)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (18,N'i30',N'Hyundai', 2012, 107)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (19,N'i30',N'Hyundai', 2013, 107)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (20,N'i30',N'Hyundai', 2014, 108)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (21,N'i30',N'Hyundai', 2014, 109)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (22,N'Lantra',N'Hyundai',NULL, 120)
INSERT [dbo].[Cars]([CarId],[Name],[Manufacturer],[Year],[Price])VALUES (23,N'i30',N'Hyundai',NULL, 125)

SET IDENTITY_INSERT [dbo].[Cars] OFF

Basic GROUP BY Clause

SELECT Manufacturer, MAX(Price) AS MaxPrice,MIN(Price) AS MinPrice FROM [dbo].[Cars]

GROUP BY Manufacturer
var results3 = (from c in db.Cars group c by c.Manufacturer into g
select new
{
Manufacturer = g.Key,
MaxPrice = g.Max(q =>q.Price),
MinPrice = g.Min(q =>q.Price)
});

Manufacturer MaxPrice MinPrice

BMW 116 110
Hyundai 125 100
Toyota 108 105
VW 120 110

With LINQ, try to achieve the same result. There are two different types of using LINQ:
// LINQ
Var results1=(from c in context. Cars group c by c.Manufacturer into g
select new CarViewModel()
{
Manufacturer=g.Key,
MaxPrice=g.Max(q=>q.Price),
MinPrice=g.Min(q=>q.Price)
});
// Fluent API
Var results2=this._repository
.Get()
.GroupBy(c=>c.Manufacturer,r=>new{Manufacturer=r.Manufacturer,Price=r.Price})
.Select(g=>newCarViewModel()
{
Manufacturer=g.Key,
MaxPrice=g.Max(q=>q.Price),
MinPrice=g.Min(q=>q.Price)
});
Composite GROUP BY Clause
We’re getting a maximum and minimum car price by car name and manufacturer. With SQL query, this
can be
SELECT Manufacturer,Name,MAX(Price)ASMaxPrice,MIN(Price)ASMinPrice
FROM [dbo].[Cars]
GROUP BYManufacturer,Name

Manufacturer Name MaxPrice MinPrice

BMW 118 116 110

How can we achieve this in c# with LINQ? Let’s see the following code example: //LINQ

Var results1=(from c in _context.Cars

group c by new{Manufacturer=c.Manufacturer,Name=c.Name} into g
select new CarViewModel()
{
Manufacturer=g.Key.Manufacturer,
Name=g.Key.Name,
MaxPrice=g.Max(q=>q.Price),
MinPrice=g.Min(q=>q.Price)
});
// Fluent API
Var results2=this._repository
.Get()
.GroupBy(c=>new{Manufacturer=c.Manufacturer,Name=c.Name},
(g,r)=>newCarViewModel()
{
Manufacturer=g.Manufacturer,
Name=g.Name,
MaxPrice=r.Max(q=>q.Price),
MinPrice=r.Min(q=>q.Price)
});
Combination of WHERE and GROUP BYClause
The original data has two records having NULL values on their Year field. We need to filter them out to
get more accurate maximum/minimum price range. For this, the simplq SQL query looks like:
SELECT Manufacturer,Name,MAX(Price) AS MaxPrice,MIN(Price) AS MinPrice
FROM[dbo].[Cars] WHERE[Year]ISNOT NULL GROUP BYManufacturer,Name

It returns the record set of:

Manufacturer Name MaxPrice MinPrice
BMW 118 116 110
Hyundai i30 109 107
Hyundai Lantra 110 100
Toyota Yaris 108 105
VW Golf 120 110

Let’s see the c# code example to get the same results.// LINQ
Var results1=(from c in this._context.Cars
Where c.Year!=null
Group c by new{Manufacturer=c.Manufacturer,Name=c.Name}into g
select new CarViewModel()
{
Manufacturer=g.Key.Manufacturer,
Name=g.Key.Name,
MaxPrice=g.Max(q=>q.Price),
MinPrice=g.Min(q=>q.Price)
});
// Fluent API
varresults2=this._repository
.Get()
.Where(c=>c.Year!=null)
.GroupBy(c=>new{Manufacturer=c.Manufacturer,Name=c.Name},
(g,r)=>newCarViewModel()
{
Manufacturer=g.Manufacturer,
Name=g.Name,
MaxPrice=r.Max(q=>q.Price),
MinPrice=r.Min(q=>q.Price)
});

A LINQ JOIN keyword is used to combine rows from two or more tables, based on a common field
between them. Like SQL Joins, the Linq is also provided some keywords to achieve Inner Join and
Outer Join. As we know from SQL outer join is divided into 2 groups that is Left Outer Join and Right
Outer Join. Here, in this tip, we learn how to achieve these joins in LINQ. Let us discuss what is inner
join.
 INNER JOIN: Inner Join only return matched records between two or more tables based on
a common field between these tables.
LEFT OUTER JOIN: Return all rows from the left table, and the matched rows from the right
table.
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table.
FULL JOIN: Return all rows when there is a match in ONE of the tables.
Here in this console application, I have two lists (EmployeeList and DepartmentList). Considering
these 2 lists as 2 different tables, I am joining these 2 tables on the basis of a common column, i.e.,
ID.
INNER JOIN

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Threading.Tasks;
6.
7. namespace Joins
8. {
9. public class Employee
10. {
11. public int Id { get; set; }
12. public int age { get; set; }
13. public string name { get; set; }
14. public string gender { get; set; }
15. }
16. public class Department
17. {
18. public int id { get; set; }
19. public string Departments { get; set; }
20. public string Location { get; set; }
21. }
22. class Demo
23. {
24. static void Main(string[] args)
25. {
26. List<Employee> li = new List<Employee>();
27. li.Add(new Employee
28. { Id = 1, age = 19, name = "Ritesh", gender = "M" });
29. li.Add(new Employee
30. { Id = 2, age = 20, name = "sujit", gender = "M" });
31. li.Add(new Employee
32. { Id = 3, age = 23, name = "Kabir", gender = "F" });
33. li.Add(new Employee
34. { Id = 4, age = 3, name = "mantu", gender = "F" });
35. li.Add(new Employee
36. { Id = 5, age = 24, name = "Kamlesh", gender = "M" });
37. li.Add(new Employee
38. { Id = 6, age = 28, name = "Manoj", gender = "M" });
39.
40. List<Department> Deli = new List<Department>();
41. Deli.Add(new Department
42. { id = 2, Departments = "IT", Location = "Bangalore" });
43. Deli.Add(new Department
44. { id = 8, Departments = "IT", Location = "Bangalore" });
45. Deli.Add(new Department
46. { id = 3, Departments = "HR", Location = "Bangalore" });
47. Deli.Add(new Department
48. { id = 7, Departments = "HR", Location = "Bangalore" });
49. Deli.Add(new Department
50. { id = 6, Departments = "Account", Location = "Bangalore" });
51.
52. var result = from emp in li join de in Deli on emp.Id equals de.id
53. select new
54. {
55. EmployeeId = emp.Id,
56. EmployeeName = emp.name,
57. Department = de.Departments,
58. Location = de.Location
59. };
60. Console.WriteLine(" ID\t\tName\t\t DepartmentName \t\tLocation");
61. foreach (var obj in result)
62. {
63. Console.WriteLine(obj.EmployeeId + "\t\t" + obj.EmployeeName +
64. "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);
65. }
66. Console.ReadLine();
67. }
68. }

LEFT OUTER JOIN

1. var result = from emp in li join de in Deli on emp.Id equals de.id into tempstorage
2. from dx in tempstorage.DefaultIfEmpty()
3. select new
4. {
5. EmployeeId = emp.Id,
6. EmployeeName = emp.name,
7.
8. Department = (dx != null) ? dx.Departments : "NULL",
9. Location = (dx != null) ? dx.Location : "NULL"
10. };
11. Console.WriteLine("ID\t\tName\t\t DepartmentName \t\tLocation");
12.
13. foreach (var obj in result)
14. {
15. Console.WriteLine(obj.EmployeeId + "\t\t" +
16. obj.EmployeeName + "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);
17. }
18. Console.ReadLine();

RIGHT OUTER JOIN

1. var result = from de in Deli join emp in li on de.id equals emp.Id into tempstorage
2. from dx in tempstorage.DefaultIfEmpty()
3. select new
4. {
5. EmployeeId = (dx != null) ? dx.Id :0,
6. EmployeeName = (dx != null) ? dx.name : "NULL",
7. Department = de.Departments,
8. Location = de.Location
9. };
10. Console.WriteLine("ID\t\tName\t\t DepartmentName \t\tLocation");
11.
12. foreach (var obj in result)
13. {
14. Console.WriteLine(obj.EmployeeId + "\t\t" + obj.EmployeeName +
15. "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);
16. }
17. Console.ReadLine();