Select records from table with conditions from two tables

Question

I am creating a mini social media and in the home page I have to review posts of the user friends I have three tables

public class Post
{
    [Key]           
    public int Id { get; set; }

    public string Date { get; set; }

    public string Text { get; set; }

    [DisplayName("PicturePath")]
    public string PicturePath { get; set; }

    public int interests { get; set; }

    public int userId { get; set; }


    public User user { get; set; }
}
public class User
{
    public int Id { get; set; }

    [Required]
    [MinLength(8)]
    [MaxLength(25)]        
    public string Name { get; set; }

    [DisplayName("Profile Picture")]
    public string PicturePath { get; set; }

    [Required]
    public string Specialization { get; set; }

    [Required]
    public string BirthDate { get; set; }

    [Required]
    public string Brief { get; set; }

    [Required]
    [EmailAddress]
    public string Email { get; set; }

    [Required]
    [DataType(DataType.Password)]
    [DisplayName("Password")]
    public string PasswordHash { get; set; }

    [DefaultValue(false)]
    public bool EmailConfirmed { get; set; }

    public virtual ICollection<Post> Posts { get; set; }

    public virtual ICollection<FriendShip> friendShip { get; set; }
    public virtual ICollection<ResetPassword> resetPassword { get; set; }

}
public class FriendShip
{

    public int Id { get; set; }

    public int UserId { get; set; }

    public int FriendId { get; set; }

    public string FriendShipDate { get; set; }


    public User user { get; set; }

}

And I am trying to select posts those belong to friends of the logged in user . Every user has friends (friends IDs entered in FriendShip Table)... So I want to preview all posts from the friends . How the query form will be?

Answer 1

Assuming that on your FriendShip table, once a "Friendship" has been made, you're adding two records which is User-Friend (User ID = current user ID) and Friend-User (User ID = friend's User ID).

For the lambda query below, make sure you've assigned loggedInUserId with the ID of the logged in user.

1. First we made an array of the user's friends
2. then we use that array on another query and .contains() which will select all the posts that belong to the IDs in that array.

var friendsList = db.FriendShips.Where(f=>f.UserId == loggedInUserId).Select(f=>f.FriendId)ToArray();
List<Post> friendsPosts = db.Posts.Where(p=>friendsList.contains(p.userId))