ef6.0 如何实现联表查询

 

ef联表查询是使用EntityFramwork框架的项目都会用到，下面给的一个联表查询示例，几乎可以满足项目中所有的联表查询逻辑：

var list = DBContext.Demo.Demo_User.Select(Demo.DataAcess.Demo.Demo_UserType, DataAcess.Demo.Demo_UserType1, (t, f, s) => t.Field(t.All, f.All, s.All))

.Where(Demo.DataAcess.Demo.Demo_UserType, p => p.UserTypeName == "测试用户类型3")

.Join(Demo.DataAcess.Demo.Demo_UserType, (t, f) => t.UserTypeID == f.UserTypeID, JoinType.LEFTOUTER)

.Join(Demo.DataAcess.Demo.Demo_UserType1, (t, f) => t.UserTypeID == f.UserTypeID, JoinType.RIGHTOUTER).ToList();

 

list.Select(m => m.ToDynamicMapper<Demo_UserModel>()).ToList(); //通过自动映射转换成业务model

在后续的开发过程居然发现以上的方法居然不行，以下是新的解决办法 

返回多个字段的解决办法：

var list = McDB.DBContext.Com_SiteGroup.Select(McDB.Com_SiteType, (t, f) => t.Field(t.All, f.SiteTypeName))
.Join(McDB.Com_UserInSite, (t1, t2) => t1.Com_SiteGroupID == t2.Com_SiteGroupID)
.Join(McDB.Com_SiteType, (t, f) => t.Com_SiteTypeID == f.Com_SiteTypeID)
.Where(McDB.Com_UserInSite, t => t.Com_UserInfoID == userId && t.Com_PositionID == positionID).ToList<SiteGroupAddModel2>(); 

 

有的时候可能只需要返回单个字段，这时候可以用简单的方法，

返回单个字段的解决办法：

var list = McDB.DBContext.Com_SiteGroupInStructure.Select(McDB.Com_Structure,(m,n)=>m.Field(n.ShortName))
.Join(McDB.Com_SiteGroup, (t, f) => t.Com_SiteGroupID == f.Com_SiteGroupID)
.Join(McDB.Com_Structure, (t, f) => t.Com_StructureID == f.Com_StructureID)
.Where(McDB.Com_SiteGroup, m => m.Com_SiteGroupID == siteGroupId).ToList<string>(o=>o.GetString(0));

例二： 

var ids = McDB.DBContext.Com_SiteGroup.Select(m => m.Com_SiteGroupID).Where(m => m.Com_StructureID == model.SiteID).ToList<Guid>(o=>o.GetGuid(0));

 

有的时候需要返回一个对象

var responsePlan = LepulsDB.DBContext.ACC_AccidentResponseLevelPlan
.Select(LepulsDB.ACC_ConfResponseLevel, LepulsDB.EMS_PMPlanOrganize,
(t, f, z) => t.Field(t.All, f.Condition, z.Name))
.Join(LepulsDB.ACC_ConfResponseLevel, (t, f) => t.Acc_ConfResponseLevelID == f.ACC_ConfResponseLevelID)
.Join(LepulsDB.EMS_PMPlanOrganize, (t, f) => t.EME_PMPlanOrganize == f.EMS_PMPlanOrganizeID)
.Where(m => m.ACC_AccidentResponseLevelPlanID == responseLevelPlanID)
.ToSingleObject<AccidentResponseLevelPlan>
(m => new AccidentResponseLevelPlan() {
ConfResponseLevelName = m.GetString("Condition"),
EME_PMPlanOrganizeName = m.GetString("Name"),
HandleTime=m.GetDateTime("HandleTime"),
UserName="111"
});