none
How to query database if it consists of many to many mapping? RRS feed

  • Question

  • I am using Entity Framework 6 and my project consists of Fluent Api mappings for my Entites. I used Entity Framework generator to generate classes and mappings.

    I have three tables in database

    -User
    -Roles
    -UserRoles
    

    UserRoles Consists of both UserId and RolesId.

    Entity Framework generator doesnot generate the UserRole mapping class. It puts mapping in the Role class

    I would like to get the name of the role based on UserId. How do I query it.

    Thanks in advance.

    • Moved by CoolDadTx Saturday, June 7, 2014 3:35 PM EF related
    Saturday, June 7, 2014 2:05 PM

Answers

  • Is your database properly configured such that UserRoles.UserId is a foreign key to User and RoleId is a foreign key to Roles?  If not then your database schema does not back what you're trying to do and you should fix it and have the designer regenerate the mappings.

    If the database relationships are correct then did EF generate a navigation property that maps User to multiple roles or vice versa?  If so then you can use that to build the relationships.

    //Get all the roles
    var roles = from u in <yourcontext>.Users select u.Roles

    //Get all the role names
    var names = from r in roles select r.Name

    Of course you can combine them together if you'd like.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Saturday, June 7, 2014 3:35 PM
  • Hi Adldk;

    The following will return a List of UserId's with there collections of Roles

    // Create DbContext, the database name needs to be corrected to the one you are using
    var db = new UsersRolesEntities();
    
    var usersRoles = (from u in db.Users
                      select new UserRolls
                     {
                         UserId = u.UserId,
                         Roles = p.Roles.Select(r => r.Name).ToList()
                     }).ToList();
    
    // Each user will have a concrete class from the database returning UserId and list of role names
    public class UserRolls
    {
        public string UserId { get; set; }
        public List<string> Roles { get; set; }
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Sunday, June 8, 2014 3:27 PM

All replies

  • Is your database properly configured such that UserRoles.UserId is a foreign key to User and RoleId is a foreign key to Roles?  If not then your database schema does not back what you're trying to do and you should fix it and have the designer regenerate the mappings.

    If the database relationships are correct then did EF generate a navigation property that maps User to multiple roles or vice versa?  If so then you can use that to build the relationships.

    //Get all the roles
    var roles = from u in <yourcontext>.Users select u.Roles

    //Get all the role names
    var names = from r in roles select r.Name

    Of course you can combine them together if you'd like.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Saturday, June 7, 2014 3:35 PM
  • Hi Adldk;

    The following will return a List of UserId's with there collections of Roles

    // Create DbContext, the database name needs to be corrected to the one you are using
    var db = new UsersRolesEntities();
    
    var usersRoles = (from u in db.Users
                      select new UserRolls
                     {
                         UserId = u.UserId,
                         Roles = p.Roles.Select(r => r.Name).ToList()
                     }).ToList();
    
    // Each user will have a concrete class from the database returning UserId and list of role names
    public class UserRolls
    {
        public string UserId { get; set; }
        public List<string> Roles { get; set; }
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Sunday, June 8, 2014 3:27 PM