EntityFramework连接查询

/// <summary>

/// 获取映射集合

/// </summary>
/// <param name="gsCode">标准</param>
/// <returns>映射集合</returns>
public List<CodeMappingDto> GetCodeMappingList(string gsCode)
{
List<CodeMappingDto> blist=null;
var db = context;//必须是同一个上下文
using (db)
{
//1、方法1
//var query = from a in db.BI_CodeMapping
// join
// b in db.BI_BillTemplate on a.BillStandard equals b.Type
// join c in db.BI_CodeTemplate on a.ProjectType equals c.ProjectType
// join d in db.BI_OverviewTemplate on a.EstimateStandard equals d.Standard
// where a.EstimateStandard == gsCode && a.BillTemplateItem==b.Item
// && a.EstimateCode==d.Identifier && a.ShortCode==c.Item
//方法2
var query = from a in db.BI_CodeMapping
join
b in db.BI_BillTemplate on a.BillStandard equals b.Type
join c in db.BI_CodeTemplate on a.ProjectType equals c.ProjectType
join d in db.BI_OverviewTemplate on a.EstimateStandard equals d.Standard
where a.EstimateStandard == gsCode && a.BillTemplateItem == b.Item
&& a.EstimateCode==d.Identifier && a.ShortCode==c.Item
select new CodeMappingDto
{
ID = a.ID,
BillTemplateItem=b.Item,
BillName = b.Description,
BillStandard=b.Type,
ShortCode=c.Item,
CodeName=c.Description,
ProjectType=c.ProjectType,
EstimateCode=d.Identifier,
EstimateStandard=d.Standard,
EstimateName=d.Description
};
query = query.Where(p => p.EstimateStandard == gsCode);//可动态添加条件
blist = query.Distinct().OrderBy(p => p.ShortCode).ToList();
}
return blist;
}