IQueryable Left Outer Join

In this example will follow the IQueryable with outer join.

Example:

• We have users 
• Each user has subscriptions
• We want to display users and the count of different subscription that the user has. 
• Count will be an out join sub query

Solution 1: without outer join - should not do this

Here we will first get the users and then get the subscription count using the for each loop. 

       public static async Task GetUsers(this List users)

        {

            var db = ApplicationDbContext.Create();

            users.AddRange(await (from u in db.Users 

                                  select new UserViewModel

                                  {

                                      Id = u.Id,

                                      Email = u.Email,

                                      FirstName = u.FirstName, 

                                      LastName = u.LastName

                                  }).OrderBy(o => o.Email).ToListAsync());

            if (users.Any())

            {

                foreach(var user in users)

                {

                    user.SubscriptionsCount = await (from us in db.UserSubscriptions

                                                     join s in db.Subscriptions on us.SubscriptionId equals s.Id

                                                     where us.UserId.Equals(user.Id)

                                                     select us).CountAsync();

                }

            }

            

        }

Solution 2 : using the sub query

This is a better approach than solution 1 but infact from maintainability purposes, a better approach would be solution 3.

       public static async Task GetUsers(this List users)
        {
            var db = ApplicationDbContext.Create();

            var singleQuery = from u in db.Users 
                              join sub in (from us in db.UserSubscriptions  
                                           join s in db.Subscriptions on us.SubscriptionId equals s.Id 
                                           group us by us.UserId into countGroup
                                           select new { Count = countGroup.Count(), UserId = countGroup.Key }) 
                                on u.Id equals sub.UserId into sub1 
                              from subR in sub1.DefaultIfEmpty() 
                              select new UserViewModel
                              {
                                  Id = u.Id,
                                  Email = u.Email,
                                  FirstName = u.FirstName,
                                  LastName = u.LastName, 
                                  SubscriptionsCount = subR.Count == null ? 0 : subR.Count
                              };

            var siteUsersSub = await (query).OrderBy(o => o.Email).ToListAsync();

            if (siteUsersSub != null && siteUsersSub.Any())
            {
                users.AddRange(siteUsersSub);
            }            
        }

Solution 3 : using separate queries and joining them 

But writing it in single query (solution 2) is not very maintainable. Consider to compose your query from sub-queries defined separately:

       public static async Task GetUsers(this List users)
        {
            var db = ApplicationDbContext.Create();

            var subQuery = from us in db.UserSubscriptions
                           join s in db.Subscriptions on us.SubscriptionId equals s.Id
                           group us by us.UserId into countGroup
                           select new { Count = countGroup.Count(), UserId = countGroup.Key };

            var query = from u in db.Users
                        join sq in subQuery on u.Id equals sq.UserId into sq1 
                        from sqR in sq1.DefaultIfEmpty()
                        select new UserViewModel()
                        {
                            Id = u.Id,
                            Email = u.Email,
                            FirstName = u.FirstName,
                            LastName = u.LastName, 
                            SubscriptionsCount = sqR.Count == null ? 0 : sqR.Count
                        };

            var siteUsers = await(query).OrderBy(o => o.Email).ToListAsync();

            if(siteUsers != null && siteUsers.Any())
            {
                users.AddRange(siteUsers);
            }
        }