Left join query of user profiles

Question

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(),
 };

Answer 1

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

Answer 2

What LINQ is this? Entities? ESQL, linq to sql? linq to objects?

If Linq to SQL then:

Try this and see what the resulting SQL is then decide based on the sql, or profile the sql itself on your db?

 /// <summary>
 /// From BReusable
 /// </summary>
 /// <param name="dc"></param>
 /// <remarks>http://www.davidhayden.com/blog/dave/archive/2007/08/17/DataContextLogLoggingLINQToSQLOutputConsoleDebuggerOuputWindow.aspx</remarks>
 public static void SendQueriesToConsole(this DataContext dc)
 {
 dc.Log = Console.Out;
 }