OpenAccess快速入門07
OpenAccess 快速入門將協助我們熟悉 OpenAccess,第七篇我們將延續前兩篇關聯查詢的內容,繼續說明常見的關聯查詢方式。
在快速入門 05 中,我們從 Employees 出發,取得關聯的 Orders 資料,現在反過來,從 Orders 實體出發,取得關聯的 Employees 資料。一樣在 VerifyRLinq.vb 中,加入新的 GetEmployeeByOrder 函式:
Public Function GetEmployeeByOrder() As IList(Of OrderSummary)
Dim orderData = From x In cxt.Orders
Where x.CUSTOMERID = "MAGAA"
Select New OrderSummary() With {.OrderId = x.ORDERID.ToString(),
.CustomerId = x.CUSTOMERID,
.EmployeeId = x.Employees.EMPLOYEEID,
.EmployeeName = GetFullName(x.Employees.FIRSTNAME, x.Employees.LASTNAME)
}
Return orderData.ToList()
End Function
上述 LINQ,我們從 Orders 出發,為方便測試,所以直接 HardCode 一個顧客編號 MAGAA,然後取回一個 List(Of OrderSummay),把這個顧客編號的所有訂單相關聯的員工資料整理出來。
接下來,在 OpenAccessWebApp01 專案中,我們加入一個新頁面 DisplayEmployeeFilterByOrders.aspx,很簡單拉一個 GridView,然後在 Page_Load 中呼叫上述的函式,將取回的資料繫結、顯示:
DisplayEmployeeFilterByOrders.aspx
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="DisplayEmployeeFilterByOrders.aspx.vb" Inherits="OpenAccessWebApp01.DisplayEmployeeFilterByOrders" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server"></asp:GridView>
</div>
</form>
DisplayEmployeeFilterByOrders.aspx.vb
Imports OpenAccessWebApp01Model
Public Class DisplayEmployeeFilterByOrders
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim obj As New VerifyRLinq()
Dim result = obj.GetEmployeeByOrder()
GridView1.DataSource = result
GridView1.DataBind()
End Sub
End Class
同場加映:使用 Join 取回未設定關聯的實體
實務上,資料表之間可能根本沒有設定關聯,在這種情況下,該如何執行關聯式查詢?這個議題和 ORM 架構無關,當然就和 OpenAccess 無關,不過實務上這種狀況很多,所以來個同場加映,透過 LINQ Join 的方式,查詢沒有設定關聯之兩個實體資料。
為方便大家了解,一樣以 Employees 和 Orders 實體為例,但我們不要透過導覽屬性查詢,改用 Join 的方式處理。在 VerifyRLinq 中加入 GetEmployeeByOrderUsingJoin 函式:
Public Function GetEmployeeByOrderUsingJoin() As IList(Of OrderSummary)
Dim orderData = From x In cxt.Orders
Join e In cxt.Employees On
x.EMPLOYEEID Equals e.EMPLOYEEID
Where x.CUSTOMERID = "MAGAA"
Select New OrderSummary() With {.OrderId = x.ORDERID.ToString(),
.CustomerId = x.CUSTOMERID,
.EmployeeId = x.Employees.EMPLOYEEID,
.EmployeeName = GetFullName(x.Employees.FIRSTNAME, x.Employees.LASTNAME)
}
Return orderData.ToList()
End Function
DisplayEmployeeFilterByOrders.aspx
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="DisplayEmployeeFilterByOrders.aspx.vb" Inherits="OpenAccessWebApp01.DisplayEmployeeFilterByOrders" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
<form id="form1" runat="server">
<div>
使用導覽屬性:<br />
<asp:GridView ID="GridView1" runat="server"></asp:GridView>
</div>
<br />
<div>
使用 Join 語法:<br />
<asp:GridView ID="GridView2" runat="server"></asp:GridView>
</div>
</form>
DisplayEmployeeFilterByOrders.aspx.vb
Imports OpenAccessWebApp01Model
Public Class DisplayEmployeeFilterByOrders
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim obj As New VerifyRLinq()
BindGridView(obj.GetEmployeeByOrder(), GridView1)
BindGridView(obj.GetEmployeeByOrderUsingJoin(), GridView2)
End Sub
Private Shared Sub BindGridView(ByVal src As IList(Of OrderSummary), ByVal gv As GridView)
gv.DataSource = src
gv.DataBind()
End Sub
End Class
結果如下: