C#中的LINQ to Objects詳解

IEnumerable<char> query = "Not what you might expect";
var item = 'a';
query = query.Where(c => c != item);
item = 'e';
query = query.Where(c => c != item);
item = 'i';
query = query.Where(c => c != item);
item = 'o';
query = query.Where(c => c != item);
item = 'u';
query = query.Where(c => c != item);

foreach (char c in query)
    Console.Write(c); // 只刪除了'u'----Not what yo might expect

string text = @"Historically, the world of data and the world of objects" +
          @" have not been well integrated. ";

string searchTerm = "data";

//字串轉換成陣列
string[] source = text.Split(new char[] { '.', '?', '!', ' ', ';', ':', ',' }, StringSplitOptions.RemoveEmptyEntries);

// 建立查詢，並忽略大小寫比較 
var matchQuery = from word in source
                 where word.ToLowerInvariant() == searchTerm.ToLowerInvariant()
                 select word;

// 統計匹配數量.  
int wordCount = matchQuery.Count();
Console.WriteLine("{0} occurrences(s) of the search term "{1}" were found.", wordCount, searchTerm);
//1 occurrences(s) of the search term "data" were found.

string text = @"Historically, the world of data and the world of objects " +
    @"have not been well integrated. Programmers work in C# or Visual Basic " +
    @"and also in SQL or XQuery. On the one side are concepts such as classes, " +
    @"objects, fields, inheritance, and .NET Framework APIs. On the other side " +
    @"are tables, columns, rows, nodes, and separate languages for dealing with " +
    @"them. Data types often require translation between the two worlds; there are " +
    @"different standard functions. Because the object world has no notion of query, a " +
    @"query can only be represented as a string without compile-time type checking or " +
    @"IntelliSense support in the IDE. Transferring data from SQL tables or XML trees to " +
    @"objects in memory is often tedious and error-prone.";

// 將文字塊切割成陣列.  
string[] sentences = text.Split(new char[] { '.', '?', '!' });

// 定義搜尋條件，此列表可以執行時動態新增.  
string[] wordsToMatch = { "Historically", "data", "integrated" };

// 去重，取交集後的數量對比  
var sentenceQuery = from sentence in sentences
                    let w = sentence.Split(new char[] { '.', '?', '!', ' ', ';', ':', ',' }, StringSplitOptions.RemoveEmptyEntries)
                    where w.Distinct().Intersect(wordsToMatch).Count() == wordsToMatch.Count()
                    select sentence;

// 執行這個查詢 
foreach (string str in sentenceQuery)
{
    Console.WriteLine(str);
}
//Historically, the world of data and the world of objects have not been well integrated

string aString = "ABCDE99F-J74-12-89A";

// 只選擇數位的字元  
IEnumerable<char> stringQuery =
  from ch in aString
  where Char.IsDigit(ch)
  select ch;

// 執行這個查詢  
foreach (char c in stringQuery)
    Console.Write(c + " ");
//9 9 7 4 1 2 8 9 

// 對上面的查詢呼叫Count方法  
int count = stringQuery.Count();
Console.WriteLine("Count = {0}", count);
//Count = 8

// 選擇第一個「-」之前的所有字元 
IEnumerable<char> stringQuery2 = aString.TakeWhile(c => c != '-');

// 執行第二個查詢
foreach (char c in stringQuery2)
    Console.Write(c);
//ABCDE99F

//根據不同版本的 vs 修改路徑
string startFolder = @"C:Program Files (x86)Microsoft Visual Studio 11.0";
IEnumerable fileList = Directory.GetFiles(startFolder, "*.*", SearchOption.AllDirectories).Select(p => new System.IO.FileInfo(p));
//建立正規表示式來尋找所有的"Visual"
Regex searchTerm = new Regex(@"Visual (Basic|C#|C++|Studio)");

//搜尋每一個「.htm」檔案
//通過 where 找到匹配項
//注意:select 中的變數要求顯示宣告其型別，因為 MatchCollection 不是泛型 IEnumerable 集合
var queryMatchingFiles =
    from file in fileList
    where file.Extension == ".htm"
    let fileText = File.ReadAllText(file.FullName)
    let matches = searchTerm.Matches(fileText)
    where matches.Count > 0
    select new
    {
        name = file.FullName,
        matchedValues = from Match match in matches
                        select match.Value
    };

foreach (var v in queryMatchingFiles)
{
    //修剪匹配找到的檔案中的路徑
    string s = v.name.Substring(startFolder.Length - 1);
    Console.WriteLine(s);

    //輸出找到的匹配值
    foreach (var v2 in v.matchedValues)
    {
        Console.WriteLine("  " + v2);
    }
}

//professional2052License.htm
//  Visual Studio
//  Visual Studio
//   Visual Studio
//VBVBWizardsFrameSetTemplates1033BanToc.htm
//   Visual Studio

//建立資料來源
var names1Text = File.ReadAllLines(@"names1.txt");
var names2Text = File.ReadAllLines(@"names2.txt");

//建立查詢，這裡必須使用方法語法
var query = names1Text.Except(names2Text);

//執行查詢
Console.WriteLine("The following lines are in names1.txt but not names2.txt");
foreach (var name in query)
{
    Console.WriteLine(name);
}

// 建立資料來源
string[] scores = System.IO.File.ReadAllLines(@"scores.csv");

// 可以改為 0~4 的任意值 
int sortField = 1;

Console.WriteLine("Sorted highest to lowest by field [{0}]:", sortField);

//演示從方法返回查詢，這裡執行查詢
foreach (string str in RunQuery(scores, sortField))
{
    Console.WriteLine(str);
}

// Keep the console window open in debug mode.  
Console.WriteLine("Press any key to exit");

// 返回查詢變數，非查詢結果
static IEnumerable<string> RunQuery(IEnumerable<string> source, int num)
{
    // 分割字串來排序  
    var scoreQuery = from line in source
                     let fields = line.Split(',')
                     orderby fields[num] descending
                     select line;

    return scoreQuery;
}

/* Output (if sortField == 1):  
   Sorted highest to lowest by field [1]:  
    116, 99, 86, 90, 94  
    120, 99, 82, 81, 79  
    111, 97, 92, 81, 60  
    114, 97, 89, 85, 82  
    121, 96, 85, 91, 60  
    122, 94, 92, 91, 91  
    117, 93, 92, 80, 87  
    118, 92, 90, 83, 78  
    113, 88, 94, 65, 91  
    112, 75, 84, 91, 39  
    119, 68, 79, 88, 92  
    115, 35, 72, 91, 70  
 */

//資料來源
var lines = File.ReadAllLines(@"spreadsheet1.csv");
//將舊資料的第2列的欄位放到第一位，逆向結合第0列和第1列的欄位
var query = from line in lines
            let t = line.Split(',')
            orderby t[2]
            select $"{t[2]}, {t[1]} {t[0]}";

foreach (var q in query)
{
    Console.WriteLine(q);
}

//寫入檔案
File.WriteAllLines("spreadsheet2.csv", query);

var names1Text = File.ReadAllLines(@"names1.txt");
var names2Text = File.ReadAllLines(@"names2.txt");

//簡單連線，並排序。重複儲存。 
var concatQuery = names1Text.Concat(names2Text).OrderBy(x => x);
OutputQueryResult(concatQuery, "Simple concatenate and sort. Duplicates are preserved:");

//基於預設字串比較器連線，並刪除重複。 
var unionQuery = names1Text.Union(names2Text).OrderBy(x => x);
OutputQueryResult(unionQuery, "Union removes duplicate names:");

//查詢在兩個檔案中出現的名稱 
var intersectQuery = names1Text.Intersect(names2Text).OrderBy(x => x);
OutputQueryResult(intersectQuery, "Merge based on intersect:");

//在每個列表中找到匹配的欄位。使用 concat 將兩個結果合併，然後使用預設的字串比較器進行排序
const string nameMatch = "Garcia";
var matchQuery1 = from name in names1Text
                  let t = name.Split(',')
                  where t[0] == nameMatch
                  select name;
var matchQuery2 = from name in names2Text
                  let t = name.Split(',')
                  where t[0] == nameMatch
                  select name;

var temp = matchQuery1.Concat(matchQuery2).OrderBy(x => x);
OutputQueryResult(temp, $"Concat based on partial name match "{nameMatch}":");


private static void OutputQueryResult(IEnumerable<string> querys, string title)
{
    Console.WriteLine(Environment.NewLine + title);
    foreach (var query in querys)
    {
        Console.WriteLine(query);
    }

    Console.WriteLine($"{querys.Count()} total names in list");
}

//每行 names.csv 包含姓氏，名字，和身份證號，以逗號分隔。例如，Omelchenko,Svetlana,111
var names = File.ReadAllLines(@"names.csv");
//每行 scores.csv 包括身份證號碼和四個測試評分，以逗號分隔。例如，111，97，92，81，60 
var scores = File.ReadAllLines(@"scores.csv");

//使用一個匿名的型別合併資料來源。
//【注意】動態建立一個 int 的考試成績成員列表。
//跳過分割字串中的第一項，因為它是學生的身份證，不是一個考試成績
var students = from name in names
               let t = name.Split(',')
               from score in scores

               let t2 = score.Split(',')
               where t[2] == t2[0]
               select new
               {
                   FirstName = t[0],
                   LastName = t[1],
                   ID = Convert.ToInt32(t[2]),
                   ExamScores = (from scoreAsText in t2.Skip(1)
                                 select Convert.ToInt32(scoreAsText)).ToList()
               };
//顯示每個學生的名字和考試平均成績。
foreach (var student in students)
{
    Console.WriteLine(
        $"The average score of {student.FirstName} {student.LastName} is {student.ExamScores.Average()}.");
}

var names = File.ReadAllLines(@"names.csv");
var scores = File.ReadAllLines(@"scores.csv");

//Name:    Last[0],       First[1],  ID[2]
//          Omelchenko,    Svetlana,  11
//Score:   StudentID[0],  Exam1[1]   Exam2[2],  Exam3[3],  Exam4[4]
//          111,           97,        92,        81,        60

//該查詢基於 id 連線兩個不同的電子試算表
var query = from name in names
            let t1 = name.Split(',')
            from score in scores
            let t2 = score.Split(',')
            where t1[2] == t2[0]
            orderby t1[0]
            select $"{t1[0]},{t2[1]},{t2[2]},{t2[3]},{t2[4]}";

//輸出
OutputQueryResult(query, "Merge two spreadsheets:");

private static void OutputQueryResult(IEnumerable<string> querys, string title)
{
    Console.WriteLine(Environment.NewLine + title);
    foreach (var query in querys)
    {
        Console.WriteLine(query);
    }

    Console.WriteLine($"{querys.Count()} total names in list");
}

var fileA = File.ReadAllLines(@"names1.txt");
var fileB = File.ReadAllLines(@"names2.txt");

//並集：連線並刪除重複的名字
var mergeQuery = fileA.Union(fileB);
//根據姓氏的首字母對姓名進行分組
var query = from name in mergeQuery
            let t = name.Split(',')
            group name by t[0][0] into g
            orderby g.Key
            select g;

//為建立的每個組建立一個新檔案,請注意，需要使用巢狀的foreach迴圈來存取每個組的單個項。 
foreach (var g in query)
{
    var fileName = @"testFile_" + g.Key + ".txt";
    Console.WriteLine(g.Key + ":");

    //寫入檔案
    using (var sw = new StreamWriter(fileName))
    {
        foreach (var name in g)
        {
            sw.WriteLine(name);
            Console.WriteLine(" " + name);
        }
    }
}

var scores = File.ReadAllLines(@"scores.csv");

//指定要計算的列
const int examNum = 3;

//scores.csv 格式:
//Student ID    Exam#1  Exam#2  Exam#3  Exam#4
//111,          97,     92,     81,     60

//+1 表示跳過第一列
//計算單一列
SingleColumn(scores, examNum + 1);
Console.WriteLine();

//計算多列
MultiColumns(scores);

private static void SingleColumn(IEnumerable<string> strs, int examNum)
{
    Console.WriteLine("Single Column Query:");

    //查詢分兩步：
    // 1.分割字串
    // 2.對要計算的列的值轉換為 int
    var query = from str in strs
                let t = str.Split(',')
                select Convert.ToInt32(t[examNum]);

    //對指定的列進行統計
    var average = query.Average();
    var max = query.Max();
    var min = query.Min();

    Console.WriteLine($"Exam #{examNum}: Average:{average:##.##} High Score:{max} Low Score:{min}");
}

private static void MultiColumns(IEnumerable<string> strs)
{
    Console.WriteLine("Multi Column Query:");

    //查詢步驟：
    // 1.分割字串
    // 2.跳過 id 列（第一列）
    // 3.將當前行的每個評分都轉換成 int，並選擇整個序列作為一行結果。 
    var query = from str in strs
                let t1 = str.Split(',')
                let t2 = t1.Skip(1)
                select (from t in t2
                        select Convert.ToInt32(t));

    //執行查詢並快取結果以提高效能
    var results = query.ToList();
    //找出結果的列數
    var count = results[0].Count();

    //執行統計
    //為每一列分數的迴圈執行一次迴圈
    for (var i = 0; i < count; i++)
    {
        var query2 = from result in results
                     select result.ElementAt(i);

        var average = query2.Average();
        var max = query2.Max();
        var min = query2.Min();

        //+1 因為 #1 表示第一次考試
        Console.WriteLine($"Exam #{i + 1} Average: {average:##.##} High Score: {max} Low Score: {min}");
    }

}

// Create the text file.  
string csvString = @"GREAL,Great Lakes Food Market,Howard Snyder,Marketing Manager,(503) 555-7555,2732 Baker Blvd.,Eugene,OR,97403,USA  
HUNGC,Hungry Coyote Import Store,Yoshi Latimer,Sales Representative,(503) 555-6874,City Center Plaza 516 Main St.,Elgin,OR,97827,USA  
LAZYK,Lazy K Kountry Store,John Steel,Marketing Manager,(509) 555-7969,12 Orchestra Terrace,Walla Walla,WA,99362,USA  
LETSS,Let's Stop N Shop,Jaime Yorres,Owner,(415) 555-5938,87 Polk St. Suite 5,San Francisco,CA,94117,USA";  
File.WriteAllText("cust.csv", csvString);  
  
// Read into an array of strings.  
string[] source = File.ReadAllLines("cust.csv");  
XElement cust = new XElement("Root",  
    from str in source  
    let fields = str.Split(',')  
    select new XElement("Customer",  
        new XAttribute("CustomerID", fields[0]),  
        new XElement("CompanyName", fields[1]),  
        new XElement("ContactName", fields[2]),  
        new XElement("ContactTitle", fields[3]),  
        new XElement("Phone", fields[4]),  
        new XElement("FullAddress",  
            new XElement("Address", fields[5]),  
            new XElement("City", fields[6]),  
            new XElement("Region", fields[7]),  
            new XElement("PostalCode", fields[8]),  
            new XElement("Country", fields[9])  
        )  
    )  
);  
Console.WriteLine(cust);