Sasan Karimi Sasan Karimi - 10 months ago 60
C# Question

Convert Left Outer join with 2 condition in relation to LINQ

I have a TSQL code that has 2 condition in relation and Left Outer join, but I couldn't convert it to LINQ syntax.

SELECT ss.StateID,
Count(CASE WHEN ss.StateID = sr.FromStateID OR ss.StateID = sr.ToStateID THEN 1 END) AS CountState
FROM SupStates ss
LEFT JOIN SupRules sr
ON ss.StateID IN ( sr.FromStateID, sr.ToStateID)

I buit this code but it doesn't return
with no relation:

from ss in this.Data()
from sp in supRule
ss.StateID == sp.FromStateID ||
ss.StateID == sp.ToStateID
group new { ss, sp } by new
StateID = ss.StateID
} into g
select new
StateID = g.Key.StateID,
RuleCount = g.Count()

I want to add StateID with 0 relation in result.Is there any way to rewrite it with LINQ?

Answer Source

The reason your Linq doesn't return any with 0's is because it's doing an inner join.

I believe you can achieve the same with something like (please forgive me if the code doesn't actually run, my linq is a little rusty!):

from ss in this.Data()
select new {
    CountState = (from sp in supRule 
                  where ss.StateID == sp.FromStatusID || ss.StateID == sp.ToStateID 
                  select sp.Id).Count()