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&lt;int&gt;();
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(),
    };
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 &amp;&amp; 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 =&gt; objectiveIds.Contains(x.Winner.ObjectiveID)),
                                   Video1 = userWinners.Count(x =&gt; x.Objectives.Any(o =&gt; o.ObjectiveNm == Constants.Promotions.SecVideo1)),
                                   Quiz1 = userWinners.Count(x =&gt; x.Objectives.Any(o =&gt; o.ObjectiveNm == Constants.Promotions.SecQuiz1))
                               };