ef聯表查詢速度_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()).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();

有的時候可能隻需要傳回單個字段，這時候可以用簡單的方法，

傳回單個字段的解決辦法：

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(o=>o.GetString(0));

例二：

var ids = McDB.DBContext.Com_SiteGroup.Select(m => m.Com_SiteGroupID).Where(m => m.Com_StructureID == model.SiteID).ToList(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

(m => new AccidentResponseLevelPlan() {

ConfResponseLevelName = m.GetString("Condition"),

EME_PMPlanOrganizeName = m.GetString("Name"),

HandleTime=m.GetDateTime("HandleTime"),

UserName="111"

});