Solved

Linq join on 3 tables

Posted on 2008-10-27
3
10,286 Views
Last Modified: 2012-08-13
Hello,

I'm determined to learn linq. I'm experiemtning with a simple role managment system that we built here to play with linq.

We have a users, role, and user_roles table.

in the user table we have username and password.
in the role table we have an id (auto increment) and the role name (shipping)
in the user_roles table we have two fields ( username, and roleid) - to link the other table together.

I'm trying to write  a method like this:

public static bool AutenticateUserByRole(string uname, string pass, string role) {
        WiTracDBDataContext db = new WiTracDBDataContext();
        try {
            if (uname == "") {
                return false;
            }
            var q = (from c in db.Users
                where c.Username == uname && c.Password == pass
                join ur in db.User_Roles on c.Username equals ur.Username
                join r in db.Roles on ur.RoleID equals r.Id
                select c);
            if (q != null) {
                return true;
            }
        }
        catch { }
        return false;
    }

The problem ofcourse is i'm not actually adding the role in my where clause. Where would I put that?

thanks,

0
Comment
Question by:copyPasteGhost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22813556
Not 100% sure, but what about:

            var q = (from c in db.Users
                where c.Username == uname && c.Password == pass && r.RoleName == role
                join ur in db.User_Roles on c.Username equals ur.Username
                join r in db.Roles on ur.RoleID equals r.Id
                select c);

Open in new window

0
 
LVL 13

Accepted Solution

by:
copyPasteGhost earned 0 total points
ID: 22813676
problem with that... r doesn't exist yet...since you declare it later..

I firgured out the problem.. I needed this.

var q = (from c in db.Users
                where c.Username == uname && c.Password == pass
                join dummy in (from r in db.User_Roles
                               join ur in db.Roles on r.RoleID equals ur.Id
                               where ur.Name == PageToAccess
                               select r) on c.Username equals dummy.Username
                select c);
0
 
LVL 13

Author Comment

by:copyPasteGhost
ID: 22813681
thanks for your help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question