Convert SQL query to equivalent LINQ

Question

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()

If you can share the code you've already got, that will help people craft an answer that more closely matches your classes. — StriplingWarrior, Nov 04 '21 at 15:56
Does https://stackoverflow.com/questions/26118064/left-join-with-or-condition-in-linq answer your question? — StriplingWarrior, Nov 04 '21 at 15:58
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Nov 05 '21 at 00:13
@KaushikThanki Regarding your specific question about multiple conditions on a `join` you can take a look at [this answer](https://stackoverflow.com/a/1265008/8607180) — Sasha, Nov 07 '21 at 16:30

score 2 · Accepted Answer · answered Nov 08 '21 at 02:26

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 uses RegimentId. And second, the Linq query uses a filter (on WolfError) 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).

Too bad. As often with these SQL to LINQ conversions you fail to introduce navigation properties instead of these manual error-prone and verbose join statements. Working with an ORM like EF, navigation properties are the way to go. — Gert Arnold, Nov 09 '21 at 07:47

Convert SQL query to equivalent LINQ

1 Answers1