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

  • 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

    Sunday, December 13, 2020 1:10 PM

All replies

  • 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);
    
    Sunday, December 13, 2020 11:01 PM
  • 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

    Monday, December 14, 2020 3:03 AM
  • Monday, December 14, 2020 8:33 AM