LINQ系列：Linq to Object联接操作符

　　联接是指将一个数据源对象与另一个数据源对象进行关联或联合的操作。这两个数据源对象通过一个共同的值或属性进行关联。
　　LINQ的联接操作符将包含可匹配（或相同）关键字的两个或多个数据源中的值进行匹配。

　　LINQ有两个联接操作符：join和groupjoin。

1. join

　　join操作符类似于T-SQL中的inner join，将一个数据源与另一个数据源相联接，根据两个数据源中相等的值进行匹配。

1>. 原型定义

public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector);

public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector, IEqualityComparer<TKey> comparer);

2>. 示例

var expr = from p in context.Products
           join c in context.Categories on p.CategoryID equals c.CategoryID
where p.CategoryID == 1
select p;

var expr = context.Cities.Join(context.Provinces, p => p.ProvinceID, c => c.ProvinceID, (p, c) => p)
    .Where(c => c.ProvinceID == 5);

var query = from p in context.Products
            join c in context.Categories on p.CategoryID equals c.CategoryID into pc
from c in pc.DefaultIfEmpty()
select new
            {
                p.ProductID,
                p.ProductName,
                p.UnitPrice,
                CategoryName = c == null ? "No Category" : c.CategoryName
            };

 

from c in categories
join Product p in products
on c.CategoryID equals p.CategoryID
select new
{
    c.CategoryName,
    p.ProductID,
    p.ProductName
}

from c in categories
join p in c.Products.Cast<Product>()
on c.CategoryID equals p.CategoryID
select new
{
    c.CategoryName,
    p.ProductID,
    p.ProductName
}

categories.Join(
    products.Cast<Product>(),
    c => c.CategoryID,
    p => p.CategoryID,
    (c, p) => new
    {
        c.CategoryName,
        p.ProductID,
        p.ProductName
    }
)

 

2. GroupJoin

　　GroupJoin操作符常应用于返回“主键对象-外键对象集合”形式的查询，例如“产品类别-此类别下的所有产品”。

1>.原型定义

public static IQueryable<TResult> GroupJoin<TOuter, TInner, TKey, TResult>(this IQueryable<TOuter> outer, IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, IEnumerable<TInner>, TResult>> resultSelector);

public static IQueryable<TResult> GroupJoin<TOuter, TInner, TKey, TResult>(this IQueryable<TOuter> outer, IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, IEnumerable<TInner>, TResult>> resultSelector, IEqualityComparer<TKey> comparer);

2>. 示例

var expr = from c in context.Categories
           join p in context.Products on c.CategoryID equals p.ProductID into r
select new { c.CategoryName, Products = r };
foreach (var item in expr)
{
foreach (var product in item.Products)
    {
        Console.WriteLine(product.ProductName);
    }
}

var expr = context.Categories.GroupJoin(context.Products, 
    c => c.CategoryID,
    p => p.CategoryID, 
    (c, p) => new { c.CategoryName, Products = p });