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 };

}