欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

linq用法整理

程序员文章站 2022-04-09 09:21:30
linq用法整理 普通查询 Group by 有条件Group by 嵌套Group Inner Joins Simple key join 简单键值join Composite key join 多条件join Multiple join 多条件join Inner join by using g ......

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