0

I am trying to use the "into" statement with DefaultIfEmpty() for the left outer join, but when I try to join to the 3rd collection, it doesn't like it (can't see /use it / find it )

It doesn't seem to like personRole on the line below

join roleTypes in roles on personRole.ContactRoleTypeId equals roleTypes.ContactRoleTypeId into r2

the query:

findPersonResultsViewModelNew =
         from azed in findPersonViewModel.findPersonResultsViewModel
         join personRole in personContactRoles on azed.PersonID equals personRole.PersonId into r1
         join roleTypes in roles on personRole.ContactRoleTypeId equals roleTypes.ContactRoleTypeId into r2
         from p in r1.DefaultIfEmpty()
         from g in r2.DefaultIfEmpty()
         select
         //…. other code
  • Your order is important. Once you do `into r1`, `personRole` no longer exists. You must immediately do `from personRole in r1.DefaultIfEmpty()` if you want to left join and use `personRole` following. See my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786). – NetMage Jun 26 '19 at 17:38

1 Answers1

0

Change the order of your statements. When doing left join, you must immediately follow the join with a new from...DefaultIfEmpty():

findPersonResultsViewModelNew =
         from azed in findPersonViewModel.findPersonResultsViewModel
         join personRole in personContactRoles on azed.PersonID equals personRole.PersonId into prj
         from personRole in prj.DefaultIfEmpty()
         join roleTypes in roles on personRole.ContactRoleTypeId equals roleTypes.ContactRoleTypeId into rtj
         from roleTypes in rtj.DefaultIfEmpty()
         select
NetMage
  • 26,163
  • 3
  • 34
  • 55
  • Actually , I get a null reference exception on `personRole.ContactRoleTypeId` , on the last join . thoughts? –  Jun 26 '19 at 20:22
  • If you are using LINQ to SQL/EF that shouldn't happen, but if you are using LINQ to Objects, you need `personRole?.ContactRoleTypeId` due to the possibility of `personRole` being `null` if there is nothing to join. – NetMage Jun 26 '19 at 20:29