|
As you can see in the above figure,
Employee and Department object are related to each other by DeptID field. Using
this data model, you can get the employee’s department name from Employee object
like below,
Listing 1
EmployeeInfoDataContext dbEmp = new
EmployeeInfoDataContext();
var LINQQuery = from emp in
dbEmp.Employees
select
emp;
foreach (Employee emp in
LINQQuery)
{
Response.Write("Name:
"+emp.EmpName + "<br>");
Response.Write("Dept: " +
emp.Department.DepartmentName + "<br>");
Response.Write("========================<br>");
}
If you notice the query sent to SQL
server through SQL profiler, the LINQ to SQL engine will send separate query to
get the department name associated with an employee by sending DeptID associated
with the employee.
Refer the below figure,
Since, Employee and Department object
is related to each other the LINQ to SQL will lazy load the Department name
whenever it is accessed like above. This is a wonderful feature the framework
offers when we doesn’t need the related data for every row at once. But, at
times it may become overkill for the application performance if you access large
number of data rows with related data like above. To overcome this difficulty
the framework offers a class called DataLoadOptions which will help the
developers to immediate load the related data whenever the primary object
(Employee) is loaded in LINQ to SQL.
To understand this feature, we will use
the examples discussed in the below articles which uses the same data model
listed above and make the Department to immediate load with the employee object
when displaying it in ListView control.
ListView
Control with LINQ to SQL Class– Part 1
ListView
Control with LINQ to SQL Class– Part 2
The samples in the previous article
will lazy load the department and thus it will degrade the performance when the
data grows massively due to large number of database call. You can see this in
SQL profiler like above. In Part
1 of the above article we have used LINQDataSource control while in Part
2 we have written the LINQ query ourselves. The method BindEmp() in Part
2 uses the same code discussed above to bind the ListView control. Refer the
above articles. I have listed the BindEmp() code below for reference,
Listing 2
public void BindEmp()
{
EmployeeInfoDataContext dbEmp =
new EmployeeInfoDataContext();
var LINQQuery = from emp in
dbEmp.Employees
select
emp;
lvEmployee.DataSource =
LINQQuery;
lvEmployee.DataBind();
}
ListView ItemTemplate
<ItemTemplate>
<span
style="background-color: #FFFBD6;color: #333333;">EmpID:
<asp:Label ID="EmpIDLabel"
runat="server" Text='<%# Eval("EmpID") %>' />
<br />
EmpName:
<asp:Label
ID="EmpNameLabel" runat="server" Text='<%# Eval("EmpName")%>' />
<br />
<%-- DeptID:
<asp:Label ID="DeptIDLabel"
runat="server" Text='<%# Eval("DeptID") %>' />
<br />--%>
Age:
<asp:Label ID="AgeLabel"
runat="server" Text='<%# Eval("Age") %>' />
<br />
Address:
<asp:Label
ID="AddressLabel" runat="server" Text='<%# Eval("Address") %>'/>
<br />
Department:
<asp:Label
ID="DepartmentLabel" runat="server"
Text='<%#
Eval("Department.DepartmentName") %>' />
<br />
<asp:Button ID="EditButton"
runat="server" CommandName="Edit" Text="Edit" />
<asp:Button
ID="DeleteButton" runat="server" CommandName="Delete"
Text="Delete"
/>
<br />
<br />
</span>
</ItemTemplate>
In the above code, the
DepartmentName(bolded) will be lazy loaded when the ListView control renders
every row.
Now, we will go ahead and use
DataLoadOptions to immediate load the Department in the sample.
I. Load
the Dependent or Related data object in single Database call
The DataContext object has a property
called LoadOptions using which we can specify the LINQ to SQL framework to
retrieve the related data with the main target. See the below code,
Listing 3
public void BindEmp()
{
EmployeeInfoDataContext dbEmp =
new EmployeeInfoDataContext();
System.Data.Linq.DataLoadOptions dl = new
System.Data.Linq.DataLoadOptions();
dl.LoadWith<Employee>(d
=> d.Department);
dbEmp.LoadOptions =
dl;
var LINQQuery = from emp in
dbEmp.Employees
select
emp;
lvEmployee.DataSource =
LINQQuery;
lvEmployee.DataBind();
}
The above code(bolded) with
DataLoadOptions will send a join query to get the related data with the main
target. You can now enable the SQL profiler to see this. Refer the below
figure,
As you can see, there is only a single
join query which will be sent to the database by LINQ to SQL framework. I have
listed the query sent to the database below (the above profiler image does not
show it fully).
SELECT [t0].[EmpID], [t0].[EmpName],
[t0].[DeptID], [t0].[Age], [t0].[Address], [t2].[test], [t2].[DeptID] AS
[DeptID2], [t2].[DepartmentName]
FROM [dbo].[Employee] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[DeptID],
[t1].[DepartmentName]
FROM [dbo].[Department] AS
[t1]
) AS [t2] ON [t2].[DeptID] =
[t0].[DeptID]
Note:
You can use the above bolded code in
Listing 1 to immediately load Department name.
|