Queryable.Join Method ef join

EF join

两张表：

var query = db.Categories         // 第一张表
   .Join(db.CategoryMaps,         // 第二张表
      c => c.CategoryId,          // 主键
      cm => cm.ChildCategoryId,   // 外键
      (c, cm) => new { Category = c, CategoryMap = cm }) // 关联后的结果对象集
   .Select(x => x.Category);  // select 查询结果集

多张表：

var fullEntries = dbContext.tbl_EntryPoint　　//第一张表
    .Join(
        dbContext.tbl_Entry,　　　　　　　　　　//第二张表
        entryPoint => entryPoint.EID,
        entry => entry.EID,
        (entryPoint, entry) => new { entryPoint, entry }
    )
    .Join(
        dbContext.tbl_Title,　　　　　　　　　　//第三张表
        combinedEntry => combinedEntry.entry.TID,
        title => title.TID,
        (combinedEntry, title) => new 
        {
            UID = combinedEntry.entry.OwnerUID,
            TID = combinedEntry.entry.TID,
            EID = combinedEntry.entryPoint.EID,
            Title = title.Title
        }
    )
    .Where(fullEntry => fullEntry.UID == user.UID)
    .OrderBy(p=>p.UID)
    .Take(10);

优点：coding方便

缺点：join多张表的情景，代码冗长

替代方法：创建数据库视图，对视图进行EF查询

Queryable.Join Method

Definition

Namespace:

System.Linq

Assembly:

System.Linq.Queryable.dll

Correlates the elements of two sequences based on matching keys.

Join<TOuter,TInner,TKey,TResult>(IQueryable<TOuter>, IEnumerable<TInner>, Expression<Func<TOuter,TKey>>, Expression<Func<TInner,TKey>>, Expression<Func<TOuter,TInner,TResult>>)

Type Parameters

TOuter

The type of the elements of the first sequence.

TInner

The type of the elements of the second sequence.

TKey

The type of the keys returned by the key selector functions.

TResult

The type of the result elements.

Parameters

outer

IQueryable<TOuter>

The first sequence to join.

inner

IEnumerable<TInner>

The sequence to join to the first sequence.

outerKeySelector

Expression<Func<TOuter,TKey>>

A function to extract the join key from each element of the first sequence.

innerKeySelector

Expression<Func<TInner,TKey>>

A function to extract the join key from each element of the second sequence.

resultSelector

Expression<Func<TOuter,TInner,TResult>>

A function to create a result element from two matching elements.

Returns

IQueryable<TResult>

An IQueryable<T> that has elements of type TResult obtained by performing an inner join on two sequences.

Join返回的结果是IQueryable,后面跟where 的话，生成的sql是不带where的。