CODEWITHSUNDEEP
sql-serverentity-frameworkinner-joinlinq-extensions
mack_asp
mack_asp

Reputation: 11

How to extensions join 3 tables on different id

SQL inner join query: 

 Select 
     r.RoleName 
 From 
     webpages_Roles r 
 Inner Join 
     webpages_GroupInRoles gr ON r.RoleID = gr.RoleId 
 Inner Join 
     webpages_UsersInGroup ug ON gr.GroupID = ug.GroupID 
 Where 
     ug.UserID = 1

I am trying to convert this SQL to extension join 3 tables using EF:

var q1 = db.webpages_Roles
    .Join(db.webpages_GroupInRoles,
          r => r.RoleId,
          gr => gr.RoleID,
          (r, gr) => r)
    .Join(db.webpages_UsersInGroup,
          ug => ug.GroupID,
          gr=>gr.GroupID,
          (ug, gr) => ug);

Upvotes: 0

Views: 385

Answers (2)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

You are almost there.Try filtering webpages_UsersInGroup collection with Where extension method before doing a join.

 var q1 = db.webpages_Roles
        .Join(
        db.webpages_GroupInRoles,
        r => r.RoleId,
        gr => gr.RoleID,
        (r, gr) => r)
        .Join(
        db.webpages_UsersInGroup.Where(x=>x.UserId==1),
        ug => ug.GroupID,
        gr=>gr.GroupID,
        (ug, gr) => ug);

Upvotes: 0

ocuenca
ocuenca

Reputation: 39326

An easy way to translate your sql query is using the LINQ query syntax:

var query= from r in db.webpages_Roles
           join gr in db.webpages_GroupInRoles on r.RoleID equals gr.RoleId
           join ug in db.webpages_UsersInGroup on gr.GroupID equals ug.GroupID 
           where ug.UserID == 1
           select r.RoleName;

Is simpler and easier to read

Upvotes: 0

Related Questions