各位先進好，想請教multiple tables join的問題
我的資料表關聯與資料範例如下圖：
以下程式碼為MemberToConferences Join Members
public List<EditorMemberViewModel> GetEditorList(int ConferenceId)
{
//列出MemberToConferences為Editor，並排除掉己指派的Editor（MemberToPaper）
List<EditorMemberViewModel> EMV = new List<EditorMemberViewModel>();
db.MemberToConferences
.Where(x => x.ConferenceId == ConferenceId && x.Role == "Editor")
.Join(db.Members,
m => m.MemberId,
x => x.MemberId,
(m, x) => new
{
m,
x,
x.Name,
x.Email,
m.Role
}).ToList()
.ForEach(o => EMV.Add(new EditorMemberViewModel
{
MemberToConference = o.m,
Members = o.x,
Name = o.Name,
Email = o.Email,
RoleName = GetRole(o.Role)
}));
return EMV;
}
ViewModel
public class EditorMemberViewModel
{
public int ConferenceId { get; set; }
public int PaperId { get; set; }
[DisplayName("搜尋：")]
public string Search { get; set; }
public string Title { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string RoleName { get; set; }
public Member Members { get; set; }
public MemberToConference MemberToConference { get; set; }
public List<EditorMemberViewModel> MemberToConferenceList { get; set; }
}
請問要如何join MemberToPaper並排除掉MemberToPaper己指派的Editor身份的User呢？
因為MemberID 1己經被指派到MemberToPaper，所以不用再列出MemberID 1
如範例所示，謝謝各位前輩
已邀請的邦友 {{ invite_list.length }}/5
用 linq 語法比較接近 T-SQL
多個table的JOIN
你的not in看起來是子查詢
SELECT Alljoin.*
FROM Alljoin
Where MemberId NOT IN
(
SELECT MemberId
FROM MemberToPaper
)
var subquery = from a in Alljoin
where !(from b in MemberToPaper select b.MemberId).Contains(a.MemberId)
select a;
大大的寫法我也不知道該如何應用，不知道該從何處去學習及應用呢？謝謝～
應該可以直接複製阿，你會SQL語法吧?我給你的連結有教join阿
目前先改寫成這樣，可以運作，有時間再來試看看大大介紹的的這個方法該如何寫？（我複製上去VS有很多紅，實在不知該怎麼修改）
public List<EditorMemberViewModel> GetAssignList(int ConferenceId, int PaperId, string Role)
{
//列出MemberToConferences為Editor，排除己指派的Editor（MemberToPaper）
List<EditorMemberViewModel> EMV = new List<EditorMemberViewModel>();
string sql = @"SELECT *
FROM MemberToConference AS mtc
JOIN Members AS m ON mtc.MemberId = m.MemberId
WHERE mtc.Role = @Role
AND mtc.ConferenceId = @ConferenceId
AND m.MemberId NOT IN(
SELECT MemberId
FROM MemberToPaper AS mtp
WHERE mtp.Role = @Role
AND mtp.ConferenceId = @ConferenceId
AND mtp.PaperId = @PaperId
)";
var v = db.Database.SqlQuery<EditorMemberViewModel>(sql,
new SqlParameter("ConferenceId", ConferenceId),
new SqlParameter("PaperId", PaperId),
new SqlParameter("Role", Role)).ToList();
v.ForEach(o => EMV.Add(new EditorMemberViewModel
{
Name = o.Name,
Email = o.Email,
RoleName = GetRole(o.Role)
}));
return EMV;
}
感謝各位大大給靈感與方向～謝謝～
LINQ改寫成這樣，測試OK，感謝大大指路～
public List<EditorMemberViewModel> GetAssignList2(int ConferenceId, int PaperId, string Role)
{
//LINQ
List<EditorMemberViewModel> EMV = new List<EditorMemberViewModel>();
var v = from mtc in db.MemberToConferences
join m in db.Members on
mtc.MemberId equals m.MemberId
where mtc.Role == Role && mtc.ConferenceId == ConferenceId
select new { mtc, m };
var subquery = from a in v
where !(from b in db.MemberToPapers
.Where(x => x.ConferenceId == ConferenceId && x.PaperId == PaperId && x.Role == Role) select b.MemberId)
.Contains(a.mtc.MemberId)
select a;
foreach (var o in subquery)
{
EMV.Add(new EditorMemberViewModel
{
Name = o.m.Name,
Email = o.m.Email,
RoleName = GetRole(o.mtc.Role),
MemberId = o.m.MemberId
});
}
return EMV;
}
抱歉我現在才看到我a&b寫反了
讓我偷改一下XD
.Where(x => x.ConferenceId == ConferenceId && x.PaperId == PaperId && x.Role == Role)
這個感覺可以不用加啊
真的不用加也，不是很完全了解這程式碼如何看。
這樣不是代表著在MemberToPapers裡的所有MemberId都會被撈出來嗎？
也就意味著己經存在MemberToPapers裡的所有MemberId都不會被撈出來，不區分ConferenceId、PaperId、Role。
where !(from b in db.MemberToPapers
select b.MemberId).Contains(a.mtc.MemberId)
還是這一段的意思是指在MemberToPapers的所有MemberId（不區分ConferenceId、PaperId、Role），且有包含在a.mtc.MemberId都不會被列出來？
where !(from b in db.MemberToPapers
select b.MemberId).Contains(a.mtc.MemberId)
select a
是指找出b.MemberId中包含a.MemberId的資料，前面加上!就是相反，所以是找出b.MemberId != a.MemberId的資料
select a是列出你在a查找的結果
嗯，跟我想的是一樣的，我更新了一下原PO的資料範例。
當沒有加入WHERE條件式時，如下
.Where(x => x.ConferenceId == ConferenceId && x.PaperId == PaperId && x.Role == Role)
不就代表b.MemberId 1和2都會被列出來，所以b.MemberId != a.MemberId的狀況下會排除掉MemberId＝1和2，最後結果撈出來的應該會是0筆。
但是依範例我們預期的正確結果應該是要撈出MemberId＝2的User，因為MemberId 2還沒被assign Editor角色。
所以才會說以上的程式邏輯竟然可以撈出正確的資料，實在有點不了解？
沒有實際驗證過，可以這樣寫試試看
var result = (from mc in context.MemberToConfrences
join m in context.Member on mc.MemberId equals m.MemberId
join mp in context.MemberToPaper on new { X1 = mc.ConferenceId, X2 = mc.MemberId, X3 = mc.Role } equals new { X1 = mp.ConferenceId, X2 = mp.MemberId, X3 = mp.Role } into t
from mpc in t.DefaultIfEmpty()
where mc.Role == "Editor" && mpc == null
select new { mc.ConferenceId, mc.MemberId, mc.Role, m.Name, m.Email }).ToList();
iThome鐵人賽
看影片追技術