linq用法整理

普通查詢

        var highScores = from student in students
                         where student.ExamScores[exam] > score
                         select new {Name = student.FirstName, Score = student.ExamScores[exam]};

Group by

    var queryLastNames =
        from student in students
        group student by student.LastName into newGroup
        orderby newGroup.Key
        select newGroup;

有條件Group by

    var queryGroupByAverages = from student in students
                               group new { student.FirstName, student.LastName }
                                    by student.ExamScores.Average() > 75 
                                    into studentGroup
                               select studentGroup;

    var queryHighScoreGroups =
        from student in students
        group student by new { FirstLetter = student.LastName[0], Score = student.ExamScores[0] > 85 } 
        into studentGroup
        orderby studentGroup.Key.FirstLetter
        select studentGroup;

嵌套Group

    var queryNestedGroups =
        from student in students
        group student 
        by student.Year 
        into newGroup1
        from newGroup2 in
            (from student in newGroup1
             group student by student.LastName)
        group newGroup2 by newGroup1.Key;

Inner Joins

• Simple key join 簡單鍵值join

    var query = from person in people
                join pet in pets on person equals pet.Owner
                select new { OwnerName = person.FirstName, PetName = pet.Name };

• Composite key join 多條件join

    IEnumerable<string> query =
                                from employee in employees
                                join student in students
                                on new { employee.FirstName, employee.LastName } equals new { student.FirstName, student.LastName }
                                select employee.FirstName + " " + employee.LastName;

• Multiple join 多條件join

    var query = from person in people
                join cat in cats 
                on person equals cat.Owner
                join dog in dogs 
                on new { Owner = person, Letter = cat.Name.Substring(0, 1) } equals new { dog.Owner, Letter = dog.Name.Substring(0, 1) }
                select new { CatName = cat.Name, DogName = dog.Name };

• Inner join by using grouped join 使用分組連接的內聯

    var query1 = 
                 from person in people
                 join pet in pets 
                 on person equals pet.Owner 
                 into gj
                 from subpet 
                 in gj
                 select new { OwnerName = person.FirstName, PetName = subpet.Name };

    var query2 = 
                 from person in people
                 join pet in pets 
                 on person equals pet.Owner
                 select new { OwnerName = person.FirstName, PetName = pet.Name };

left outer joins 左外部聯接

    var query = 
                from person in people
                join pet in pets 
                on person equals pet.Owner 
                into gj
                from subpet in gj.DefaultIfEmpty()
                select new 
                { 
                        person.FirstName, 
                        PetName = subpet?.Name ?? String.Empty 
                };

join 子句的結果進行排序

             var groupJoinQuery2 =
                 from category in categories
                 join prod in products on category.ID equals prod.CategoryID into prodGroup
                 orderby category.Name
                 select new
                 {
                     Category = category.Name,
                     Products = from prod2 in prodGroup
                                orderby prod2.Name
                                select prod2
                 };

Join by using composite keys 多條件 join

var query = 
    from o in db.Orders
    from p in db.Products
    join d in db.OrderDetails
    on new {o.OrderID, p.ProductID} equals new {d.OrderID, d.ProductID} 
    into details
    from d in details
    select new 
    {
        o.OrderID, 
        p.ProductID, 
        d.UnitPrice
    };

在查詢表達式中處理 null 值Handle null values in query expressions

var query1 =
    from c in categories
    where c != null
    join p in products 
    on c.ID equals p?.CategoryID
    select new { Category = c.Name, Name = p.Name };

void TestMethod(Northwind db)
{
    var query =
        from o in db.Orders
        join e in db.Employees
            on o.EmployeeID equals (int?)e.EmployeeID
        select new { o.OrderID, e.FirstName };
}