How to get sql result of my table "PartnerAdvertises" only of the users of the user listed in the other table PartnerAdvertiseUsers

Question

User1991482975 posted

Hi,

i have two table in  my model "PartnerAdvertises" and "PartnerAdvertiseUsers" :

 public class PartnerAdvertise
    {
        public int PartnerId { get; set; }
        public virtual Partner ParentPartner { get; set; }

        public int PartnerAdvertiseButtonId { get; set; }
        public virtual Partner ParentPartnerAdvertiseButton { get; set; }

        [Key]
        public int PartnerAdvertiseId { get; set; }

        [Required(ErrorMessageResourceName = "Required", ErrorMessageResourceType = typeof(ViewRes.ValidationStrings))]
        [Display(Name = "PartnerAdvertiseTitle", ResourceType = typeof(ViewRes.NamesPartner))]
        [StringLength(2000)]
        [AllowHtml]
        public string PartnerAdvertiseTitle { get; set; }
}



 public class PartnerAdvertiseUser
    {
        public int PartnerAdvertiseId { get; set; }
        public virtual PartnerAdvertise ParentPartnerAdvertiseForUser { get; set; }

        [Display(Name = "UserId", ResourceType = typeof(ViewRes.NamesPartner))]
        public string UserId { get; set; }

        [Key]
        public int PartnerAdvertiseUserId { get; set; }

        [Display(Name = "PartnerAdvertiseUserRole", ResourceType = typeof(ViewRes.NamesPartner))]
        public string PartnerAdvertiseUserRole { get; set; }

}

The first is my tabel and the other is the list of user which can have access to teh advert.

In my controller how can i show to the logged user the liste of item of "PartnerAdvertises" only if is part of "PartnerAdvertiseUsers"?

[Authorize]
        public ActionResult HairAccountMenuPartner(string accountmenu, int? partnerdeleteid, int? partnernonvisibleid, int? partnervisibleid, int? page)
        {


            var partners = db.Partners as IQueryable<Partner>;
            var userstr = User.Identity.GetUserId();
            partners = partners.Where(r =>???? == userstr);

            //Not hidden by admin and Not deleted by user
            //partners = partners.Where(r => r.PartnerVisible.Equals(true));
            partners = partners.Where(r => r.PartnerDelete.Equals(false));

            //SORTING 
            partners = partners.OrderBy(r => r.PartnerTitle);

            //FIRST 250
            partners = partners.Take(250);

            //Pagination
            int pageSize = 4;
            int pageNumber = (page ?? 1);

            return PartialView(partners.ToPagedList(pageNumber, pageSize));

        }

Knowing that each advert can have many users which can manage it..

Thanks for the help

Answer 1

User1120430333 posted

You would have to save the user-id of user saving a record in PartnerAdvertise I would think. That would mean you could only allow the user yo update or delete the record only if the user-id matches the user-id the originally saved the record.

var useridstr = User.Identity.GetUserId.ToString();
            partners = partners.Where(r => r.userid == useridstr);

Answer 2

User1686398519 posted

Hi grafic.web,  

According to your description, it seems that one user can manage multiple advertisements, and one advertisement can be managed by multiple users, which means that PartnerAdvertiseUser and PartnerAdvertise are in a many-to-many relationship.If this is the case, you need to modify your code as follows.

You just use Include to load related entities.

Model

    public class PartnerAdvertise
    {
        public int PartnerId { get; set; }
        public Partner ParentPartner { get; set; }
        public int PartnerAdvertiseButtonId { get; set; }
        public Partner ParentPartnerAdvertiseButton { get; set; }
        [Key]
        public int PartnerAdvertiseId { get; set; }
        [Required]
        [Display(Name = "PartnerAdvertiseTitle")]
        [StringLength(2000)]
        public string PartnerAdvertiseTitle { get; set; }
        public ICollection<PartnerAdvertiseUser> PartnerAdvertiseUserList { get; set; }
    }
    public class PartnerAdvertiseUser
    {
        public ICollection<PartnerAdvertise> PartnerAdvertiseList { get; set; }
        [Display(Name = "UserId")]
        public string UserId { get; set; }
        [Key]
        public int PartnerAdvertiseUserId { get; set; }
        [Display(Name = "PartnerAdvertiseUserRole")]
        public string PartnerAdvertiseUserRole { get; set; }
    }

Controller

var userstr =1;//Assuming that userstr is the UserId of the logged-in user
var partnerAdvertiseUserWithAdvertiseList = db.PartnerAdvertiseUser
       .Include("PartnerAdvertiseList")
       .Where(m=>m.UserId== userstr.ToString()).ToList();

//testlist:One user can manage multiple advertisements, and one advertisement can be managed by multiple users
var testlist = db.PartnerAdvertise.Include("PartnerAdvertiseUserList").ToList();
var testlist2 = db.PartnerAdvertiseUser.Include("PartnerAdvertiseList").ToList();

Here is the result. 

Best Regards,

YihuiSun

Answer 3

User1991482975 posted

I CREATED A NEW POST BECAUSE MY MODEL HAS CHANGED

https://forums.asp.net/t/2172937.aspx?+many+to+many+relationship+show+partner+only+if+users+is+listend+in+PartenerUSers+Table

Any help?