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