Introduction to the ADO.NET Entity Framework using ASP.NET - Digging a bit more into the ADO.NET Entity Framework
(Page 4 of 5 )
In the previous section, navigational properties are fetched using the "Include" method as follows:
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
Dim ctxt As New NorthwindEntities
Dim oEmp As Emp = (From o In ctxt.Emp.Include("Dept") _
Where o.Empno = Me.txtEmpno.Text _
Select o).FirstOrDefault
If oEmp Is Nothing Then
btnClear_Click(Nothing, Nothing)
Me.lblMsg.Text = "Employee not found."
Exit Sub
End If
Me.txtEname.Text = oEmp.Ename
Me.txtSal.Text = oEmp.Sal
Me.txtDeptno.Text = oEmp.Dept.Deptno
End Sub
When we use the "Include" option, we call it "Eager Loading." The above code can be rewritten as follows:
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
Dim ctxt As New NorthwindEntities
Dim oEmp As Emp = (From o In ctxt.Emp _
Where o.Empno = Me.txtEmpno.Text _
Select o).FirstOrDefault
If oEmp Is Nothing Then
Me.lblMsg.Text = String.Format("Employee with Empno: {0} not found.", Me.txtEmpno.Text)
btnClear_Click(Nothing, Nothing)
Exit Sub
End If
Me.txtEname.Text = oEmp.Ename
Me.txtSal.Text = oEmp.Sal
If Not oEmp.DeptReference.IsLoaded Then oEmp.DeptReference.Load()
Me.txtDeptno.Text = oEmp.Dept.Deptno
End Sub
In the above code, we are explicitly executing the "Load" method to retrieve the relationship details. This is called "Explicit Loading."
Similarly, the add method can be rewritten as follows:
Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim ctxt As New NorthwindEntities
Dim oDept As Dept = ctxt.Dept.Where(Function(p) p.Deptno = Me.txtDeptno.Text).FirstOrDefault
If oDept Is Nothing Then
Me.lblMsg.Text = "Invalid Deptno"
Exit Sub
End If
Dim oEmp As New Emp With {.Empno = Me.txtEmpno.Text, .Ename = Me.txtEname.Text, .Sal = Me.txtSal.Text, .Dept = oDept}
ctxt.AddToEmp(oEmp)
ctxt.SaveChanges()
Me.lblMsg.Text = "Added Successfully!"
End Sub
The above is bit different from the previously-provided code. In the above code, there is performance overhead as it retrieves department details from the database (another round trip to the database) even before adding the row to the table.
Another important operation when dealing with database interactions is the transaction. The ADO.NET Entity Framework supports transactions in a very simple manner. The following is the code which executes a transaction:
Protected Sub btnDoTrans_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDoTrans.Click
Try
Dim ctxt As New NorthwindEntities
Dim oDept As New Dept With {.Deptno = 90, .Dname = "Marketing"}
Dim oEmp1 As New Emp With {.Empno = 9001, .Ename = "Jagadish", .Sal = 7000, .Dept = oDept}
Dim oEmp2 As New Emp With {.Empno = 9002, .Ename = "Pulakhandam", .Sal = 5900, .Dept = oDept}
oDept.Emp.Add(oEmp1)
oDept.Emp.Add(oEmp2)
ctxt.AddToDept(oDept)
ctxt.SaveChanges()
Me.lblMsg.Text = "Saved successfully"
Catch ex As Exception
If ex.Message.ToLower.Contains("innerexception") Then
Me.lblMsg.Text = ex.InnerException.Message
Else
Me.lblMsg.Text = ex.Message
End If
End Try
End Sub
You can also observe the Events in SQL Profiler during transaction execution as follows:
Next: What are Entity SQL queries? >>
More ASP.NET Articles
More By Jagadish Chaterjee
