لود کردن اطلاعات مرتبط با کلید خارجی

[prans_tork]

سلام

تیبل های زیر رو در نظر بگیرید که به روش Code First طراحی شدن:

تیبل یوزر:

public class ApplicationUser : IdentityUser

    {

        [Required]

        [MaxLength(30, ErrorMessage = ConstMsg.MaxLenghtMsg)]

        [MinLength(3, ErrorMessage = ConstMsg.MinLenghtMsg)]

        public string FName { get; set; }





        [MaxLength(60, ErrorMessage = ConstMsg.MaxLenghtMsg)]

        [MinLength(3, ErrorMessage = ConstMsg.MinLenghtMsg)]

        public string LName { get; set; }





        [NotMapped]

        public string FullName => $"{FName} {LName}";





        public int? SexCode { get; set; }





        [MaxLength(11, ErrorMessage = ConstMsg.MaxLenghtMsg)]

        [MinLength(11, ErrorMessage = ConstMsg.MinLenghtMsg)]

        public string Mobile { get; set; }





        public string NatCode { get; set; }

        public int? CountryID { get; set; }





        public int? ProvinceID { get; set; }





        public int? CityID { get; set; }





        [MaxLength(300, ErrorMessage = ConstMsg.MaxLenghtMsg)]

        [MinLength(3, ErrorMessage = ConstMsg.MinLenghtMsg)]

        public string Address { get; set; }





        [MaxLength(10, ErrorMessage = ConstMsg.MaxLenghtMsg)]

        [MinLength(10, ErrorMessage = ConstMsg.MinLenghtMsg)]

        public string PostalCode { get; set; }

        //public string UserImageID { get; set; }





        public virtual ICollection<TblAsks> TblAsks { get; set; }

        public virtual ICollection<TblBoon> TblBoon { get; set; }

        public virtual ICollection<TblCategory> TblCategory { get; set; }

        public virtual ICollection<TblComments> TblComments { get; set; }

        public virtual ICollection<TblCompanyInfo> TblCompanyInfo { get; set; }

        public virtual ICollection<TblDisCount> TblDisCount { get; set; }

        public virtual ICollection<TblFavorates> TblFavorates { get; set; }

        public virtual ICollection<TblLike> TblLikes { get; set; }

        public virtual ICollection<TblImage> TblImage { get; set; }

        public virtual ICollection<TblLoginHistory> TblLoginHistorys { get; set; }

        public virtual ICollection<TblMenu> TblMenu { get; set; }

        public virtual ICollection<TblNews> TblNews { get; set; }

        public virtual ICollection<TblNewsLetter> TblNewsLetters { get; set; }

        public virtual ICollection<TblNotifys> TblNotifys { get; set; }

        public virtual ICollection<TblOrders> TblOrders { get; set; }

        //public virtual ICollection<TblPostType> TblPostType { get; set; }

        public virtual ICollection<TblPrice> TblPrice { get; set; }

        public virtual ICollection<TblProducts> TblProducts { get; set; }

        public virtual ICollection<TblProductsFeature> TblProductsFeature { get; set; }

        public virtual ICollection<TblProductsWeight> TblProductsWeight { get; set; }

        public virtual ICollection<TblProperties> TblProperties { get; set; }

        public virtual ICollection<TblProperties_Products> TblProperties_Products { get; set; }

        public virtual ICollection<TblRating> TblRatings { get; set; }

        public virtual ICollection<TblServers> TblServers { get; set; }

        public virtual ICollection<TblSettings> TblSettings { get; set; }

        public virtual ICollection<TblShopingCart> TblShopingCarts { get; set; }

        public virtual ICollection<TblSliders> TblSliders { get; set; }

        public virtual ICollection<TblTechnicalProp> TblTechnicalProp { get; set; }

        public virtual ICollection<TblTechnicalProp_Products> TblTechnicalProp_Products { get; set; }

        public virtual ICollection<TblTopics> TblTopics { get; set; }













        [InverseProperty("ApplicationSenderUser")]

        public virtual ICollection<TblMessages> TblMessagesSender { get; set; }





        [InverseProperty("ApplicationReceiverUser")]

        public virtual ICollection<TblMessages> TblMessagesReceiver { get; set; }





        public virtual TblUsersImage TblUsersImage { get; set; }





        public virtual ICollection<TblUsersImage> TblRegUsersImage { get; set; }





        //[ForeignKey(nameof(SexID))]

        [NotMapped]

        public virtual TblSexs TblSexs { get; set; }





        [ForeignKey(nameof(CountryID))]

        public virtual TblCountries TblCountries { get; set; }





        [ForeignKey(nameof(ProvinceID))]

        public virtual TblProvinces TblProvinces { get; set; }





        [ForeignKey(nameof(CityID))]

        public virtual TblCities TblCities { get; set; }

    }

تیبل رول ها:

public class AppliationRole : IdentityRole

    {

        public string AvatorImageURL { get; set; }





        ////[NotMapped]

        [InverseProperty("AppliationSenderRole")]

        public virtual ICollection<TblMessages> TblSenderGroupMessages { get; set; }





        ////[NotMapped]

        [InverseProperty("AppliationReceiverRole")]

        public virtual ICollection<TblMessages> TblReceiverGroupMessages { get; set; }

    }

تیبل meesages:

public class TblMessages

    {

        [Key]

        public int ID { get; set; }

        public string SenderUserID { get; set; }

        public string ReceiverUserID { get; set; }





        public string SenderGroupRoleID { get; set; }

        public string ReceiverGroupRoleID { get; set; }





        [Display(Name = "عنوان")]

        [MaxLength(100)]

        public string Title { get; set; }





        [Display(Name = "متن پیام")]

        public string Text { get; set; }





        //[Display(Name = "خوانده شده")]

        //public bool Read { get; set; }





        [Display(Name = "تاریخ")]

        [DataType(DataType.DateTime)]

        public DateTime Date { get; set; }





        [Display(Name = "آی پی کاربر")]

        [MaxLength(50)]

        public string IP { get; set; }





        public int PriorityCode { get; set; }





        //[Display(Name = "حذف شده")]

        //[DefaultValue(false)]

        //public bool Trushed { get; set; }





        public int TicketID { get; set; }





        [Required]

        public int SenderStatus { get; set; }





        [Required]

        public int ReceiverStatus { get; set; }





        [Required]

        public int SenderGroupStatus { get; set; }





        [Required]

        public int ReceiverGroupStatus { get; set; }





        //[NotMapped]

        [ForeignKey(nameof(SenderGroupRoleID))]       

        public virtual AppliationRole AppliationSenderRole { get; set; }





        //[NotMapped]

        [ForeignKey(nameof(ReceiverGroupRoleID))]

        public virtual  AppliationRole AppliationReceiverRole { get; set; }





        //[NotMapped]

        [ForeignKey(nameof(SenderUserID))]

        public virtual ApplicationUser ApplicationSenderUser { get; set; }





        //[NotMapped]

        [ForeignKey(nameof(ReceiverUserID))]

        public virtual ApplicationUser ApplicationReceiverUser { get; set; }





        [NotMapped]

        [ForeignKey(nameof(PriorityCode))]

        public virtual TblPrioritys TblPrioritys { get; set; }





        [NotMapped]

        [ForeignKey(nameof(SenderStatus))]

        public virtual TblMessageStatus TblSenderUserMessageStatus { get; set; }





        [NotMapped]

        [ForeignKey(nameof(ReceiverStatus))]

        public virtual TblMessageStatus TblReceiverUserMessageStatus { get; set; }





        [NotMapped]

        [ForeignKey(nameof(SenderGroupStatus))]

        public virtual TblMessageStatus TblSenderGroupMessageStatus { get; set; }





        [NotMapped]

        [ForeignKey(nameof(ReceiverGroupStatus))]

        public virtual TblMessageStatus TblReceiverGroupMessageStatus { get; set; }

    }

تیبل استتوس ها:

public class TblMessageStatus

    {

        [Key]

        public int ID { get; set; }





        [Required]

        public int Code { get; set; }





        [Required]

        public string Title { get; set; }





        //[NotMapped]

        public virtual ICollection<TblMessages> TblSenderUserMessages { get; set; }





        //[NotMapped]

        public virtual ICollection<TblMessages> TblReceiverUserMessages { get; set; }





        //[NotMapped]

        public virtual ICollection<TblMessages> TblSenderGroupMessages { get; set; }





        //[NotMapped]

        public virtual ICollection<TblMessages> TblReceiverGroupMessages { get; set; }

    }

و نهایتا: ریلیشن های ساخته شده :

modelBuilder.Entity<TblMessages>()

                .HasOne(a => a.TblPrioritys)

                .WithMany(b => b.TblMessages)

                .HasPrincipalKey(b => b.PrioCode)

                .HasForeignKey(a => a.PriorityCode);





            modelBuilder.Entity<TblMessages>()

                .HasOne(a => a.TblSenderUserMessageStatus)

                .WithMany(b => b.TblSenderUserMessages)

                .HasPrincipalKey(b => b.Code)

                .HasForeignKey(a => a.SenderStatus)

                .OnDelete(DeleteBehavior.Restrict);





            modelBuilder.Entity<TblMessages>()

                .HasOne(a => a.TblReceiverUserMessageStatus)

                .WithMany(b => b.TblReceiverUserMessages)

                .HasPrincipalKey(b => b.Code)

                .HasForeignKey(a => a.ReceiverStatus)

                .OnDelete(DeleteBehavior.Restrict);





            modelBuilder.Entity<TblMessages>()

                .HasOne(a => a.TblSenderGroupMessageStatus)

                .WithMany(b => b.TblSenderGroupMessages)

                .HasPrincipalKey(b => b.Code)

                .HasForeignKey(a => a.SenderGroupStatus)

                .OnDelete(DeleteBehavior.Restrict);





            modelBuilder.Entity<TblMessages>()

                .HasOne(a => a.TblReceiverGroupMessageStatus)

                .WithMany(b => b.TblReceiverGroupMessages)

                .HasPrincipalKey(b => b.Code)

                .HasForeignKey(a => a.ReceiverGroupStatus)

                .OnDelete(DeleteBehavior.Restrict);

حالا مشکل اینه که وقتی کویری زیر انجام میشه:

var q = await db.TblMessages





                .Where(a => a.ReceiverUserID == UserID || qroles.Contains(a.ReceiverGroupRoleID))

                .Where(a => a.ReceiverStatus <= 2 && a.ReceiverGroupStatus <= 2)





                .Include(a => a.ApplicationSenderUser)

                .Include(a => a.AppliationSenderRole)

                .Include(a => a.TblPrioritys)

                .Include(a => a.TblReceiverUserMessageStatus)





                .OrderByDescending(a => a.Date)

                .Skip(MSkip)

                .Take(MTake)





                .ToListAsync();

اولا به جای اینکه فقط ApplicationSenderUser ها (اطلاعات کاربر فرستنده پیام) لود بشن همراه مسیج ها ApplicationReceiverUser ها ( اطلاعات کابر گیرنده پیام) هم لود میشن
دوما:AppliationSenderRole ها ( اطلاعات گروه ارسال کننده پیام) لود نمیشن
و سوما: به جای اینکه فقط TblReceiverUserMessageStatus ها لود بشن هر چهار استاتوس مرتبط لود میشن


1.jpg

مشکل کدها دقیقا کجاست؟؟؟؟؟؟؟؟؟؟؟؟

ممنون از پاسخ دوستان

[prans_tork]

دوستان حرفه ای، نظری ندارن؟؟؟؟