C# Model Query returns no result because Joins may not have records

I have a query and I cannot remember. I guess my real question is, Do I need the extra Joins in this query and if yes them how do I handle the fact that Users may not have Posts, Tasks, or Notifications. I know that you can use coalesce but in this instance it doesn't appear to like it. So How do I get a successful Query if no records are found in the tables of the joins.

In the controller I do a redirect

if (modelInstance == null)
{
   return RedirectToAction("Index", "Dashboard");
}

If I remove the joins it will populate the View Page. My guess is because there are no records in these joins - Yet..

Here is my Query:

 public class ProfileViewModel
{
    public static ProfileViewModel GetUserProfile(string id, GeneralEntities db)
    {
        var qUser = from usr in db.Users
                    join post in db.Posts on usr.AspNetUsersId equals post.CreatedBy
                    join task in db.Tasks on usr.AspNetUsersId equals task.UsersId
                    join notif in db.Notifications on usr.AspNetUsersId equals notif.UsersId
                    where (usr.AspNetUsersId == id)
                    select new ProfileViewModel
                    {
                        AspNetUsersId = usr.AspNetUsersId,
                        FirstName = usr.FirstName,
                        LastName = usr.LastName,
                        ProfileImage = usr.ProfileImage,
                        Title = usr.Title,
                        Education = usr.Education,
                        Skills = usr.Skills,
                        About = usr.About,
                        IsFemale = usr.IsFemale,
                        IsMale = usr.IsMale,
                        IsStaff = usr.IsStaff
                    };

        var result = qUser.FirstOrDefault();

        if (result != null)
        {
            result.PostResults = db.Posts.Where(x => x.CreatedBy == result.AspNetUsersId);
            result.TaskResults = db.Tasks.Where(x => x.UsersId == result.AspNetUsersId);
            result.NotificationResults = db.Notifications.Where(x => x.UsersId == result.AspNetUsersId);
        };

        return result;
    }

    public string UsersId { get; set; }
    public string AspNetUsersId { get; set; }
    [Display(Name = "F Name")]
    public string FirstName { get; set; }
    [Display(Name = "L Name")]
    public string LastName { get; set; }
    [Display(Name = "Profile Image")]
    public string ProfileImage { get; set; }
    [Display(Name = "Job Title")]
    public string Title { get; set; }
    [Display(Name = "Education")]
    public string Education { get; set; }
    [Display(Name = "Skills")]
    public string Skills { get; set; }
    [Display(Name = "About")]
    public string About { get; set; }
    public bool? IsStaff { get; set; }
    public bool? IsMale { get; set; }
    public bool? IsFemale { get; set; }
    public virtual IEnumerable<Post> PostResults { get; set; }
    public virtual IEnumerable<Tasks> TaskResults { get; set; }
    public virtual IEnumerable<Notification> NotificationResults { get; set; }
}

I also tried the .Where(x => x.UserId != null) but that did not work either.

thanks for your help!



Read more here: https://stackoverflow.com/questions/67013079/c-sharp-model-query-returns-no-result-because-joins-may-not-have-records

Content Attribution

This content was originally published by Scott Purtan at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: