iT邦幫忙

0

ASP.NET MVC Join Not In

各位先進好,想請教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
如範例所示,謝謝各位前輩

小魚 iT邦大師 1 級 ‧ 2019-07-16 21:58:34 檢舉
Entity + Linq 我棄權,
其實我覺得Entity限制蠻多的,
如果要比較複雜的功能效率很差...
leo226 iT邦新手 5 級 ‧ 2019-07-17 10:45:00 檢舉
請問小魚大大都是用什麼法方來實作呢?
0
sion
iT邦新手 4 級 ‧ 2019-07-17 10:56:28
最佳解答

用 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;
看更多先前的回應...收起先前的回應...
leo226 iT邦新手 5 級 ‧ 2019-07-17 17:36:22 檢舉

大大的寫法我也不知道該如何應用,不知道該從何處去學習及應用呢?謝謝~

sion iT邦新手 4 級 ‧ 2019-07-17 17:48:50 檢舉

應該可以直接複製阿,你會SQL語法吧?我給你的連結有教join阿

leo226 iT邦新手 5 級 ‧ 2019-07-18 15:16:24 檢舉

目前先改寫成這樣,可以運作,有時間再來試看看大大介紹的的這個方法該如何寫?(我複製上去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;
}

感謝各位大大給靈感與方向~謝謝~

leo226 iT邦新手 5 級 ‧ 2019-07-18 17:24:36 檢舉

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;
}
sion iT邦新手 4 級 ‧ 2019-07-19 09:28:56 檢舉

抱歉我現在才看到我a&b寫反了/images/emoticon/emoticon10.gif
讓我偷改一下XD

sion iT邦新手 4 級 ‧ 2019-07-19 09:30:16 檢舉
.Where(x => x.ConferenceId == ConferenceId && x.PaperId == PaperId && x.Role == Role)

這個感覺可以不用加啊

leo226 iT邦新手 5 級 ‧ 2019-07-19 16:38:16 檢舉

真的不用加也,不是很完全了解這程式碼如何看。
這樣不是代表著在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都不會被列出來?

sion iT邦新手 4 級 ‧ 2019-07-19 17:05:52 檢舉
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查找的結果

leo226 iT邦新手 5 級 ‧ 2019-07-22 10:29:26 檢舉

嗯,跟我想的是一樣的,我更新了一下原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角色。
所以才會說以上的程式邏輯竟然可以撈出正確的資料,實在有點不了解?

0
jimmy310
iT邦見習生 0 級 ‧ 2019-07-17 14:35:49

沒有實際驗證過,可以這樣寫試試看

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();
leo226 iT邦新手 5 級 ‧ 2019-07-17 17:34:59 檢舉

抱歉,我放上去VS太多紅了,不知道該如何修正,請教這樣的寫法專有名詞叫什麼呢?我可以用關鍵字上網google學習一下~謝謝

0
tenno081
iT邦新手 4 級 ‧ 2019-07-18 10:15:44
leo226 iT邦新手 5 級 ‧ 2019-07-18 15:21:17 檢舉

謝謝喔,我以為我用的那個寫法是LINQ,正確來說我的寫法應該是lambda,好像也是屬於LINQ的一種變型寫法~
樓上大大的正規LINQ要如何輸出我找時間來練習看看~

我要發表回答

立即登入回答