CODEWITHSUNDEEP
c#linqt-sql
Sasan
Sasan

Reputation: 614

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)
GROUP  BY ss.StateID

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

from ss in this.Data()
                             from sp in supRule
                             where
                                 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?

Upvotes: 1

Views: 52

Answers (1)

Mo D
Mo D

Reputation: 559

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 {
    ss.StateID,
    CountState = (from sp in supRule 
                  where ss.StateID == sp.FromStatusID || ss.StateID == sp.ToStateID 
                  select sp.Id).Count()
}

Upvotes: 3

Related Questions