LINQtoSQL语句中Join和SelectMany的相关操作

join操作

适用场景：在我们表关系中有一对一关系，一对多关系，多对多关系等。对各个表之间的关系，就用这些实现对多个表的操作。

说明：在join操作中，分别为join(join查询), selectmany(select一对多选择)和groupjoin(分组join查询)。

该扩展方法对两个序列中键匹配的元素进行inner join操作

selectmany

说明：我们在写查询语句时，如果被翻译成selectmany需要满足2个条件。1：查询语句中没有join和into，2：必须出现entityset。在我们表关系中有一对一关系，一对多关系，多对多关系等，下面分别介绍一下。

1.一对多关系(1 to many)：

var q =
    from c in db.customers
    from o in c.orders
    where c.city == "london"
    select o;

语句描述：customers与orders是一对多关系。即orders在customers类中以entityset形式出现。所以第二个from是从c.orders而不是db.orders里进行筛选。这个例子在from子句中使用外键导航选择伦敦客户的所有订单。

var q =
    from p in db.products
    where p.supplier.country == "usa" && p.unitsinstock == 0
    select p;

语句描述：这一句使用了p.supplier.country条件，间接关联了supplier表。这个例子在where子句中使用外键导航筛选其供应商在美国且缺货的产品。生成sql语句为：

select [t0].[productid], [t0].[productname], [t0].[supplierid],
[t0].[categoryid],[t0].[quantityperunit],[t0].[unitprice], 
[t0].[unitsinstock], [t0].[unitsonorder],[t0].[reorderlevel],
[t0].[discontinued] from [dbo].[products] as [t0]
left outer join [dbo].[suppliers] as [t1] on 
[t1].[supplierid] = [t0].[supplierid]
where ([t1].[country] = @p0) and ([t0].[unitsinstock] = @p1)
-- @p0: input nvarchar (size = 3; prec = 0; scale = 0) [usa]
-- @p1: input int (size = 0; prec = 0; scale = 0) [0]

2.多对多关系(many to many)：

var q =
    from e in db.employees
    from et in e.employeeterritories
    where e.city == "seattle"
    select new
    {
        e.firstname,
        e.lastname,
        et.territory.territorydescription
    };

说明：多对多关系一般会涉及三个表(如果有一个表是自关联的，那有可能只有2个表)。这一句语句涉及employees, employeeterritories, territories三个表。它们的关系是1：m：1。employees和territories没有很明确的关系。

语句描述：这个例子在from子句中使用外键导航筛选在西雅图的雇员，同时列出其所在地区。这条生成sql语句为：

select [t0].[firstname], [t0].[lastname], [t2].[territorydescription]
from [dbo].[employees] as [t0] cross join [dbo].[employeeterritories]
as [t1] inner join [dbo].[territories] as [t2] on 
[t2].[territoryid] = [t1].[territoryid]
where ([t0].[city] = @p0) and ([t1].[employeeid] = [t0].[employeeid])
-- @p0: input nvarchar (size = 7; prec = 0; scale = 0) [seattle]

3.自联接关系：

var q =
    from e1 in db.employees
    from e2 in e1.employees
    where e1.city == e2.city
    select new {
        firstname1 = e1.firstname, lastname1 = e1.lastname,
        firstname2 = e2.firstname, lastname2 = e2.lastname,
        e1.city
    };

语句描述：这个例子在select 子句中使用外键导航筛选成对的雇员，每对中一个雇员隶属于另一个雇员，且两个雇员都来自相同城市。生成sql语句为：

select [t0].[firstname] as [firstname1], [t0].[lastname] as 
[lastname1],[t1].[firstname] as [firstname2], [t1].[lastname] as 
[lastname2],[t0].[city] from [dbo].[employees] as [t0],
[dbo].[employees] as [t1] where ([t0].[city] = [t1].[city]) and 
([t1].[reportsto] = [t0].[employeeid])

groupjoin

像上面所说的，没有join和into，被翻译成selectmany，同时有join和into时，那么就被翻译为groupjoin。在这里into的概念是对其结果进行重新命名。

1.双向联接(two way join)：

此示例显式联接两个表并从这两个表投影出结果：

var q =
    from c in db.customers
    join o in db.orders on c.customerid
    equals o.customerid into orders
    select new
    {
        c.contactname,
        ordercount = orders.count()
    };

说明：在一对多关系中，左边是1，它每条记录为c（from c in db.customers），右边是many，其每条记录叫做o ( join o in db.orders )，每对应左边的一个c，就会有一组o，那这一组o，就叫做orders，也就是说，我们把一组o命名为orders，这就是into用途。这也就是为什么在select语句中，orders可以调用聚合函数count。在t-sql中，使用其内嵌的t-sql返回值作为字段值。如图所示：

生成sql语句为：

select [t0].[contactname], (
    select count(*)
    from [dbo].[orders] as [t1]
    where [t0].[customerid] = [t1].[customerid]
) as [ordercount]
from [dbo].[customers] as [t0]

2.三向联接(there way join)：

此示例显式联接三个表并分别从每个表投影出结果：

var q =
    from c in db.customers
    join o in db.orders on c.customerid
    equals o.customerid into ords
    join e in db.employees on c.city
    equals e.city into emps
    select new
    {
        c.contactname,
        ords = ords.count(),
        emps = emps.count()
    };

生成sql语句为：

select [t0].[contactname], (
    select count(*)
    from [dbo].[orders] as [t1]
    where [t0].[customerid] = [t1].[customerid]
) as [ords], (
select count(*)
    from [dbo].[employees] as [t2]
    where [t0].[city] = [t2].[city]
) as [emps]
from [dbo].[customers] as [t0]

3.左外部联接(left outer join)：

此示例说明如何通过使用 此示例说明如何通过使用defaultifempty() 获取左外部联接。在雇员没有订单时，defaultifempty()方法返回null：

var q =
    from e in db.employees
    join o in db.orders on e equals o.employee into ords
    from o in ords.defaultifempty()
    select new
    {
        e.firstname,
        e.lastname,
        order = o
    };

说明：以employees左表，orders右表，orders 表中为空时，用null值填充。join的结果重命名ords，使用defaultifempty()函数对其再次查询。其最后的结果中有个order，因为from o in ords.defaultifempty() 是对ords组再一次遍历，所以，最后结果中的order并不是一个集合。但是，如果没有from o in ords.defaultifempty() 这句，最后的select语句写成select new { e.firstname, e.lastname, order = ords }的话，那么order就是一个集合。

4.投影的let赋值(projected let assignment)：

说明：let语句是重命名。let位于第一个from和select语句之间。

这个例子从联接投影出最终“let”表达式：

var q =
    from c in db.customers
    join o in db.orders on c.customerid
    equals o.customerid into ords
    let z = c.city + c.country
    from o in ords
    select new
    {
        c.contactname,
        o.orderid,
        z
    };

5.组合键(composite key)：

这个例子显示带有组合键的联接：

var q =
    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
    };

说明：使用三个表，并且用匿名类来说明：使用三个表，并且用匿名类来表示它们之间的关系。它们之间的关系不能用一个键描述清楚，所以用匿名类，来表示组合键。还有一种是两个表之间是用组合键表示关系的，不需要使用匿名类。

6.可为null/不可为null的键关系(nullable/nonnullable key relationship)：

这个实例显示如何构造一侧可为 null 而另一侧不可为 null 的联接：

var q =
    from o in db.orders
    join e in db.employees
        on o.employeeid equals
        (int?)e.employeeid into emps
    from e in emps
    select new
    {
        o.orderid,
        e.firstname
    };

order by操作

适用场景：对查询出的语句进行排序，比如按时间排序等等。

说明：按指定表达式对集合排序；延迟，：按指定表达式对集合排序；延迟，默认是升序，加上descending表示降序，对应的扩展方法是orderby和orderbydescending

1.简单形式

这个例子使用 orderby 按雇用日期对雇员进行排序：

var q =
    from e in db.employees
    orderby e.hiredate
    select e;

说明：默认为升序

2.带条件形式

注意：where和order by的顺序并不重要。而在t-sql中，where和order by有严格的位置限制。

var q =
    from o in db.orders
    where o.shipcity == "london"
    orderby o.freight
    select o;

语句描述：使用where和orderby按运费进行排序。

3.降序排序

var q = 
    from p in db.products
    orderby p.unitprice descending
    select p;

4.thenby

语句描述：使用复合的 orderby 对客户进行排序，进行排序：

var q =
    from c in db.customers
    orderby c.city, c.contactname
    select c;

说明：按多个表达式进行排序，例如先按city排序，当city相同时，按contactname排序。这一句用lambda表达式像这样写：

var q = 
    db.customers
    .orderby(c => c.city)
    .thenby(c => c.contactname).tolist();

在t-sql中没有thenby语句，其依然翻译为orderby，所以也可以用下面语句来表达：

var q = 
    db.customers
    .orderby(c => c.contactname)
    .orderby(c => c.city).tolist();

所要注意的是，多个orderby操作时，级连方式是按逆序。 对于降序的，用相应的降序操作符替换即可。

var q = 
    db.customers
    .orderbydescending(c => c.city)
    .thenbydescending(c => c.contactname).tolist();

需要说明的是，orderby操作，不支持按type排序，也不支持匿名类。比如

var q = 
    db.customers
    .orderby(c => new
    {
        c.city,
        c.contactname
    }).tolist();

会被抛出异常。错误是前面的操作有匿名类，再跟orderby时，比较的是类别。比如

var q = 
    db.customers
    .select(c => new
    {
        c.city,
        c.address
    })
    .orderby(c => c).tolist();

如果你想使用orderby(c => c)，其前提条件是，前面步骤中，所产生的对象的类别必须为c#语言的基本类型。比如下句，这里city为string类型。

var q = 
    db.customers
    .select(c => c.city)
    .orderby(c => c).tolist();

5.thenbydescending

这两个扩展方式都是用在orderby/orderbydescending之后的，第一个thenby/thenbydescending扩展方法作为第二位排序依据，第二个thenby/thenbydescending则作为第三位排序依据，以此类推

var q =
    from o in db.orders
    where o.employeeid == 1
    orderby o.shipcountry, o.freight descending
    select o;

语句描述：使用orderby先按发往国家再按运费从高到低的顺序对 employeeid 1 的订单进行排序。

6.带groupby形式

var q = 
    from p in db.products
    group p by p.categoryid into g
    orderby g.key
    select new {
        g.key,
        mostexpensiveproducts =
            from p2 in g
            where p2.unitprice == g.max(p3 => p3.unitprice)
            select p2
    };

语句描述：使用orderby、max 和 group by 得出每种类别中单价最高的产品，并按 categoryid 对这组产品进行排序。