Linq join on 3 tables

Posted on 2008-10-27
Last Modified: 2012-08-13

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?


Question by:copyPasteGhost
  • 2
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

LVL 13

Accepted Solution

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);
LVL 13

Author Comment

ID: 22813681
thanks for your help

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
fomat Json objects 6 28
Events in static methods 3 50
XML & .net 5 40
Counting connections to SQL Server through C# 3 23
Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

937 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

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now