將SQL查詢轉換為等效的LINQ-有解無憂

SELECT 
    *
FROM
    T_Fighters   
LEFT OUTER JOIN T_FighterEnlistments ON
    (T_FighterEnlistments.FighterId = T_Fighters.FighterId)
LEFT OUTER JOIN T_FighterTransfers ON 
    (T_FighterTransfers.FighterId = T_Fighters.FighterId)
LEFT OUTER JOIN T_Battalions ON
    (T_Battalions.BattalionId = T_FighterEnlistments.BattalionId OR T_Battalions.BattalionId = T_FighterTransfers.BattalionId)  
LEFT OUTER JOIN T_BattalionSideServed ON
    (T_BattalionSideServed.BattalionSideServedId = T_Battalions.BattalionSideServedId)

具體低于條件

LEFT OUTER JOIN T_Battalions ON
      (T_Battalions.BattalionId = T_FighterEnlistments.BattalionId OR T_Battalions.BattalionId = T_FighterTransfers.BattalionId)

我知道剩下的部分只是不確定左上連接或條件。

 from fighter in T_Fighters
    join fighterEnlistment in T_FighterEnlistments on fighter.FighterId equals fighterEnlistment.FighterId into fighterEnlistments  
                            from subFighterEnlistments in fighterEnlistments.Where(x => !x.WolfError).DefaultIfEmpty()
    join soldierTransfer in T_FighterTransfers on fighter.FighterId equals soldierTransfer.FighterId into soldierTransfers
                            from subsoldierTransfers in soldierTransfers.Where(x=>!x.WolfError).DefaultIfEmpty()        
    join battalions in T_Battalions on subsoldierTransfers.BattalionId equals battalions.BattalionId into battalions
                            from subbattalions in battalions.DefaultIfEmpty()
    join battalionsSideServed in T_RegimentSideServeds on subbattalions.RegimentSideServedId equals battalionsSideServed.RegimentSideServedId into battalionsSideServeds
                            from subbattalionsSideServeds in battalionsSideServeds.DefaultIfEmpty()

uj5u.com熱心網友回復：

你快到了。缺少的是Any用于您獨特的 left-join-with-or-on-a-joined-set：

注意：原始 SQL 查詢似乎與您建議的 Linq 查詢不同。一方面，SQL 查詢使用BattalionId，而 Linq 查詢使用RegimentId。其次，Linq 查詢使用WolfErrorSQL 查詢中不存在的過濾器 (on )。我建議的查詢是指原始 SQL 查詢欄位，帶有附加過濾器。

代碼：

from fighter in T_Fighters
    join fighterEnlistment in T_FighterEnlistments 
    on fighter.FighterId equals fighterEnlistment.FighterId into fighterEnlistments  
        from subFighterEnlistments in fighterEnlistments.Where(x => !x.WolfError).DefaultIfEmpty()
    join fighterTransfer in T_FighterTransfers 
    on fighter.FighterId equals fighterTransfer.FighterId into fighterTransfers
        from subFighterTransfers in fighterTransfers.Where(x=>!x.WolfError).DefaultIfEmpty()        
    from t_battalion in T_Battalions 
      .Where(battalion => subFighterEnlistments.Any(x => x.BattalionId == battalion.BattalionId) || 
                          subsoldierTransfers.Any(x => x.BattalionId == battalion.BattalionId))
      .DefaultIfEmpty()
    join battalionsSideServed in T_BattalionSideServed 
    on t_battalion.BattalionSideServedId equals battalionsSideServed.BattalionSideServedId into battalionsSideServeds
        from subbattalionsSideServeds in battalionsSideServeds.DefaultIfEmpty()
select { ... something ... }

現在，由于在沒有一些具體物件的情況下很難理解這一點，這里是在 LinqPad 中完成的上述代碼的簡化：

void Main()
{
    var a = new [] { new A { Id = "A1", Name = "A1" },  new A { Id = "A2", Name = "A2" } };
    var b = new [] { new B { Id = "B1", Name = "B1" },  new B { Id = "B2", Name = "B2" } };
    var a1b2 = new Base[] { new A { Id = "A1", Name = "A1" },  new B { Id = "B2", Name = "B2" } };
    var b1a2 = new Base[] { new B { Id = "B1", Name = "B1" },  new A { Id = "A2", Name = "A2" } };
    var a_nulls1 = new [] { new A { Id = "A1", Name = null },  new A { Id = null, Name = "A2" } };
    var a_nulls2 = new [] { new B { Id = null, Name = "A1" },  new B { Id = "A2", Name = null } };
    
    var res = 
        from item_a in a
            join item_b in b on item_a.Id equals item_b.Id into joined_ab
                from item_joined_ab in joined_ab.DefaultIfEmpty()
            join item_a1b2 in a1b2 on item_a.Id equals item_a1b2.Id into joined_a_a1b2
                from item_joined_a_a1b2 in joined_a_a1b2.DefaultIfEmpty()
            join item_b1a2 in b1a2 on item_a.Id equals item_b1a2.Id into joined_a_b1a2
                from item_joined_a_b1a2 in joined_a_b1a2.DefaultIfEmpty()
            from item_joined_a1_nulls in a_nulls1
              .Where(a_n => joined_a_a1b2.Any(x => x.Id == a_n.Id) || 
                            joined_a_b1a2.Any(x => x.Name == a_n.Name))
              .DefaultIfEmpty()
            join item_a2_nulls in a_nulls2 on item_joined_a1_nulls.Id equals item_a2_nulls.Id into joined_a_nulls
                from item_joined_a_nulls in joined_a_nulls.DefaultIfEmpty()
        select new { item_joined_ab, item_joined_a_a1b2, item_joined_a_b1a2, item_joined_a1_nulls, item_joined_a_nulls };
        
    res.Dump();

}

class Base 
{ 
    public string Id { get; set; } 
    public string Name { get; set; } 
}
class A : Base {}
class B : Base {}

結果（直接運行，忽略html css）：

顯示代碼片段

body{margin:0.3em 0.3em 0.4em 0.4em;font-family:Verdana;font-size:80%;background:white}p,pre{margin:0;padding:0;font-family:Verdana}table{border-collapse:collapse;border:2px solid #17b;margin:0.3em 0.2em}table.limit{border-bottom-color:#c31}table.expandable{border-bottom-style:dashed}table.error{border-bottom-width:4px}td,th{vertical-align:top;border:1px solid #aaa;padding:0.1em 0.2em;margin:0}th{text-align:left;background-color:#ddd;border:1px solid #777;font-family:tahoma;font-size:.9em;font-weight:bold}th.member{padding:0.1em 0.2em 0.1em 0.2em}td.typeheader{font-family:tahoma;font-weight:bold;background-color:#17b;color:white;padding:0 0.2em 0.15em 0.1em}td.n{text-align:right}a:link.typeheader,a:visited.typeheader,a:link.extenser,a:visited.extenser,a:link.fixedextenser,a:visited.fixedextenser{font-family:tahoma;font-size:.9em;font-weight:bold;text-decoration:none;background-color:#17b;color:white;float:left}a:link.difheader,a:visited.difheader{color:#ff8}a:link.extenser,a:visited.extenser,a:link.fixedextenser,a:visited.fixedextenser{float:right;padding-left:2pt;margin-left:4pt}span.typeglyph,span.typeglyphx{padding:0 0.2em 0 0;margin:0}span.extenser,span.extenserx,span.fixedextenser{margin-top:1.2pt}span.typeglyph,span.extenser,span.fixedextenser{font-family:webdings}span.fixedextenser{display:none;position:fixed;right:6px}td.typeheader:hover .fixedextenser{display:block}span.typeglyphx,span.extenserx{font-family:arial;font-weight:bold;margin:2px}table.group{border:none;margin:0}td.group{border:none;padding:0 0.1em}div.spacer{margin:0.6em 0}table.headingpresenter{border:none;border-left:3px dotted #1a5;margin:.8em 0 1em 0.15em}th.headingpresenter{font-family:Arial;border:none;padding:0 0 0.2em 0.5em;background-color:white;color:green;font-size:1.1em}td.headingpresenter{border:none;padding:0 0 0 0.6em}td.summary{background-color:#def;color:#024;font-family:Tahoma;padding:0 0.1em 0.1em 0.1em}td.columntotal{font-family:Tahoma;background-color:#eee;font-weight:bold;color:#17b;font-size:.9em;text-align:right}span.graphbar{background:#17b;color:#17b;margin-left:-2px;margin-right:-2px}a:link.graphcolumn,a:visited.graphcolumn{color:#17b;text-decoration:none;font-weight:bold;font-family:Arial;font-size:1.1em;letter-spacing:-0.2em;margin-left:0.1em;margin-right:0.2em}a:link.collection,a:visited.collection{color:green}a:link.reference,a:visited.reference{color:blue}i{color:green}em{color:red}.highlight{background:#ff8}.fixedfont{font-family:Consolas,monospace}code{font-family:Consolas}code.xml b{color:blue;font-weight:normal}code.xml i{color:maroon;font-weight:normal;font-style:normal}code.xml em{color:red;font-weight:normal;font-style:normal}span.cc{background:#666;color:white;margin:0 1.5px;padding:0 1px;font-family:Consolas,monospace;border-radius:3px}.difadd{background:#d3f3d3}.difremove{background:#f3d8d8}::-ms-clear{display:none}input,textarea,button,select{font-family:Verdana;font-size:1em;padding:.2em}button{padding:.2em .4em}input,textarea,select{margin:.15em 0}input[type="checkbox"],input[type="radio"]{margin:0 0.4em 0 0;height:0.9em;width:0.9em}input[type="radio"]:focus,input[type="checkbox"]:focus{outline:thin dotted red}.checkbox-label{vertical-align:middle;position:relative;bottom:.07em;margin-right:.5em}fieldset{margin:0 .2em .4em .1em;border:1pt solid #aaa;padding:.1em .6em .4em .6em}legend{padding:.2em .1em}

<div class="spacer"><table id="t1"><tr><td class="typeheader" colspan="5">(2 items)<span class="fixedextenser">4</span></td></tr><tr><th title="UserQuery B">item_joined_ab</th><th title="UserQuery Base">item_joined_a_a1b2</th><th title="UserQuery Base">item_joined_a_b1a2</th><th title="UserQuery A">item_joined_a1_nulls</th><th title="UserQuery B">item_joined_a_nulls</th></tr><tr><td><i>null</i></td><td><table id="t2"><tr><td class="typeheader" colspan="2">A<span class="fixedextenser">4</span></td></tr><tr id="sum3"><td colspan="2" class="summary">UserQuery A</td></tr><tr><th class="member" title="System.String">Id</th><td>A1</td></tr><tr><th class="member" title="System.String">Name</th><td>A1</td></tr></table></td><td><i>null</i></td><td><table id="t4"><tr><td class="typeheader" colspan="2">A<span class="fixedextenser">4</span></td></tr><tr id="sum5"><td colspan="2" class="summary">UserQuery A</td></tr><tr><th class="member" title="System.String">Id</th><td>A1</td></tr><tr><th class="member" title="System.String">Name</th><td><i>null</i></td></tr></table></td><td><i>null</i></td></tr><tr><td><i>null</i></td><td><i>null</i></td><td><table id="t6"><tr><td class="typeheader" colspan="2">A<span class="fixedextenser">4</span></td></tr><tr id="sum7"><td colspan="2" class="summary">UserQuery A</td></tr><tr><th class="member" title="System.String">Id</th><td>A2</td></tr><tr><th class="member" title="System.String">Name</th><td>A2</td></tr></table></td><td><table id="t8"><tr><td class="typeheader" colspan="2">A<span class="fixedextenser">4</span></td></tr><tr id="sum9"><td colspan="2" class="summary">UserQuery A</td></tr><tr><th class="member" title="System.String">Id</th><td><i>null</i></td></tr><tr><th class="member" title="System.String">Name</th><td>A2</td></tr></table></td><td><i>null</i></td></tr></table></div>

（當然，如果我們將Id最后一個連接中的一個 s更改為s Name，我們將B在最后一列中包含一個s ）。

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/353798.html

標籤：实体框架林克

上一篇：Java中==和equals()的區別

下一篇：如何將SQL陳述句轉換為LINQ？