Left join query of user profiles

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();
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 win

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.UserID

That 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))
                               };

StackExchange Code Review Q#1428, answer score: 6