HOW TO：從多個來源填入物件集合 (LINQ)
更新： 2008 年 7 月
本範例示範如何將不同來源型別的資料合併成新的型別。下列程式碼中的範例會合併字串與整數陣列。不過，相同準則也適用於任何兩個資料來源，包括記憶體中物件 (諸如 LINQ to SQL 查詢結果、ADO.NET 資料集和 XML 文件) 的任何組合。
.gif "注意事項") 注意事項： |
|---|
請不要嘗試將記憶體中資料或檔案系統中的資料，與仍然在資料庫中的資料聯結。這類跨定義域的聯結會產生未定義的結果，原因是針對資料庫查詢和其他類型的來源定義聯結作業的方式可能不同。此外，如果資料庫中的資料量太大，這類作業也可能會導致記憶體不足的例外狀況 (Exception)。若要將資料庫中的資料聯結至記憶體中資料，請先在資料庫查詢中呼叫 ToList 或 ToArray，然後再對傳回的集合執行聯結。 |
若要建立資料檔案
- 依照 HOW TO：從不同的檔案聯結內容 (LINQ) 中的說明，建立新的 Visual C# 或 Visual Basic 專案，然後將下列 names.csv 和 scores.csv 檔案複製到方案資料夾中。
範例
下列範例顯示如何使用具名型別 Student，來儲存將兩個記憶體中字串集合合併得來的資料，這些字串模擬 .csv 格式的試算表資料。第一個字串集合表示學生名稱和學號，而第二個集合表示學生學號 (第一欄) 和四個測驗分數。
Class Student
Public FirstName As String
Public LastName As String
Public ID As Integer
Public ExamScores As List(Of Integer)
End Class
Class PopulateCollections
Shared Sub Main()
' Join content from spreadsheets into a list of Student objectss.
' names.csv contains the student name
' plus an ID number. scores.csv contains the ID and a
' set of four test scores. The following query joins
' the scores to the student names by using ID as a
' matching key, and then projects the results into a new type.
Dim names As String() = System.IO.File.ReadAllLines("../../../names.csv")
Dim scores As String() = System.IO.File.ReadAllLines("../../../scores.csv")
' Name: Last[0], First[1], ID[2], Grade Level[3]
' Omelchenko, Svetlana, 111, 2
' Score: StudentID[0], Exam1[1] Exam2[2], Exam3[3], Exam4[4]
' 111, 97, 92, 81, 60
' This query joins two dissimilar spreadsheets based on common ID value.
' Multiple from clauses are used instead of a join clause
' in order to store results of id.Split.
' Note the dynamic creation of a list of ints for the
' TestScores member. We skip 1 because the first string
' in the array is the student ID, not an exam score.
Dim scoreQuery1 = From name In names _
Let n = name.Split(New Char() {","}) _
From id In scores _
Let s = id.Split(New Char() {","}) _
Where n(2) = s(0) _
Select New Student() _
With {.FirstName = n(0), .LastName = n(1), .ID = n(2), _
.ExamScores = (From scoreAsText In s Skip 1 _
Select Convert.ToInt32(scoreAsText)).ToList()}
' Optional. Store the query results for faster access
' in future queries. May be useful with very large data files.
Dim students As List(Of Student) = scoreQuery1.ToList()
' Display the list contents
' and perform a further calculation
For Each s In students
Console.WriteLine("The average score of " & s.FirstName & " " & _
s.LastName & " is " & s.ExamScores.Average())
Next
' Keep console window open in debug mode.
Console.WriteLine("Press any key to exit.")
Console.ReadKey()
End Sub
End Class
' Output:
'The average score of Adams Terry is 85.25
'The average score of Fakhouri Fadi is 92.25
'The average score of Feng Hanying is 88
'The average score of Garcia Cesar is 88.25
'The average score of Garcia Debra is 67
'The average score of Garcia Hugo is 85.75
'The average score of Mortensen Sven is 84.5
'The average score of O'Donnell Claire is 72.25
'The average score of Omelchenko Svetlana is 82.5
'The average score of Tucker Lance is 81.75
'The average score of Tucker Michael is 92
'The average score of Zabokritski Eugene is 83
class Student
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int ID { get; set; }
public List<int> ExamScores { get; set; }
}
class PopulateCollections
{
static void Main()
{
// These data files are defined in How to: Join Content from Dissimilar Files (LINQ)
string[] names = System.IO.File.ReadAllLines(@"../../../names.csv");
string[] scores = System.IO.File.ReadAllLines(@"../../../scores.csv");
// Merge the data sources using a named type.
// var could be used instead of an explicit type.
// Note the dynamic creation of a list of ints for the
// TestScores member. We skip 1 because the first string
// in the array is the student ID, not an exam score.
IEnumerable<Student> queryNamesScores =
from name in names
let x = name.Split(',')
from score in scores
let s = score.Split(',')
where x[2] == s[0]
select new Student()
{
FirstName = x[0],
LastName = x[1],
ID = Convert.ToInt32(x[2]),
ExamScores = (from scoreAsText in s.Skip(1)
select Convert.ToInt32(scoreAsText)).
ToList()
};
// Optional. Store the newly created student objects in memory
// for faster access in future queries. Could be useful with
// very large data files.
List<Student> students = queryNamesScores.ToList();
// Display the results and perform one further calculation.
foreach (var student in students)
{
Console.WriteLine("The average score of {0} {1} is {2}.",
student.FirstName, student.LastName, student.ExamScores.Average());
}
//Keep console window open in debug mode
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
}
/* Output:
The average score of Adams Terry is 85.25.
The average score of Fakhouri Fadi is 92.25.
The average score of Feng Hanying is 88.
The average score of Garcia Cesar is 88.25.
The average score of Garcia Debra is 67.
The average score of Garcia Hugo is 85.75.
The average score of Mortensen Sven is 84.5.
The average score of O'Donnell Claire is 72.25.
The average score of Omelchenko Svetlana is 82.5.
The average score of Tucker Lance is 81.75.
The average score of Tucker Michael is 92.
The average score of Zabokritski Eugene is 83.
*/
這些範例中的資料來源是使用物件初始設定式進行初始化。查詢中會使用 join 子句將名稱對應至分數。ID 會當成外部索引鍵。不過，在其中一個來源中，ID 是字串，而在另一個來源中，卻是整數。因為 join 會進行相等比較，所以您必須先擷取字串中的 ID，然後再將它轉換為整數。這項作業是以兩個 let 子句完成。第一個 let 子句中的暫時識別項 x 會儲存由三個字串組成的陣列，而這三個字串則是透過在各個空格處分割原始字串所建立。第二個 let 子句中的識別項 n 則會儲存將 ID 子字串轉換為整數的結果。在 select 字串中，則會使用物件初始設定式，以利用兩個來源的資料具現化 (Instantiate) 每個新 Student 物件。
如果您不需要儲存查詢的結果，則匿名型別會比具名型別方便使用。如果要在執行查詢的方法外傳遞查詢結果，則必須使用具名型別。下列範例會執行與上一個範例相同的工作，但使用的是匿名型別而非具名型別：
' This query uses an anonymous type
' Note the dynamic creation of a list of ints for the
' TestScores member. We skip 1 because the first string
' in the array is the student ID, not an exam score.
Dim scoreQuery2 = From name In names _
Let n = name.Split(New Char() {","}) _
From id In scores _
Let s = id.Split(New Char() {","}) _
Where n(2) = s(0) _
Select New With {.Last = n(0), _
.First = n(1), _
.TestScores = (From scoreAsText In s Skip 1 _
Select Convert.ToInt32(scoreAsText)).ToList()}
' Display the list contents
' and perform a further calculation
For Each s In scoreQuery2
Console.WriteLine("The average score of " & s.First & " " & s.Last & " is " & s.TestScores.Average())
Next
// Merge the data sources by using an anonymous type.
// Note the dynamic creation of a list of ints for the
// TestScores member. We skip 1 because the first string
// in the array is the student ID, not an exam score.
var queryNamesScores2 =
from name in names
let x = name.Split(',')
from score in scores
let s = score.Split(',')
where x[2] == s[0]
select new
{
First = x[0],
Last = x[1],
TestScores = (from scoreAsText in s.Skip(1)
select Convert.ToInt32(scoreAsText))
.ToList()
};
// Display the results and perform one further calculation.
foreach (var student in queryNamesScores2)
{
Console.WriteLine("The average score of {0} {1} is {2}.",
student.First, student.Last, student.TestScores.Average());
}
編譯程式碼
建立一個以 .NET Framework 3.5 版為目標的 Visual Studio 專案。根據預設，專案有 System.Core.dll 的參考，以及 System.Linq 命名空間的 using 指示詞 (C#) 或 Imports 陳述式 (Visual Basic)。
請將這段程式碼複製到您的專案，
按 F5 編譯和執行程式。
按任何鍵離開主控台視窗。
請參閱
概念
參考
變更記錄
日期 |
記錄 |
原因 |
|---|---|---|
|
2008 年 7 月 |
加入第二組程式碼範例。 |
內容 Bug 修正。 |