I have the following left join LINQ query that is returning the results that I expect, but it does not feel right. I need ALL records from the UserProfile table.Then the LastWinnerDate is a single record from the winner table (possible multiple records) indicating the DateTime the last record was entered in that table for the user.WinnerCount is the number of records for the user in the winner table (possible multiple records).Video1 is basically a bool indicating there is, or is not a record for the user in the winner table matching on a third table Objective (should be 1 or 0 rows).Quiz1 is same as Video1 matching another record from Objective Table (should be 1 or 0 rows).Video and Quiz is repeated 12 times because they're used for a report, which is to be displayed to a user listing all user records and indicate if they have met the objectives.var objectiveIds = new List<int>();objectiveIds.AddRange(GetObjectiveIds(objectiveName, false));var q =    from up in MetaData.UserProfile    select new RankingDTO    {        UserId = up.UserID,        FirstName = up.FirstName,        LastName = up.LastName,        LastWinnerDate = (            from winner in MetaData.Winner            where objectiveIds.Contains(winner.ObjectiveID)            where winner.Active            where winner.UserID == up.UserID            orderby winner.CreatedOn descending            select winner.CreatedOn).First(),        WinnerCount = (            from winner in MetaData.Winner            where objectiveIds.Contains(winner.ObjectiveID)            where winner.Active            where winner.UserID == up.UserID            orderby winner.CreatedOn descending            select winner).Count(),        Video1 = (            from winner in MetaData.Winner            join o in MetaData.Objective on winner.ObjectiveID equals o.ObjectiveID            where o.ObjectiveNm == Constants.Promotions.SecVideo1            where winner.Active            where winner.UserID == up.UserID            select winner).Count(),        Quiz1 = (            from winner2 in MetaData.Winner            join o2 in MetaData.Objective on winner2.ObjectiveID equals o2.ObjectiveID            where o2.ObjectiveNm == Constants.Promotions.SecQuiz1            where winner2.Active            where winner2.UserID == up.UserID            select winner2).Count(),    };
Left join query of user profiles
c#;.net;linq
It doesn't feel right, because you have a lot of repetitive code.Notice how each property selector's where statement contains        where objectiveIds.Contains(winner.ObjectiveID)        where winner.Active        where winner.UserID == up.UserIDThat means you can refactor this where condition to a join in the containing query.Also, you specify an order by clause, when you are only using the values to get the Count().  This does nothing but take up space and time.I think this query might be more along the line of what you are trying to achieve:        var rankingDtos =                     from user in MetaData.UserProfile                    let userWinners = from winner in MetaData.Winner                                      let objectives = from objective in MetaData.Objective                                                       where winner.ObjectiveID == objective.ObjectiveID                                                       select objective                                      where winner.UserID == user.UserID && winner.Active                                      orderby winner.CreatedOn descending                                      select new                                                 {                                                     Winner = winner,                                                     Objectives = objectives                                                 }                    select new RankingDTO                               {                                   UserId = user.UserID,                                   FirstName = user.FirstName,                                   LastName = user.LastName,                                   LastWinnerDate = userWinners.First().Winner.CreatedOn,                                   WinnerCount = userWinners.Count(x => objectiveIds.Contains(x.Winner.ObjectiveID)),                                   Video1 = userWinners.Count(x => x.Objectives.Any(o => o.ObjectiveNm == Constants.Promotions.SecVideo1)),                                   Quiz1 = userWinners.Count(x => x.Objectives.Any(o => o.ObjectiveNm == Constants.Promotions.SecQuiz1))                               };