A C# Linq to Sql query that uses SUM, Case When, Group by, outer join, aggregate and defaults
===

I've been searching for possible solutions and attempting this for several hours without luck. Any help will be greatly appreciated.

I've got a Sql statement which I'm trying to put together as a C# LINQ query.

Here is the working SQL:

	SELECT up.UserProfileID
		,up.FirstName
		,up.LastName
		,SUM(CASE WHEN ul.CompletionDate IS NULL THEN 0
			ELSE ISNULL(ul.Score, 0)
		END) AS TotalScore
	FROM dbo.UserProfile up
		LEFT OUTER JOIN dbo.UserLearning ul ON up.UserProfileID = ul.UserProfileID
	WHERE up.ManagerUserProfileID IS NULL
	GROUP BY up.UserProfileID, up.FirstName, up.LastName

I've tried several different ways but seem to end up with either a statement that doesn't return what I want or doesn't execute successfully

My current (non-working) code looks something like this:

    var pd = from up in db.UserProfiles
    	 join ul in db.UserLearnings on up.UserProfileID equals ul.UserProfileID into temp
    	 from upJOINul in temp.DefaultIfEmpty(new UserLearning() { Score = 0 })
    
    	 where up.ManagerUserProfileID.Equals(null)
    
    	 group up by new
    	 {
    		 UserProfileID = up.UserProfileID,
    		 FirstName = up.FirstName,
    		 LastName = up.LastName,
    		 TotalScore = up.UserLearnings.Sum(u => u.Score)
    	 };


Thank you for any help

