CODEWITHSUNDEEP
sqlentity-frameworklinq
user2390419
user2390419

Reputation: 61

Linq To entity no full join issue

I have the following SQL script that gives me the results i'm after , i'm having trouble replicating the full join and the count distinct on the activities count:

   select ActivityCount,ActivityComplete, ImagePath, SubjectTitle from
(select 
Count(Distinct(Act.[ActivityID])) as ActivityCount,
Sum(Case when (([OutcomeID] = 1 or [OutcomeID] = 3) and AOU.ActivityUserID=1 ) Then 1 Else 0 End) As ActivityComplete,
Sub.[SubjectImageName] as ImagePath,
Sub.SubjectTitle as SubjectTitle
from [dbo].[UserActivityOutcome] AOU
full join Activity Act on Act.[ActivityID] = AOU.[ActivityID]
left join Category Cat on Act.[CategoryID] = Cat.[CategoryID]
left join Subject Sub on Cat.[SubjectID] = Sub.[SubjectID]
group by Sub.SubjectTitle,  Sub.[SubjectImageName]
) as x

results:

</head>
<body><div class="spacer"><table id="t1"><tr><td class="typeheader" colspan="4">Result Set (2 items)</td></tr><tr><th title="System.Int32">ActivityCount</th><th title="System.Int32">ActivityComplete</th><th title="System.String">ImagePath</th><th title="System.String">SubjectTitle</th></tr><tr><td class="n">25</td><td class="n">3</td><td>Subject 1.png</td><td>Subject 1</td></tr><tr><td class="n">1</td><td class="n">1</td><td>Subject 2.png</td><td>Subject 2</td></tr><tr><td title="Total=26&#xD;&#xA;Average=13" class="columntotal">26</td><td title="Total=4&#xD;&#xA;Average=2" class="columntotal">4</td><td title="Totals" class="columntotal"></td><td title="Totals" class="columntotal"></td></tr></table></div></body>
</html>

my linq looks like this:

 from x in (
    (from Act in Activities 
    join AOU in UserActivityOutcomes on Act.ActivityID equals AOU.ActivityID  into AOU_join
    from AOU in AOU_join.DefaultIfEmpty()
    join ActNA in Activities on AOU.ActivityID equals ActNA.ActivityID into ActNA_join
    from ActNA in ActNA_join.DefaultIfEmpty()
    join Cat in Categories on AOU.Activity.CategoryID equals Cat.CategoryID into Cat_join
    from Cat in Cat_join.DefaultIfEmpty()
    join Sub in Subjects on Cat.SubjectID equals Sub.SubjectID into Sub_join
    from Sub in Sub_join.DefaultIfEmpty()
    group new {Sub, AOU, Act,ActNA} by new {
      Sub.SubjectTitle,
      Sub.SubjectImageName
    } into g
    select new {
      ActivityCount =  g.Distinct().Count(), //g.Count(),
      ActivityComplete = g.Sum(p => (
      (p.AOU.OutcomeID == 1 ||
      p.AOU.OutcomeID == 3)&&
      p.AOU.ActivityUserID == 23 ? 1 : 0)),
      ImagePath = g.Key.SubjectImageName,
      SubjectTitle = g.Key.SubjectTitle
    }))
select new {
  x.ActivityCount,
  x.ActivityComplete,
  x.ImagePath,
  x.SubjectTitle
}

Upvotes: 1

Views: 46

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205909

LINQ full outer join is tricky. The only way it could be simulated is by union of left outer join and right antijoin. Here is IMO the LINQ equivalent of your SQL query:

var query =
    // AOU full join Act
    (from e in (from AOU in UserActivityOutcomes
                join Act in Activities on AOU.ActivityID equals Act.ActivityID into Act_join
                from Act in Act_join.DefaultIfEmpty()
                select new { AOU, Act })
               .Concat
               (from Act in Activities
                join AOU in UserActivityOutcomes on Act.ActivityID equals AOU.ActivityID into AOU_join
                from AOU in AOU_join.DefaultIfEmpty()
                where AOU == null
                select new { AOU, Act })
     let AOU = e.AOU
     let Act = e.Act
     // left join Cat
     join Cat in Categories on Act.CategoryID equals Cat.CategoryID into Cat_join
     from Cat in Cat_join.DefaultIfEmpty()
     // left join Sub
     join Sub in Subjects on Cat.SubjectID equals Sub.SubjectID into Sub_join
     from Sub in Sub_join.DefaultIfEmpty()

     group new { Sub, AOU, Act } by new { Sub.SubjectTitle, Sub.SubjectImageName } into g
     select new
     {
         ActivityCount = g.Where(e => e.Act != null).Select(e => e.Act.ActivityID).Distinct().Count(),
         ActivityComplete = g.Sum(e => (e.AOU.OutcomeID == 1 || e.AOU.OutcomeID == 3) && e.AOU.ActivityUserID == 1 ? 1 : 0),
         ImagePath = g.Key.SubjectImageName,
         SubjectTitle = g.Key.SubjectTitle
    };

Upvotes: 1

Related Questions