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)
Specifically below condition
LEFT OUTER JOIN T_Battalions ON (T_Battalions.BattalionId = T_FighterEnlistments.BattalionId OR T_Battalions.BattalionId = T_FighterTransfers.BattalionId)
I know rest of the part just not sure for above left join with or condition.
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()
CodePudding user response：
You are almost there. What's missing is using Any for your unique left-join-with-or-on-a-joined-set:
- Note: It seems that the original SQL query is not identical to your suggested Linq query. For one, the SQL query uses
BattalionId, while the Linq query usesRegimentId. And second, the Linq query uses a filter (onWolfError) that's not present in the SQL query. My suggested query refers to the original SQL query fields, with the additional filter.
Code:
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 ... }
Now, since it's a bit hard to wrap our head around this without some concrete objects, here's a simplification of the above code, done in 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 {}
Result (just run it, ignore the 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>(Of course, if we'll change one of the Ids in the last join to be Name instead, we'll get a B to be included in the last column).