CODEWITHSUNDEEP
linqentity-frameworkvisual-studio-2012
Salman Shaykh
Salman Shaykh

Reputation: 221

Linq Query relating Many to Many relationship

i am trying to write a LINQ query but somehow it is not working, here is the query

var Table3Qry =
   from LoginTable in db.Logins 
   from ServerTable in LoginTable.Servers.Where(x => x.ServerID == id) 
   select new { LoginTable.passwd, LoginTable.LoginID };

Table Structures are

Login

 - Passwd
 - Userid 
 - Loginid

Server

 - Serverid
 - Servername

ServerLogins

 - Serverid
 - Loginid

I want to find all the Passwords in Login table to a specific ServerID (named id in the query).

Upvotes: 1

Views: 42

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

Filter servers by id, then select all passwords from each server logins:

var passwords =
     db.Servers.Where(s => s.ServerID == id)
       .SelectMany(s => s.Logins.Select(l => new { l.Passwd, l.LoginId }));

Query syntax:

var passwords = from s in db.Servers
                where s.ServerID == id
                from l in s.Logins
                select new { l.Passwd, l.LoginId };

If you are starting from Logins table:

var passwords = from l in db.Logins
                where l.Servers.Any(s => s.ServerID == id)
                select new { l.Passwd, l.LoginId };

Upvotes: 1

Related Questions