【Linq】常用語法彙總

語言繼承查詢(Language Integrated Query, LINQ)，在C#編程語言中集成了查詢語法，可以用相同的語法訪問不同的數據源，LINQ提供了不同數據源的抽象層，所以可以使用相同語法。

public class Book
{
    public int Id { get; set; }
    /// <summary>
    /// 書名
    /// </summary>
    public string BookName { get; set; }
    /// <summary>
    /// 作者id
    /// </summary>
    public int AutherId { get; set; }
    /// <summary>
    /// 類型
    /// </summary>
    public string Type { get; set; }
    /// <summary>
    /// 售價
    /// </summary>
    public decimal Price { get; set; }
    /// <summary>
    /// 銷量
    /// </summary>
    public int Sales { get; set; }
}
public class Auther
{
    public int Id { get; set; }
    /// <summary>
    /// 作者
    /// </summary>
    public string AutherName { get; set; }
}
public class Library
{
    public string Address { get; set; }
    /// <summary>
    /// 書本
    /// </summary>
    public List<Book> BookList { get; set; }
}

1、條件查詢（Where）

 var query = from book in bookList
             where book.Price > 50
             orderby book.Sales descending,book.BookName
             select book;
//等同於
var query = bookList.Where(n => n.Price > 50).OrderByDescending(g => g.Sales).ThenBy(y => y.BookName);

　　需要註意的是，延遲查詢

var ary = new List<string>(){ "Aa", "Bb", "Cc"};

var a1 = ary.Where(n => n.Contains("a"));//["Aa"]

ary.Add("Ga");

a1;//["Aa", "Ga"]

　　解決這個問題，只需要用 ToList();

var ary = new List<string>(){ "Aa", "Bb", "Cc"};

var a1 = ary.Where(n => n.Contains("a")).ToList();//["Aa"]

ary.Add("Ga");

a1;//["Aa"]

　　索引篩選

//銷量大於50且為奇數行
var query = bookList.Where((n, index) => n.Sales > 50 && index%2 != 0);

2、複合查詢（SelectMany）

//查找所有圖書館中的小說
var query = from library in libraryList
            from book in library.BookList
            where book.Type == "小說"
            select book;

//等同於
query = libraryList.SelectMany(n => n.BookList).Where(g => g.Type == "小說");

//組織返回結果
query = libraryList.SelectMany(n => n.BookList,  (n, g) => new {n.Address, g.BookName, g.Sales}).Where(y => y.Sales > 100);

3、集合連接

//內連接
var query1 = from book in bookList
    　　　　　join auther in autherList on book.AutherId equals auther.Id
    　　　　　select new { book.BookName, auther.AutherName };
//組連接
var query2 = from auther in autherList
        　　　join book in bookList on auther.Id equals book.AutherId into items
        　　　select new { auther.AutherName, Books = items };
//左外連接
var query3 = from book in bookList
            join auther in autherList on book.AutherId equals auther.Id into items
            from auther in items.DefaultIfEmpty()
            select new
            {
                book.BookName,
                AutherName = auther == default(Auther) ? "無" : auther.AutherName
            };
//多條件連接
var query4 = from book in bookList
    join auther in autherList on new {name = book.BookName, id = book.AutherId} equals new {name = auther.AutherName, id = auther.Id}
    select book;

4、排序（OrderBy、ThenBy）

var query = from book in bookList
                orderby book.Sales descending, book.AutherId, book.Price descending 
                select book;
//等同於
query = bookList.OrderByDescending(n => n.Sales).ThenBy(g => g.AutherId).ThenByDescending(y => y.Price);

5、分組（GroupBy）

//單一條件分組
var query = from book in bookList
                        group book by book.Type into bs
                        select bs.First();
//等同於
query = bookList.GroupBy(n => n.Type).Select(g => g.First());

//多條件分組
var query = from book in bookList
            group book by new { book.Type, book.AutherId } into bs
            select new
            {
                Type = bs.First().Type,
                AutherId = bs.First().AutherId,
                Count = bs.Count()
            };
//等同於
query = bookList.GroupBy(n => new {n.Type, n.AutherId}).Select(g => new
            {
                Type = g.First().Type,
                AutherId = g.First().AutherId,
                Count = g.Count()
            });

6、合併與分區（Zip、Take、Skip）

int[] numbers = { 1, 2, 3 };
string[] words = { "One", "Two", "Three", "Four" };
//元素依次組合，長度為較小的集合
IEnumerable<string> zip = numbers.Zip(words, (n, g) => n + "=" + g);//["1=One", "2=Two", "3=Three"]

//跳過集合的前n個元素
var skip = words.Skip(3);//["Four"]
//獲取集合的前n個元素,有延遲
var take = numbers.Take(2);//[1, 2]

int pageSize = 3;//每頁容量
int pageNum = 0;//頁數
var page = words.Skip(pageSize * pageNum).Take(pageSize);//["One", "Two", "Three"]

7、集合操作（Distinct、Union、Concat、Intersect、Except）

int[] ary1 = {1, 2, 2, 4, 5};
int[] ary2 = {3, 5, 5, 6, 10, 7};

//合併,自動去重
var union = ary1.Union(ary2);//1, 2, 3, 4, 5, 6, 7, 10
//合併,不會去重
var concat = ary1.Concat(ary2);//1, 2, 2, 4, 5, 3, 5, 5, 6, 10, 7
//去重
var distict = ary1.Distinct();//1, 2, 4, 5
//取交集,自動去重
var intersect = ary1.Intersect(ary2);//5
//取補集,自動去重
var except = ary1.Except(ary2);//1, 2, 4

8、類型篩選（ofType）

object[] data = { "one", 1 , 2 ,"three"};
var query = data.ofType<string>();

9、聚合操作符（Count、Sum、Min、Max、Average、Aggregate）

//個數
var count = bookList.Count(n => n.Sales > 50);
//求和
var sum = bookList.Sum(n => n.Price);
//最小值
var min = bookList.Min(n => n.Sales);
//最大值
var max = bookList.Max(n => n.Price);
//平均值
var average = bookList.Average(n => n.Sales);
//累加,總銷量
var aggregate1 = bookList.Select(n => n.Sales).Aggregate((g, y) => g + y);
//累加，初始值
var aggregate2 = bookList.Select(n => n.Sales).Aggregate(10, (g, y) => g + y);
//累加，初始值，結果處理
var aggregate3 = bookList.Select(n => n.Sales).Aggregate(10, (g, y) => g + y, result => result/100);

10、轉換操作符 （ToArray、ToDictionary、ToList、ToLookup、Cast）

Book[] ary = bookList.ToArray();

List<Book> list = bookList.ToList();

Dictionary<int, Book> dic = bookList.ToDictionary<Book, int>(n => n.Id);

//轉換成LookUp集合,key-以key分組的內部集合
ILookup<string, Book> look = bookList.ToLookup(n => n.Type);

IEnumerable<Book> cast = ary.Cast<Book>();