Figured it out...

SELECT     TOP (100) PERCENT u.UserID, u.Username, u.LastName, u.FirstName, r.RoleName AS Type, CONVERT(VARCHAR, ur.EffectiveDate, 1) AS Joined, CONVERT(VARCHAR, 
                      ur.ExpiryDate, 1) AS Expires, upd.Photo, upd.Biography, dbo.Files.FileName AS PhotoFileName, dbo.Files.Folder AS PhotoFolder
FROM         dbo.Files INNER JOIN
                          (SELECT     up.UserID, MAX(CASE WHEN ppd.PropertyName = 'Photo' THEN up.PropertyValue ELSE '' END) AS Photo, 
                                                   MAX(CASE WHEN ppd.PropertyName = 'Biography' THEN up.PropertyValue ELSE '' END) AS Biography
                            FROM          dbo.UserProfile AS up INNER JOIN
                                                   dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID AND ppd.PortalID = 0
                            GROUP BY up.UserID) AS upd ON dbo.Files.FileId = upd.Photo RIGHT OUTER JOIN
                      dbo.Users AS u INNER JOIN
                      dbo.UserRoles AS ur ON u.UserID = ur.UserID INNER JOIN
                      dbo.Roles AS r ON ur.RoleID = r.RoleID AND (ur.ExpiryDate IS NULL OR
                      DATEDIFF(day, GETDATE(), ur.ExpiryDate) >= 0) ON upd.UserID = u.UserID

Scott McCulloch at Ventrian 1-upped it all:
http://www.ventrian.com/blog/getting-...

No Query required!

protected string GetProfileImage(int userId) { return Page.ResolveUrl("~/profilepic.ashx?userid=" + userId.ToString()); }