Linq right join on 3 odd object collection

Question

Hi have 3 list of models that holds odd number of elements and their are all joining by userId.

These 3 list of models in the real world it would take data from 3 stored procedures on a execution. I am trying to use Linq to right outer join them, "Which i don't know how to". I know there is a left join in Linq but say if a situation arises where the first list gets populated and then before the second and/or third list gets populated a new user gets added with relevant data for all 3 tables. So the new user data will be on the second and third table but not the first table and I want all the data to be displayed for my ViewModel after i put them to gather with Linq ? This is when you need right joins as you want to show nulls on the left table data.

Would anyone know if there is a way to do a right join for this or is there a better way? Below is the 3 List of object, which I need to put them to gather for my Viewmodel using Linq.

Please do advice.

B

class Program
{
    static void Main(string[] args)
    {
        List<CommissionBrokerReport> dailyReports = new List<CommissionBrokerReport>(new CommissionBrokerReport[]
        { 
            new CommissionBrokerReport { userId  = 101, firstName = "Bruce", lastName = "Wayne", value = 17433.3333M, average = 0M },
            new CommissionBrokerReport { userId  = 303, firstName = "Selina", lastName = "Kyle", value = 7279.13M, average = 0M }
        });
        List<CommissionBrokerReport> weeklyReports = new List<CommissionBrokerReport>(new CommissionBrokerReport[]
        { 
            new CommissionBrokerReport { userId  = 101, value = 0M, average = 7532.9167M },
            new CommissionBrokerReport { userId  =303, value = 0M, average = 0M },
            new CommissionBrokerReport { userId  = 404, value = 33.3333M, average = 666.6666M }
        });
        List<CommissionBrokerReport> monthlyReports = new List<CommissionBrokerReport>(new CommissionBrokerReport[]
        { 
            new CommissionBrokerReport { userId  = 101, value = 37550.0000M, average = 4653.7500M },
            new CommissionBrokerReport { userId  = 303, value = 0M,  average = 0M },
            new CommissionBrokerReport  { userId  = 404, value = 33.3333M, average = 666.6666M },
            new CommissionBrokerReport  { userId  = 505, value = 55.5555M, average = 10000.0000M }
        });
    }
}

Answer 1

This is one way of how you can join 3 list and get the null result for missing records from different list.

var MonthlyWeeklyRpt = 
  from m in monthlyReports
  join w in weeklyReports on m.userId equals w.userId into weeklyrpt
  from w in weeklyrpt.DefaultIfEmpty()
  select new
  {
     MonthlyReports = m,
     WeeklyReports = w
  };


var MonthlyWeeklyDailyRpt = 
 from q in MonthlyWeeklyRpt
 join d in dailyReports on q.MonthlyReports.userId equals d.userId into dailyrpt
 from d in dailyrpt.DefaultIfEmpty()
 select new
 {
   MonthlyReports = q.MonthlyReports,
   WeeklyReports = q.WeeklyReports,
   DailyReports =d
 };

        foreach (var item in MonthlyWeeklyDailyRpt)
        {
            Console.WriteLine("monthly reports");
            Console.WriteLine(item.MonthlyReports.userId + " " + item.MonthlyReports.value);


            if (item.WeeklyReports != null)
            {
                Console.WriteLine("weekly reports");
                Console.WriteLine(item.WeeklyReports.userId + " " + item.WeeklyReports.value);                   
            }
            else
                Console.WriteLine("null weekly report");


            if (item.DailyReports != null)
            {
                Console.WriteLine("daily reports");
                Console.WriteLine(item.DailyReports.userId + " " + item.DailyReports.value);

            }
            else
                Console.WriteLine("null daily report");

            Console.WriteLine(" ");

        }

it will print following output

monthly reports
101 37550.0000
weekly reports
101 0
daily reports
101 17433.3333

monthly reports
303 0
weekly reports
303 0
daily reports
303 7279.13

monthly reports
404 33.3333
weekly reports
404 33.3333
null daily report

monthly reports
505 55.5555
null weekly report
null daily report

I hope it'll help you.