Linq to Entities Join/Union Statement Help

Question

Trying to learn Linq syntax and am struggling with method-based vs expressions. I have 8 tables that allow users to associate to groups and orgs and have forms associated to groups. To further explain, I assign a form to a group. That group may have a user assigned directly or via an org that the user belongs to. I need a Linq statement that will correctly join/union up the tables so I can return the forms assigned to a given user. Here is the basic schema:

Edit 25 Feb
1. Note I'm using VS2010 and compiling 4.0.
2. Removed pk id column from all link tables and enabled 'Include foreign key columns' in wizard, which cleaned up the edmx layout (link tables now association sets vice entity sets)
3. Added table scripts (snipped out some fluff) and added the edmx as generated by designer
4. Rewrote my t-sql from using IN clause to EXISTS, still works
5. Still reading and testing edmx with LinqPad, sigh...

CREATE TABLE [dbo].[Org](
[orgID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [PK_Org] PRIMARY KEY CLUSTERED 
(
[orgID] ASC
) 

CREATE TABLE [dbo].[Groups](
[groupID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
(
[groupID] ASC
)

CREATE TABLE [dbo].[Form](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [PK_Form] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)

CREATE TABLE [dbo].[Users](
[userID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
[userID] ASC
)

###############################################################
Link tables and FKs
###############################################################

CREATE TABLE [dbo].[User_Org](
[userID] [int] NOT NULL,
[orgID] [int] NOT NULL)

ALTER TABLE [dbo].[User_Org]  WITH CHECK ADD  CONSTRAINT [FK_User_Org_Org] FOREIGN KEY([orgID])
REFERENCES [dbo].[Org] ([orgID])

ALTER TABLE [dbo].[User_Org] CHECK CONSTRAINT [FK_User_Org_Org]

ALTER TABLE [dbo].[User_Org]  WITH CHECK ADD  CONSTRAINT [FK_User_Org_Users] FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([userID])

ALTER TABLE [dbo].[User_Org] CHECK CONSTRAINT [FK_User_Org_Users]

###############################################################
CREATE TABLE [dbo].[User_Group](
[userID] [int] NOT NULL,
[groupID] [int] NOT NULL)

ALTER TABLE [dbo].[Org_Group] CHECK CONSTRAINT [FK_Org_Group_Org]

ALTER TABLE [dbo].[User_Group]  WITH CHECK ADD  CONSTRAINT [FK_User_Group_Group] FOREIGN KEY([groupID])
REFERENCES [dbo].[Groups] ([groupID])

ALTER TABLE [dbo].[User_Group] CHECK CONSTRAINT [FK_User_Group_Group]

ALTER TABLE [dbo].[User_Group]  WITH CHECK ADD  CONSTRAINT [FK_User_Group_Users] FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([userID])

ALTER TABLE [dbo].[User_Group] CHECK CONSTRAINT [FK_User_Group_Users]

###############################################################
CREATE TABLE [dbo].[Org_Group](
[orgID] [int] NOT NULL,
[groupID] [int] NOT NULL)

ALTER TABLE [dbo].[Org_Group]  WITH CHECK ADD  CONSTRAINT [FK_Org_Group_Group] FOREIGN KEY([groupID])
REFERENCES [dbo].[Groups] ([groupID])

ALTER TABLE [dbo].[Org_Group] CHECK CONSTRAINT [FK_Org_Group_Group]

ALTER TABLE [dbo].[Org_Group]  WITH CHECK ADD  CONSTRAINT [FK_Org_Group_Org] FOREIGN KEY([orgID])
REFERENCES [dbo].[Org] ([orgID])

###############################################################
CREATE TABLE [dbo].[Form_Group](
[FormID] [int] NOT NULL,
[groupID] [int] NOT NULL)

ALTER TABLE [dbo].[Form_Group]  WITH CHECK ADD  CONSTRAINT [FK_Form_Group_Form] FOREIGN KEY([FormID])
REFERENCES [dbo].[Form] ([ID])

ALTER TABLE [dbo].[Form_Group] CHECK CONSTRAINT [FK_Form_Group_Form]

ALTER TABLE [dbo].[Form_Group]  WITH CHECK ADD  CONSTRAINT [FK_Form_Group_Groups] FOREIGN KEY([groupID])
REFERENCES [dbo].[Groups] ([groupID])

ALTER TABLE [dbo].[Form_Group] CHECK CONSTRAINT [FK_Form_Group_Groups]

The lousy T-SQL statement that gives me what I want is:

declare @userid int
set @userid = 1
select distinct(f.id)
from Form f
join Form_Group fg on f.id = fg.formid 
join Groups g on fg.groupid = g.groupid
where exists
((select g1.groupid
from Groups g1 
join User_Group ug on g1.groupid = ug.groupid 
join Users u on ug.userid = u.userid
where u.userid = @userid
and g.groupid = g1.groupid)
union
(select g2.groupid
from Groups g2 
join Org_group og on g2.groupid = og.groupid 
join Org o on og.orgid = o.orgid 
join User_org uo on o.orgid = uo.orgid 
join Users u on uo.userid = u.userid
where u.userid = @userid
and g.groupid = g2.groupid)
) 

Please and thanks!

Answer 1

Are you sure you need that many tables?
Are you sure about all the N:N relations?

I don't have SQL Management Studio here (I'm on my mac). But I guess you could simplify your T-SQL statement to:

select distinct(Form.id)
from Form
     inner join Form_Group on Form.formID = Form_Group.formID
     inner join Group on Form_Group.groupID = Group.groupID
     left outer join User_Group on Group.groupid = User_Group.groupid AND User_Group.userid = @userid  
     left outer join Org_Group on Group.groupid = Org_Group.groupid   
     inner join Org on Org_Group.orgid = Org.orgid   
     inner join User_Org on Org.orgid = User_Org.orgid AND User_Org.userid = @userid

That should make it easier to construct your LINQ statement.
Here's some useful information on LINQ and left outer/inner joins in LINQ:

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx
http://odetocode.com/Blogs/scott/archive/2008/03/25/inner-outer-lets-all-join-together-with-linq.aspx

Answer 2

Fiddling with LinqPad, I got something that worked!

int userID = 1;  

var formIDsforUser = 
(from g in Groups
from u in g.Users 
where u.userID == userID 
from o in g.Orgs 
from u1 in o.Users 
where u.userID == userID
from f in g.Form
select f.ID).Distinct();

I am feeling quite thick at the moment as the concepts are still fuzzy. What helped I think was dumping the primary keys on the link tables. Any better way to write this query? What would it look like using methods?

LinqPad's result converter (the lambda symbol) spits out:

Group.MergeAs (AppendOnly)
.SelectMany (
  g => g.Users, 
  (g, u) => 
     new  
     {
     }
)
.Where (temp0 => (temp0.u.userID == userID))
.SelectMany (
  temp0 => temp0.g.Orgs, 
  (temp0, o) => 
     new  
     {
     }
)
.SelectMany (
  temp1 => temp1.o.Users, 
  (temp1, u1) => 
     new  
     {
     }
)
.Where (
  temp2 => 
     (temp2.<>h__TransparentIdentifier1.<>h__TransparentIdentifier0.u.userID == userID)
)
.SelectMany (
  temp2 => temp2.<>h__TransparentIdentifier1.<>h__TransparentIdentifier0.g.Form, 
  (temp2, f) => f.ID
)
.Distinct ()

which looks an awful mess...

and LinqPad's sql result is:

-- Region Parameters  
DECLARE p__linq__0 Int = 1  
DECLARE p__linq__1 Int = 1  
-- EndRegion  
SELECT   
[Distinct1].[formID] AS [formID]  
FROM ( SELECT DISTINCT   
[Extent4].[formID] AS [formID]  
FROM    (SELECT   
  [User_Group].[userID] AS [userID],   
  [User_Group].[groupID] AS [groupID]  
  FROM [dbo].[User_Group] AS [User_Group]) AS [Extent1]  
INNER JOIN (SELECT   
  [Org_Group].[orgID] AS [orgID],   
  [Org_Group].[groupID] AS [groupID]  
  FROM [dbo].[Org_Group] AS [Org_Group]) AS [Extent2]  
      ON [Extent1].[groupID] = [Extent2].[groupID]  
INNER JOIN (SELECT 
  [User_Org].[userID] AS [userID], 
  [User_Org].[orgID] AS [orgID]
  FROM [dbo].[User_Org] AS [User_Org]) AS [Extent3]  
         ON [Extent2].[orgID] = [Extent3].[orgID]
INNER JOIN (SELECT 
  [Form_Group].[formID] AS [formID], 
  [Form_Group].[groupID] AS [groupID]
  FROM [dbo].[Form_Group] AS [Form_Group]) AS [Extent4]  
        ON [Extent1].[groupID] = [Extent4].[groupID]
WHERE ([Extent1].[userID] = @p__linq__0) 
       AND ([Extent1].[userID] = @p__linq__1)  
)  AS [Distinct1]

which when run against db yields the proper results as well...