title: Linq to Entities Join/Union Statement Help
body: <p>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: </p>
  
  <p><strong>Edit 25 Feb<br>
  1. Note I'm using VS2010 and compiling 4.0.<br>
  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)<br>
  3. Added table scripts (snipped out some fluff) and added the edmx as generated by designer<br>
  4. Rewrote my t-sql from using IN clause to EXISTS, still works<br>
  5. Still reading and testing edmx with LinqPad, sigh...</strong></p>
  
  <pre><code>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]
  </code></pre>
  
  <p><img src="https://i1025.photobucket.com/albums/y320/andy67_pics/EntityDesignerDiagram.jpg" alt="alt text"></p>
  
  <p>The lousy T-SQL statement that gives me what I want is:  </p>
  
  <pre><code>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)
  ) 
  </code></pre>
  
  <p>Please and thanks!</p>