Why I get exception when I try to fetch data from tables that has many to many relationship

Question

I use the EF6 database first identity 2.0 tables. I try to get users and roles records and using navigation properties for this purpose.

This is my database scheme from VisualStudio:

This is my original database scheme from SQLManagmentStudio:

And here are entities:

    public virtual DbSet<ApplicationGroupRole> ApplicationGroupRoles { get; set; }
    public virtual DbSet<ApplicationGroup> ApplicationGroups { get; set; }
    public virtual DbSet<ApplicationUserGroup> ApplicationUserGroups { get; set; }
    public virtual DbSet<AspNetRole> AspNetRoles { get; set; }
    public virtual DbSet<AspNetUserClaim> AspNetUserClaims { get; set; }
    public virtual DbSet<AspNetUserLogin> AspNetUserLogins { get; set; }
    public virtual DbSet<AspNetUser> AspNetUsers { get; set; }
    

on this row:

var query = context.AspNetUsers.SelectMany(c=>c.AspNetRoles).ToList();  

I get this error:

Invalid object name 'dbo.AspNetUserAspNetRoles'.

Any idea why I get this error?

Answer 1

sounds like you are trying to get data from a table that does not exist in your database. check to see if the table "dbo.AspNetUserAspNetRoles" exist in your database.

This is a similar question on stackoverflow

on second look at your question it look like you are trying to create a many to many relationship between AspNetUser and AspNetRoles Create another POCO class the link the many to many relationship

public class AspNetUserAspnetRole{
public int AspNetRolesId {get;set;}
public int AspNetUserId {get;set;}
public AspNetRoles AspNetRoles {get;set;}
public AspNetUser AspNetUser {get;set;}

}

Add this class to the db context and add migration and update the database then your query should work