Once I was also a beginner in LINQ and Entity Framework thing , and like all beginners , I too used to think that the queries are executed at the place where we declare them for example consider below lines of code.
var employees= context.Employees.Where(x.Gender==Male); //-----First query
var order=context.Orders.Where(x=>x.Status=="completed"); //------Second query
A beginner would think that as soon as the execution flow comes to the second query the first query gets executed and the employees variable gets filled by the Employee's objects. But this is not the case with LINQ. Queries are not executed at the time we create them. They executes when any of the following situations happens.
1. when we Iterate over a query variable.
2. when we use any conversion operator on the result like ToList(), ToArray(), ToDictionary() etc.
3. when we use a Singleton Method on the query result like Single(), First(),Last(),Min(),Max(), Count(),Average() etc.
Overall, the point is, there are two types of execution in LINQ
• Deferred Execution and
• Immediate Execution
A query is always deferred executed until we explicitly force it to execute immediately by using any conversion operator or singleton operator. Now let's explore these concepts with some examples.
Examples of deferred execution with Entity Framework database first workflow
Step 1. Create a database and some table.
Open SSMS and using visual designer create a database , name it as Organization and create two tables called Employee and Department. If you are not a fan of visual designer and prefer SQL script to generate Database and tables, then copy and paste below code as a new query and run the script.
Create database Organization
go
use Organization
go
CREATE TABLE [dbo].[Department] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Employee] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
[Salary] INT NULL,
[DepartmentId] INT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
);
Go
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (1, N'HR')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (2, N'DEV')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (3, N'BA')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (4, N'UI')
SET IDENTITY_INSERT [dbo].[Department] OFF
Go
Go
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary],[DepartmentId] ) VALUES (1, N'sachin', 100000,1)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary],[DepartmentId] ) VALUES (2, N'arjun', 23000,2)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary],[DepartmentId] ) VALUES (3, N'vikash', 30000,3)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary],[DepartmentId] ) VALUES (4, N'pocha', 43000,4)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary],[DepartmentId] ) VALUES (5, N'Nivedita', 24000,2)
SET IDENTITY_INSERT [dbo].[Employee] OFF
Go
Step 2.Go to visual studio and create a new project , Name it as DatabaseFirstDemo
The primary focus here is to learn Linqwork flow , so for now , i am going to stick to a console app because we don't want to get distracted by the complications of web or desktop app , so to keep things simple and to focus on EF and Linq , create a console application.
• Open visual studio.
• Go to Files ---->New---->Project
• Under New Project window select console application.
• Give the project a name (DatabaseFirstDemo)
• Click Ok
Step 3.Install Entity Framework.
With the new project the first step is to Install EF. To do that , we use package manager console.
• Go to Tools.
• Select Nuget Package Manger and Then Package Manager Console.
• Install Entity Framework using the install-package command.
Install-Package EntityFramework -Version 6.2.0
Step 4. Add Ado.Net Entity Data Model.
• Right Click the project.
• Add ----> Add New Item.
• Under Add new Item window select Data tab and under that select Ado.Net Entity Data Model and click Add.
This is going to be our conceptual Model that represents the mapping between our database tables and domain classes. I have given it a meaningful name as OrganizationModel.
• Under the Entity Data Model wizard, select EF Designer From database , cause we already have a database and click next.
• In the next window select or specify the connection to your database.
1. Specify Server Name
2. Choose Database.
3. Test the connection and
4. Click Ok.
• Change the connection name to OrganizationDbContext and click on Next.
• In the next window select your table and give the model namespace a meaningful name as OrganizationModel and click finish.
Notice :- At this point EF looks at our database and displays the tables, views , stored procedure and Functions , currently we only had tables so we just ticked the table and left the rest as is.
At this point , you will get a security warning like "Running this template can harm your computer" , don't worry about it and just click next. It gives us the warning because visual studio tries to run a template to generate the code.
Unfolding Edmx
EF generates domain models by running some templates, these templates are present under an EDMX file, the extension of templates are .tt. Let's uncover these templates. As you Notice in the above figure, we have two templates.
1. OrganizationModel.Context.tt and
2. OrganizationModel.tt
Here the "tt" stands for template , the first template is responsible for generating DbContext and DBSet while the second template is responsible for generating our domain Entities. This is the reason when you expand the first template that is your context.tt you will find your DbContext class, similarly on expanding Model.tt you will find all your domain Entities.
DbContext Class
At this point, if we expand our context class, we see the actual generated code. This is a plain C# code that EF generates for us. This class derives from the DbContext, so we call it our context class. Remember DbContext is an abstraction over the database. It provides a simple API to load the data from a database or save data to the database.
It has properties of type DbSet. A DbSet represents a Table in our Database. As in our database we had two tables Employee and Department, EF generated two Dbset namely DbSet of Employee and DbSet of Department.
Domain Classes
On expanding the other template that is model.tt we can see our domain Entities. Each domain Entities has properties based on our table columns.
So, the key thing here is, we had started with a database, we had just created our database and imported that into the entity data model and EF took care of all the rest of the work. So, Every time we have a need to change our database we will come to our Edmx and refresh it and EF will automatically update the domain classes.
Example : deferred execution
public class Program
{
public static void Main()
{
var context = new OrganizationDbContext();
var employees = context.Employees; // code doesn't execute here. At this point nothing is send to the database
foreach(var emp in employees)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}",emp.Name,emp.Salary,emp.Department.Name);
Console.WriteLine("---------");
}
Console.ReadLine();
}
}
Note: the code actually executes when we iterate over the query variable inside Foreach block and at this time only, Linq to Entities (ie. Linq Provider) converts our query to corresponding T-SQL and send it to SQL Server. This is called as deferred execution.
Example : Immediate Execution
public class Program
{
public static void Main()
{
var context = new OrganizationDbContext();
var employees = context.Employees.ToList(); // code executes here. At this //point T-SQL is being send to the database
foreach(var emp in employees)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}",emp.Name,emp.Salary,emp.Department.Name);
Console.WriteLine("---------");
}
Console.ReadLine();
}
}
In the above example the query gets immediately executed and all the data come to the memory after that when we iterate over the query variable we actually fetch the data from the in-memory collection.
Although you will get the same result in both situations, each one has its own advantages and disadvantages.
When to use deferred execution
Deferred Execution enables the query to be extended. This means we can store different results in different variables as shown below.
public class Program
{
public static void Main()
{
var context = new OrganizationContext();
var employees = context.Employees;
var filtered = employees.Where(x => x.Salary < 30000); // this is not in-memory filtering , at this time there is nothing in memory.
var ordered = filtered.OrderBy(x => x.Name);
Console.WriteLine("-------------------Without filtering--------------------------");
foreach (var emp in employees)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}", emp.Name, emp.Salary, emp.Department.Name);
}
Console.WriteLine("--------------------------After filtering------------------------");
// after this point everything is in-memory, the first time we iterate over the //employees variable the T-SQL for all 3 statements will be sent the
//server
foreach (var emp1 in filtered)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}", emp1.Name, emp1.Salary, emp1.Department.Name);
}
Console.WriteLine("-----------------------After filtering and Ordering------------------");
foreach (var emp2 in ordered)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}", emp2.Name, emp2.Salary, emp2.Department.Name);
}
Console.ReadLine();
}
}
Output
Notice: in the above example we are able to extend the query as per our requirement. If we would have used Immediate execution then couldn't get three result sets. For example, after getting filtered data we can't get non-filtered data.
public class Program
{
public static void Main()
{
var context = new OrganizationDbContext();
var employees = context.Employees.Where(x=>x.Salary<2000).ToList(); // code executes here. At this point T-SQL is being send to the database
foreach(var emp in employees)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}",emp.Name,emp.Salary,emp.Department.Name);
Console.WriteLine("---------");
}
Console.ReadLine();
}
}
With the above example if we want to get all employees in a variable then it is not possible because we have already received the filtered data. But with differed execution the count of roundtrips to server increases, you can see in the example of deferred execution, there will be three roundtrips to the server for three different results. This may lead to the bad performance of the software. If you don't want to extend the query and just want all ordered filtered results then deferred execution will increase the performance as there will be just one trip to the server and the query will execute on demand.
Example of immediate Execution
public class Program
{
public static void Main()
{
var context = new OrganizationDbContext();
var employees = context.Employees.ToList(); // code executes here. At this point T-SQL is being send to the database
foreach(var emp in employees)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}",emp.Name,emp.Salary,emp.Department.Name);
Console.WriteLine("---------");
}
Console.ReadLine();
}
}
When to use Immediate execution
If you want to filter data based on a custom property then you can't achieve it with deferred execution. consider below example.
Add a new calculated property to the employee entity
public partial class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public Nullable Salary { get; set; }
public Nullable DepartmentId { get; set; }
public virtual Department Department { get; set; }
public bool isPromotable
{
get
{
if(Salary<25000)
{
return true;
}
return false;
}
}
}
Now if we want to check whether an employee is eligible for promotion or not with the custom property then we will get a run time error stating Linq to Entities is not able to translate the isPromotable to T-SQL , this is because isPromotable is a custom property, and Entity Framework have no idea about it.
public class Program
{
public static void Main()
{
var context = new OrganizationDbContext();
var employees = context.Employees.Where(x=>x.isPromotable==true);
foreach(var emp in employees)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}",emp.Name,emp.Salary,emp.Department.Name);
Console.WriteLine("---------");
}
Console.ReadLine();
}
}
The above code will throw a run time error. so, here we can use immediate execution so that all employees first load into the memory and then we filter them on custom property.
public class Program
{
public static void Main()
{
var context = new OrganizationDbContext();
var employees = context.Employees.ToList(); // all data comes to the memory here
var filtered= employees.Where(x=>x.isPromotable==true); // in-memory filtering
foreach(var emp in employees)
{
Console.WriteLine("EmployeeName:{0} | Salary:{1} | Department:{2}",emp.Name,emp.Salary,emp.Department.Name);
Console.WriteLine("---------");
}
Console.ReadLine();
}
}
Don't mix deferred execution with lazy loading, lazing loading is about loading related data on demand we will discuss loading related data in the next article.