none
Query Many-to many relation RRS feed

  • Question

  • Hello,

    I have a question about creating a Linq query, I dont have a lot of experience with yet, therefore I am looking for some guidance. I checked out the 101 LINQ examples but was not able to translate it in a solution for my problem.

    I have 3 tables:

    • User;
    • UserRoles;
    • Modules.

    A user can have many roles and a role can belong to many users, a role can have many modules, and a module can be related to different roles.

    So there is a many-to-many relation between User and UserRoles and there is a many-to-many relation between UserRoles and Modules.

    I need to create a query which gives me all unique module-rows for a given user, this result will be used to load all allowed modules for a user in my application.

    I get stuck with the two many-to-many tables.

    How can I create a Linq-to-SQL query to accomplish this?

    Marcel

    Saturday, June 6, 2015 11:08 AM

Answers

  • Hello Marcel,

    >> I need to create a query which gives me all unique module-rows for a given user, this result will be used to load all allowed modules for a user in my application.

    It is not clear if you are using LINQ to SQL item or the Entity Framework as your database layer, for what you want, if you are:

    Using LINQ to SQL project, since in database, for the many to many relationship, there is an injection table, in your case, you could drop all five tables into the .dbml design panel, and use join syntax as below:

                    using (DataClasses1DataContext db = new DataClasses1DataContext())
    
                                    {              
    
                                        int GiveUserID = 1;
    
                    
    
                                        var result = (from u in db.Users
    
                                                      join ur in db.UserRoles on u.UserID equals ur.Users_UserID
    
                                                      join r in db.Roles on ur.Roles_RoleID equals r.RoleID
    
                                                      join rm in db.RoleModules on r.RoleID equals rm.Roles_RoleID
    
                                                      join m in db.Modules on rm.Modules_ModuleID equals m.ModuleID
    
                                                      where u.UserID == GiveUserID
    
                                                      select new { u.UserID, m.ModuleID, m.ModuleName }).ToList();
    
                                    }
    

    If you are using Entity Framework, since it would not represents the injection table in the design panel, we need to use foreach syntax:

    using (Model1Container db = new Model1Container())
    
                    {
    
                        List<Module> modules = new List<Module>();
    
                        db.Database.Log = Console.Write;
    
                        int GiveUserID = 1;
    
                        var users = db.Users.Where(u => u.UserID == GiveUserID);
    
                        foreach (var user in users)
    
                        {
    
                            foreach (var role in user.Roles)
    
                            {
    
                                foreach (var module in role.Modules)
    
                                {
    
                                    modules.Add(module);
    
                                }
    
                            }
    
                        }
    
                    }
    

    Or using Raw SQL query in EF: http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 8, 2015 2:19 AM
    Moderator