Getting records from the database with many to many relationship in asp.net mvc

Question

This is the structure of the database on which i am working.Click on this text.

I want to select the EmailID of the users which belong to a particular CategoryID mentioned in MailStatusTable where the column MailStatus Contains False.

This is the query which i have got so far :

var category = from m in mcontext.MailStatusTable
                             where m.MailStatus == false
                             select new
                             {
                                 CategoryID = m.CategoryID
                             };
            var email_list = from u in mcontext.UserProfiles
                             where u.AnExpert == true
                             where u.AnInstitute == true
                             where category.Contains(u.UserProfileID)

                             select new
                             {
                                 LastName = u.LastName,
                                 EmailU = u.Email
                             };

But my third where condition is incorrect as both the UserProfiles Table and Category Table are connected via a Third Table and that table doesn't have a model class as both the columns in it are foreign keys.

This is the relation which i have defined in my context class :

modelBuilder.Entity<Categories>()
            .HasMany(c => c.CategoriesUserProfile).WithMany(x => x.UserProfileCategories).Map(q => q.MapLeftKey("CategoryID").MapRightKey("UserProfileID").ToTable("UserProfileCategories"));

How Should i pick the EmailID of the users which Belong to a particular category.

Thanks in advance. Sure, These are my models :

public class UserProfiles
{
    public int UserProfileID { get; set; }
    public string LastName { get; set; }
    public bool AnExpert { get; set; }
    public bool AnInstitute { get; set; }
    public bool Client { get; set; }
    public string Email { get; set; }
    public virtual ICollection<Categories> UserProfileCategories{get;set;}
}
public class Categories
{
    public int CategoryID { get; set; }
    public String Name { get; set; }
    public virtual ICollection<UserProfiles> CategoriesUserProfile { get; set; }
    public virtual ICollection<MailStatusTables> CategoriesMailStatusTable { get; set; }

}
public class MailStatusTables
{
    public int MailStatusID { get; set; }
    public bool MailStatus { get; set; }
    public int EnquiryID { get; set; }
    public int CategoryID { get; set; }
    public virtual Categories MailStatusCategory { get; set; }

}

can you post your models? – michael berezin Jan 16 '17 at 05:57 — michael berezin, Jan 16 '17 at 05:57

score 0 · Accepted Answer · answered Jan 15 '17 at 20:25

When writing LINQ queries, most of the times it works best to start with the entities you want the query to return (or the ones that contain the property you want to return). So, in this case, UserProfile:

var email_list = from u in mcontext.UserProfiles
                 where u.AnExpert
                    && u.AnInstitute
                    && u.UserProfileCategories
                        .Any(c => c.MailStatusTables
                                   .Any(ms => !ms.MailStatus))
                 select new
                 {
                     LastName = u.LastName,
                     EmailU = u.Email
                 };

This will return you all user data from users that have at least one category containing at least one non-mail status.

By the way, I would prefer property names like IsExpert or HasMailStatus because these names convey that they are boolean values.

Getting records from the database with many to many relationship in asp.net mvc

1 Answers1